Click here to Skip to main content
15,745,574 members
Articles / Web Development / ASP.NET
Posted 2 Sep 2004


47 bookmarked

Using SQLParameters with VB.NET/C#

Rate me:
Please Sign up or sign in to vote.
3.44/5 (17 votes)
2 Sep 20042 min read
Using SqlParameters with stored procedures in .NET.


When using the Microsoft Data Access Application Block for access to a Microsoft SQL Server 2000 database and the CommandType is a stored procedure, there may be a need to pass input parameters to the stored procedure. This article sets up the scenario and walks through the process of successfully populating a DataGrid or DropDownList.

If you are not familiar with the Data Access Application Block, you are missing out. Check out: MSDN.

This should be a part of everyone's .NET tool bag as well as some of the other Application Blocks such as Exception Management, Logging Application Block, Caching Application Block etc. I quickly digress.

Data Access

In VB.NET or C#, we all have web page controls or Windows controls that may provide parameters for filtering data from the database. These are the input parameters. A typical example may be searching for some data item from a web or Windows form and then populating a DataGrid with the results.

If we have three drop down lists (ddlInputOne, ddlInputtwo, ddlInputthree) that will provide input to our search criteria (input parameters), and we are using the Microsoft Data Access Application Block and then populating a DataGrid (within a routine), the following lines of code would be used. (Note: the connectionstring would be better hidden in a secure environment or would even use integrated security). Also, the use of Ctype or Convert is to ensure that the type matches what is in the stored procedure:


Protected Sub GetResults()
    Dim connectionstring as string
    connectionstring = "server=MyServer;" & _ 
    Dim sqlparams(3) as SqlClient.SqlParameter
    sqlparams(0) = new SqlClient.SqlParameter("@inputone", SqlDbType.Int)
    sqlparams(0).Value = Ctype(ddlInputOne.SelectedItem.Value,Int32)
    sqlparams(1) = new SqlClient.SqlParameter("@inputtwo",SqlDbType.TinyInt)
    sqlparams(1).Value = Ctype(ddlInputtwo.SelectedItem.Value,Int16)
    sqlparams(2) = new SqlClient.SqlParameter("@inputthree",SqlDbType.SmallInt)
    sqlparams(2).Value = Ctype(ddlInputthree.SelectedItem.Value,Int16)
    dgMyDataGrid.DataSource() = _
      Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(connectionstring, _
      CommandType.StoredProcedure, "MyStoredProcedure",sqlparams)
End Sub


protected void Results()
    string connectionstring = "server=MyServer;database=MyDatabaseDB;" + 
                                     "uid=superuser;password=superpwd" ;
    SqlClient.SqlParameter sqlparams[3];
    sqlparams[0] = new SqlClient.SqlParameter("@inputone", SqlDbType.Int);
    sqlparams[0].Value = Convert.ToInt32(ddlInputOne.SelectedItem.Value);
    sqlparams[1] = new SqlClient.SqlParameter("@inputtwo",SqlDbType.TinyInt);
    sqlparams[1].Value = Convert.ToInt16(ddlInputtwo.SelectedItem.Value);
    sqlparams[2] = new SqlClient.SqlParameter("@inputthree",SqlDbType.SmallInt);
    sqlparams[2].Value = Convert.ToInt16(ddlInputthree.SelectedItem.Value);
    dgMyDataGrid.DataSource() = 
      CommandType.StoredProcedure, "MyStoredProcedure",sqlparams);

SQL Parameters can also have a different syntax of:


SqlParameter[] sqlparams = {
  new SqlParameter("@inputone"...), 
  new SqlParameter("@inputtwo"...), 
  new SqlParameter("@inputthree"...)


sqlparams = SqlParameter() {
  new SqlParameter("@inputone"...), 
  new SqlParameter("@inputtwo"...), 
  new SqlParameter("@inputthree"...) 

I hope that this has been informative and shown something new that is useful. I know that I love the Application Blocks and use them a lot. This code should be helpful to using stored procedures with input parameters with DAAB.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By
Web Developer
United States United States
Scott is a software engineer. Has certifications of MCSD, MCDBA, MCSE, SCJP and PMP. Experience includes Fortran, C++, ASP, VB6, ASP.NET, C#, CV++, Web and Windows Applications, Java and SQL. C# is his favorite language. He is a lifelong learner.

Interests include swimming, running, biking, skiing, reading, traveling, business and software.

Comments and Discussions

QuestionProcedure "in" or "into" Pin
Anderson V. B. Costa4-Dec-12 1:51
Anderson V. B. Costa4-Dec-12 1:51 
QuestionNo @ symbols Pin
navyjax227-Nov-11 21:52
navyjax227-Nov-11 21:52 
Generalprocedure Pin
bds111111-Feb-10 4:58
bds111111-Feb-10 4:58 
GeneralSQLParameters Pin
JoeyNavarro13-Sep-04 11:31
JoeyNavarro13-Sep-04 11:31 
GeneralRe: SQLParameters Pin
Frank100026-May-05 0:08
Frank100026-May-05 0:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.