How to: Set and Get Parameters for Command Objects

If you are using command objects to execute commands against a database, the SQL statements or stored procedures you are executing often require parameters. For example, a simple Update statement that requires parameters might look like the following:

UPDATE Employees
SET LastName = @LastName, FirstName = @FirstName, BirthDate = @BirthDate
WHERE (EmployeeID = @EmployeeID)

When this statement is executed, you must provide values for all the parameters (@LastName, @FirstName, @BirthDate, and @EmployeeID). To do so, you use parameter objects. Command objects support a parameters collection (for example, Parameters) that contains a set of objects of type SqlParameter, OleDbParameter, OdbcParameter, or OracleParameter. There is one parameter object in the collection for each parameter you need to pass. In addition, if you are calling a stored procedure, you might need an additional parameter to accept the procedure's return value.

Setting Parameter Values

Before you execute a command, you must set a value for every parameter in the command.

To set a parameter value

• For each parameter in the command's parameters collection, set its Value property.

  The following example shows how to set parameters before executing a command that references a stored procedure. The sample assumes that you have already configured the parameters collection with three parameters named au_id, au_lname, and au_fname. The individual parameters are set by name to make it clear which parameter is being set.

  With oleDbCommand1
      .CommandText = "UpdateAuthor"
      .CommandType = System.Data.CommandType.StoredProcedure
      .Parameters("au_id").Value = "172-32-1176"
      .Parameters("au_lname").Value = "White"
      .Parameters("au_fname").Value = "Johnson" 
  End With
  
  OleDbConnection1.Open()
  oleDbCommand1.ExecuteNonQuery()
  OleDbConnection1.Close()
  

  oleDbCommand1.CommandText = "UpdateAuthor";
  oleDbCommand1.CommandType = System.Data.CommandType.StoredProcedure;
  
  oleDbCommand1.Parameters["au_id"].Value = "172-32-1176";
  oleDbCommand1.Parameters["au_lname"].Value = "White";
  oleDbCommand1.Parameters["au_fname"].Value = "Johnson";
  
  oleDbConnection1.Open();
  oleDbCommand1.ExecuteNonQuery();
  oleDbConnection1.Close();
  

Getting Return Values

Stored procedures often pass values back to the application that called them.

To get values returned by procedures

1. Create parameters whose Direction property is set to Output or InputOutput (if the parameter is used in the procedure to both receive and send values). Make sure the data type of the parameter matches the expected return value.

2. After executing the procedure, read the Value property of the parameter being passed back.

To get a procedure's return value

1. Create parameters whose Direction property is set to ReturnValue.

   Note

   The parameter object for the return value must be the first item in the parameters collection.

2. Make sure the parameter's data type matches the expected return value.

   Note

   Update, Insert, and Delete SQL statements return an integer value indicating the number of records affected by the statement. You can get this value as the return value of the ExecuteNonQuery method. For more information, see Specifying Parameters and Return Values.

The following example shows how to get the return value of a stored procedure called CountAuthors. In this case, it is assumed that the first parameter in the command's parameters collection is named "retvalue" and that is configured with a direction of ReturnValue.

Dim returnValue As Integer

oleDbCommand1.CommandText = "CountAuthors"
oleDbCommand1.CommandType = CommandType.StoredProcedure

oleDbConnection1.Open()
oleDbCommand1.ExecuteNonQuery()
oleDbConnection1.Close()

returnValue = CType(oleDbCommand1.Parameters("retvalue").Value, Integer)
MessageBox.Show("Return Value = " & returnValue.ToString())

int returnValue;

oleDbCommand1.CommandText = "CountAuthors";
oleDbCommand1.CommandType = CommandType.StoredProcedure;

oleDbConnection1.Open();
oleDbCommand1.ExecuteNonQuery();
oleDbConnection1.Close();

returnValue = (int)(oleDbCommand1.Parameters["retvalue"].Value);
MessageBox.Show("Return Value = " + returnValue.ToString());

See Also

Tasks

How to: Create and Execute an SQL Statement that Returns Rows

How to: Create and Execute an SQL Statement that Returns a Single Value

How to: Create and Execute an SQL Statement that Returns No Value

How to: Execute a Stored Procedure that Returns Rows

How to: Execute a Stored Procedure that Returns a Single Value

How to: Execute a Stored Procedure that Returns No Value

Concepts

Fetching Data into Your Application

Other Resources

Using Parameters with a DataAdapter

Specifying Parameters and Return Values