If the command contains an ad hoc SQL statement, as opposed to a stored-procedure name, the number of the parameters in the collection must be equal to the number of parameter placeholders within the command text, or SQL Server raises an error. With a stored procedure, all the parameters declared in the stored procedure without a default value must be provided. Parameters declared with a default value are optional. This lets you specify a value other than the default.

For more information with additional sample code demonstrating how to use parameters, see Commands and Parameters (ADO.NET).

The following example creates multiple instances of SqlParameter through the SqlParameterCollection collection. The parameters are used to select data within the data source and populate the DataSet. This code assumes that a DataSet and a SqlDataAdapter have already been created with the appropriate schema, commands, and connection.

Public Sub AddSqlParameters()
    ' ...
    ' create categoriesDataSet and categoriesAdapter
    ' ...
    categoriesAdapter.SelectCommand.Parameters.Add( _
        "@CategoryName", SqlDbType.VarChar, 80).Value = "toasters"
    categoriesAdapter.SelectCommand.Parameters.Add( _
        "@SerialNum", SqlDbType.Int).Value = 239

    categoriesAdapter.Fill(categoriesDataSet)
End Sub  

public void AddSqlParameters() 
{
// ...
// create categoriesDataSet and categoriesAdapter
// ...

  categoriesAdapter.SelectCommand.Parameters.Add(
    "@CategoryName", SqlDbType.VarChar, 80).Value = "toasters";
  categoriesAdapter.SelectCommand.Parameters.Add(
    "@SerialNum", SqlDbType.Int).Value = 239;
  categoriesAdapter.Fill(categoriesDataSet);

}

.NET Framework

.NET Framework Client Profile

Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows XP SP2 x64 Edition, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

Other Resources