CodeSnip: Calling a Stored Procedure from ASP.NET 2.0
page 1 of 1
Published: 11 May 2005
Unedited - Community Contributed
Abstract
With ASP.NET 2.0 it is easy to create an ASP.NET page that can pass parameters to a SQL Server stored procedure to insert data into a database. This CodeSnip shows you just how easy it is.
by Web Team at ORCS Web
Feedback
Average Rating: 
Views (Total / Last 10 Days): 90203/ 40

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

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.

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.



User Comments

Title: SQL   
Name: Faisal
Date: 2012-06-02 8:47:55 AM
Comment:
Thanks!
Title: DBA   
Name: Alvaro
Date: 2010-12-10 12:50:31 PM
Comment:
Excellent thanks
Title: DBA   
Name: Wendy G
Date: 2010-12-08 1:18:32 AM
Comment:
Thanks for this, it is exactly what I was looking for. I looked all over for a simple example and didn't find one until now.
Title: App Engineer   
Name: Ani G
Date: 2010-12-01 2:22:33 PM
Comment:
Looks good. Thank you very much. However, you have to make sure that all the connections get closed. Typically I use of finally block to close any open connections. In your code what if there is an error with the ExecuteNonQuery()? In that case your connection will remain open or even throw an error on the page.
Title: Liz Ravenwood   
Name: Programmer
Date: 2010-10-04 7:02:25 PM
Comment:
Thank you! I've been all over the place and found some pretty convoluted stuff that shows all kinds of additional coding for extra options, but I needed something clear, simple, and singular in function.
Title: mr   
Name: andrei
Date: 2010-08-05 6:18:52 AM
Comment:
thanks
Title: nice   
Name: mai
Date: 2010-07-05 4:46:22 AM
Comment:
thank you
شكرا جزيلا
Title: Understanding it all   
Name: jgr
Date: 2010-07-05 2:24:18 AM
Comment:
I like the simplicity of this example. Very straight forward. Many thanks..
Title: good   
Name: thanga
Date: 2010-05-31 6:40:18 AM
Comment:
simple and efficient
Title: mr   
Name: girisam
Date: 2010-04-07 8:59:13 AM
Comment:
its helping a lot
Title: Mr   
Name: Obi
Date: 2009-11-06 6:50:44 AM
Comment:
Brilliant! Straight to the point. Very helpful code!
Title: thank you   
Name: vivek
Date: 2009-07-12 11:47:43 AM
Comment:
Hi...thank a lot for the article
Title: Thanks Alot   
Name: Sangeeta
Date: 2008-10-29 10:16:14 AM
Comment:
Hey Thank u very much as it helpd me alot.......
Title: Good   
Name: Sep
Date: 2008-09-18 9:05:49 PM
Comment:
I like the simplicity of this example. Very straight forward. Many thanks..
Title: nice one   
Name: raj
Date: 2008-09-16 7:06:52 AM
Comment:
nice article and very much clear and easily can understand
Title: Understanding it all   
Name: Cherise
Date: 2008-09-11 6:11:50 PM
Comment:
I am having a time understand it all.
I have to send parameters to a already created stored procedure to received data for view/save/print. Thinking of using Crystal reports to do this? Just not sure.

I am creating a website using VS 2005 ASP.NET 2.0. I have four parameters that come from a list selection that I am trying to pass to a sql 2000 stored procedure name qcreport1. How do I do this?
Title: Procedure 'submitrecord' expects parameter '@ID', which was not supplied.   
Name: manas
Date: 2008-06-26 2:12:39 AM
Comment:
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection ("Data Source=SWT-T6;Initial Catalog=kuna;User ID=sa");
SqlDataReader dr;
//Here we declare the parameter which we have to use in our application
SqlCommand cmd = new SqlCommand();
SqlParameter sp1 = new SqlParameter();
SqlParameter sp2 = new SqlParameter();
SqlParameter sp3 = new SqlParameter();
SqlParameter sp4 = new SqlParameter();
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click1(object sender, EventArgs e)
{
//con = new SqlConnection("Data Source=SWT-T6;Initial Catalog=kuna;User ID=sa");
cmd.Parameters.Add("@ID",SqlDbType .VarChar, 10).Value=TextBox1.Text;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value =TextBox2.Text;
cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value =TextBox3.Text;
cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value =TextBox4.Text;
cmd = new SqlCommand("submitrecord", con);
cmd.CommandType =System .Data .CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
CREATE PROCEDURE submitrecord
@ID varchar(10),
@Password varchar(50),
@ConfirmPassword varchar(50),
@EmailID varchar(200)
AS
BEGIN
insert into login(ID,Password,ConfirmPassword,EmailID)values(@ID,@Password,@ConfirmPassword,@EmailID)
END


GO
Title: Help to write Stored Procedure for this   
Name: Gayathri
Date: 2008-06-09 3:55:57 AM
Comment:
I need stored procedure coding also for this. Because I am New to stored procedure.
Thank you.
Title: Found it quickly thanks to you   
Name: Firoz
Date: 2008-04-26 5:02:42 AM
Comment:
After reading your last paragraph, I realised how fortunate I was - in my Google search, your page came out 4th in the list, and I didn't even try any of the other results above it. You've probably saved me an hour! Thanks
Title: Thanks   
Name: Ksomu
Date: 2008-02-20 11:42:58 AM
Comment:
Thanks, This is much useful as a great start to me
Title: Procedure Running   
Name: ramesh
Date: 2007-12-15 4:21:51 AM
Comment:
realy nice ya....
Title: World Of Thanks   
Name: Bill Dean
Date: 2007-11-19 1:53:54 PM
Comment:
I modified your code into the following code used with ASP.NET 2.0 (Yes, I know TRY .. CATCH .. FINALLY should be used):

Imports System.Data.SqlClient

Protected Sub btnUpdateResultCodes_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateResultCodes.Click

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(Me.SqlDataSource1.ConnectionString)
Dim cmdUpdate As New SqlCommand("sp_UpdateInvoiceStatus", dbConnection)

cmdUpdate.CommandType = Data.CommandType.StoredProcedure

'Example of how to add Parameters to Command
'cmdUpdate.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10))
'cmdUpdate.Parameters("@TestParam").Value = "Testing"

dbConnection.Open()
cmdUpdate.ExecuteNonQuery()
dbConnection.Close()
lblDisplay.Text = "Result Codes Have Been Updated."

End Sub
Title: I think this one can be done also   
Name: Soe Thiha
Date: 2007-09-12 2:51:42 AM
Comment:
Just use the sqlcommand obj
i will show just breafly.
dim cmd as new sqlcommand
dim sqlstr as string
'// other require code are here .......
'// something like getting the connection, assign the
'//connection to command obj and so on
sqlstr = "EXEC Sp_Generate_Payroll '" & sDate & "','" & eDate & "'"
cmd.commandtext = sqlstr
cmd.executenonqyery

'// that's all. it is ok in win app.
Title: Thanks   
Name: Lionstone
Date: 2007-08-24 9:24:02 AM
Comment:
Thank you for this. I'm just now transitioning from classic ASP (I know, I know ... get with the program) and I'm not at all comfortable with the drag and drop "magic" of SqlDataSource controls and all that. Until (and if) I get to that point, this at least lets me get some work done. :)
Title: Hello   
Name: Adam
Date: 2007-07-17 12:01:07 PM
Comment:
Useful tip for posters bellow;
Take some English classes before posting crap.

BTW, great article.
Title: Thanks   
Name: Abdul
Date: 2007-07-17 11:58:22 AM
Comment:
Hey, thanks for the code snippet.
I spent quite a while searching for something similar thinking it would be different to asp.net 1.1. everybody else just seems to be concentrating on the drag and drop controls.
Title: ORCSWeb   
Name: Brad
Date: 2007-07-13 5:21:30 PM
Comment:
You need to check the permission level of the user you are connecting with in your code.

Brad
Title: store procedure runtime erro   
Name: hp
Date: 2007-07-11 7:51:22 PM
Comment:
hi
I am using database on different server for my application.
If I write query in my code its working fine but when I creat storeprocedure and tri to use it
it gave me this error

"The EXECUTE permission was denied on the object 'ReportUnitCaseLoad', database 'Insyst', schema 'dbo'. "

"ReportUnitCaseLoad" is my storeprocedure name.
Thank you
Title: Article Feedback   
Name: Abhishek Trivedi
Date: 2007-06-25 9:24:55 AM
Comment:
test your code first then put it.
cmdTest.Parameters("@TestParam").Value = "Testing"
it is not true.

cmdTest.Parameters["@TestParam"].Value = "Testing"
Title: useless   
Name: suji
Date: 2007-04-25 1:45:58 AM
Comment:
there is no use in this of website please change ur workers or remove this site from web world or else subscriber will hack this website because it does'nt bring knowledge the user.
Title: great   
Name: vivek
Date: 2007-04-08 6:27:15 AM
Comment:
that's great.
Title: Hi   
Name: Welcome
Date: 2007-04-04 1:03:25 AM
Comment:
Thanks Dear. You saved me a bunch of time, probably would have taken me more than an hour. I'm brand new to .net so the fact that it's no different from 1.1 is irrelevant. Regarding your omission of a try block, that's the way I prefer code samples, straight to the point. If you don't know to use a try block you don't know what you're doing in the first place.
Title: Thanks Brad   
Name: Randy
Date: 2007-02-01 8:18:57 PM
Comment:
Thanks Brad. You saved me a bunch of time, probably would have taken me more than an hour. I'm brand new to .net so the fact that it's no different from 1.1 is irrelevant. Regarding your omission of a try block, that's the way I prefer code samples, straight to the point. If you don't know to use a try block you don't know what you're doing in the first place.
Title: thanks   
Name: parag gholse
Date: 2007-01-29 1:31:16 PM
Comment:
time saving handy code
Title: Thanks   
Name: Joe Gakenheimer
Date: 2007-01-09 11:10:24 AM
Comment:
.Net guru Scott Mitchell (http://www.4guysfromrolla) can't seem to show the entire code block, yet seems to have no trouble showing screen shots of the IDE; personally, I don't have time for that nonsense.
Title: Calling a Stored Procedure from ASP.NET 2.0   
Name: Vietnamese Student .
Date: 2006-12-29 12:40:46 PM
Comment:
Could any1 give me C# code ?
Title: 12   
Name: 12
Date: 2006-11-12 9:36:32 PM
Comment:
u need to add this to the top of the line

Imports System.Data.SqlClient
Title: Regarding Calling a Stored Procedure from ASP.NET 2.0   
Name: Abey Mathew
Date: 2006-10-23 2:30:28 PM
Comment:
i have tried copying this code into a new aspx page with a single btn1 but it doesnt work. It wasys it doesnt recognize sqlconnection or sqlcommand. I did include the import statement. I am using Visual Studio 2005 with SQL 2005 and ASP.NET 2.0
Title: im   
Name: pankaj lahoti
Date: 2006-08-14 8:14:17 AM
Comment:
ya,its very interesting and also much easier code for the begginer.i found very good and my most of my friends also found easier
Title: doubt   
Name: jhanani
Date: 2006-06-07 2:53:08 AM
Comment:
MyCommand = New SqlDataAdapter("Ten Most Expensive Products", MyConnection)

MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure

DS = new DataSet()
MyCommand.Fill(DS, "Products")

In this coding if Ten Most Expensive Products is Stored Procedure then what is Products
Title: error occured   
Name: eswar
Date: 2006-04-26 2:58:33 AM
Comment:
ublic Class AskAnExpertDb
Public Shared Function GetExpertTypes() As Data.DataSet
Dim ds As Data.DataSet
Try

ds = 'SqlHelper.ExecuteDataset

(GetDBConnectionString, _
'CommandType.StoredProcedure, _
"xsp_GetExpertTypes")
Catch ex As Exception
'ExceptionManager.Publish(ex)
Throw New Exception("Error getting Expert Types: " + ex.Message)
ds = Nothing
End Try
Return ds
End Function


End Class
these code comments lines are error. please solve this error.
Title: Parameters   
Name: Awais
Date: 2006-03-22 8:01:35 AM
Comment:
It works
Title: Thanks   
Name: Chris
Date: 2006-03-10 10:57:24 AM
Comment:
In response to the comment by the person that said it should include try catch and error detection etc. I thank you for not muddying the waters with that in your example. It is much easier to understand how something works when it's not cluttered with other code. I wish more examples were like yours. Save try catch and error detection for production code.
Title: I am confused !!!   
Name: Deepu
Date: 2006-02-16 12:33:50 AM
Comment:
I didnt get the idea. If suppose i have to call the stored proc "SalesByCategory" where Category=0 then how can this be done ...using passing parameters.
If anybody got any useful links concerning stored procs & passing parameters plz type it .. :)
Title: thanks   
Name: david
Date: 2006-01-24 7:56:19 AM
Comment:
i gave up a few days ago looking for exactly what you teach above. stumbled across it on my lunch break.... cheers for the assist!!!
Title: Software   
Name: Narendra
Date: 2005-11-21 1:29:44 AM
Comment:
plz..give some tutorial on asp.net and webservices
Thans
Title: return values   
Name: nans
Date: 2005-09-16 4:56:59 AM
Comment:
how can we store the return value from a stored procedure in a variable which is in the front end?
Title: Return values in stored procedures   
Name: Krishna
Date: 2005-08-30 5:26:40 AM
Comment:
How to read a return value from a stored procedure in asp.net 2.0??
Title: The ConnectionString property has not been initialized   
Name: Rick
Date: 2005-06-25 4:45:13 PM
Comment:
It sounds to me like you tried using the code snip above exactly as it is. Make sure that you replace {MyConnectionString} with the actual connection string to your SQL database. If you need assistance building a connection string here is a great resource: http://www.connectionstrings.com/
Title: The ConnectionString property has not been initialized   
Name: Paul
Date: 2005-06-25 4:19:36 PM
Comment:
I tried this and all I get is "The ConnectionString property has not been initialized."

I think there's something missing here.
Title: dispose   
Name: Brad
Date: 2005-05-14 6:44:46 AM
Comment:
You should always using error-trapping, error-checking, and many other small techniques to optimize and improve code. I've found in the past though that code samples in articles are easier for the reader to understand if the code is kept relevant to the specific task being mention (but not everyone agrees).

Try...Catch...Finally should be used for all database operations (and some non-database operations).

As for .Dispose() - it's my understanding that calling it is not needed since .Close() is already called and they do the same thing.

Thanks,

Brad
Title: -   
Name: --
Date: 2005-05-13 7:10:02 PM
Comment:
A. You should use Try block and connection.dispouse even in the example :)
Title: Software   
Name: Arun Aralikatti
Date: 2005-05-13 6:54:48 AM
Comment:
This is Comment From Arun
Title: Same as v1.1   
Name: Brad
Date: 2005-05-12 7:35:00 AM
Comment:
That is correct. I could not easily find an online code sample for any version, so I wrote this using v2.0.
Title: Calling a Stored Procedure from ASP.NET 2.0   
Name: Kamran Sorathia
Date: 2005-05-11 12:44:26 PM
Comment:
This is same as we do in .net 1.1.. i dont see any changes in this particular codesnip ...

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-03-30 10:43:34 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search