I guess I'm from the "old school" because I learned coding without a fancy GUI, and I still have a tendency to lean in that direction when possible. The latest version of Visual Studio is a great tool, but I still like to understand what's going on rather than just dragging and dropping controls onto a page. Perhaps with some more time I'll give up on this way of thinking, but for now I still like to hand-code at times.

With ASP.NET 2.0 available now, I wanted to create an ASP.NET page that would pass parameters to a SQL Server stored procedure to insert data into a database. I've read that this can be done now with controls and almost no understanding of coding, but I wanted to do it manually. It surprised me that I couldn't find a decent online sample through Google, which motivated me to put together this short article.

I am using Visual Studio 2005 to create my test page. I set up a page with nothing more than a submit button, and then started adding code to the submit method in the associated .vb code file.

The first thing I had to do was add "Imports System.Data.SqlClient" to the very top of the file. Without that the data objects weren't being recognized. After that imports statement is added, the rest isn't too complex.

I created a variable to hold my connection string, represented in braces below (where you will need to put your real connection string). I then created a SQL connection based on the connection string, and also created a SQL command based on that SQL connection. When creating the SQL command, you need to pass the name of the stored procedure ("Insert_Test" in this case) and the name of the connection object.

The next thing you need to do is tell the command that it is going to be calling a stored procedure. You do this by setting the CommandType to Data.CommandType.StoredProcedure (Visual Studio's intellisense makes it really easy to find what you need if you don't remember exactly what you need to type).

Next, I added a parameter to the SQL command object providing the name of the parameter and also the data type and size. After the parameter is added, the value needs to be specified. Until you specify the value, no data is actually set, so be sure to perform this step. In this sample I'm only sending one parameter value, but I could have just as easily sent 20 if needed.

Lastly, I open the connection, execute the stored procedure (automatically passing the parameters defined), and close the connection.

Here is the entire method with the code I used:

Protected Sub Btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) 
Handles Btn1.Click

  Dim sConnStr As String = {MyConnectionString}
  Dim cnBKTest As New SqlConnection(sConnStr)
  Dim cmdTest As New SqlCommand("Insert_Test", cnBKTest)

  cmdTest.CommandType = Data.CommandType.StoredProcedure

  cmdTest.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10))
  cmdTest.Parameters("@TestParam").Value = "Testing"

  cnBKTest.Open()
  cmdTest.ExecuteNonQuery()
  cnBKTest.Close()

End Sub

By Brad Kingsley is founder and president of ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.