facebook  linkedin  Twitter  skype  Rss googlePlus

Calling a stored procedure from ASP.Net

May 04 2005
7912

 By Brad Kingsley

I guess I'm from the "old school" because I learned coding without a fancy GUI and I still have a tendency to lean 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 from 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 function 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 by below (put your real connection string here). 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 Command Type to Data.CommandType.StoredProcedure (VS'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 subroutine 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


That's it! It really isn't very hard, but since I couldn't find an online sample I wound up fussing with this for about an hour. Hopefully this will give someone else a good start and save them from wasting time scratching their heads like I did.

~Brad

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.

About the Author, Patrick Santry

Patrick Santry, has two decades of experience in enabling businesses to take advantage of the digital landscape. A well rounded experience in technology, and business is what sets me apart from the rest of the pack. When it comes to an overall digital strategy my experience is impressive.

BS in Computer Information Systems. Four time recipient of the Microsoft MVP Award, and author of several books and magazine articles on digital technologies.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.

Popular Articles