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:
cmdTest.CommandType = Data.CommandType.StoredProcedure
, Data.SqlDbType.VarChar, 10))
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 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.