|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
Executing a Data Command that Returns a Single Value
At times, you might want to execute a database command or function that returns a single value — that is, a scalar value. Because you are returning only one value, this type of command is typically not performed with datasets. A typical example is an SQL statement that returns an aggregate value (SUM, COUNT, and so on). Other examples are a stored procedure that takes a credit card number and returns an authorization code, or a procedure that takes a product code and returns a product name.
Note If you are using a dataset, you can aggregate values of data table columns using column expressions. For more information, see Creating Dataset Columns Using Expressions.
To execute a command that returns a scalar value
- Add a data command to your form or component. For more information, see Adding Data Commands to a Form or Component.
- Set the data command object's CommandText property to an SQL statement or the name of a stored procedure.
- Set the CommandType property to CommandType.Text (for a statement) or CommandType.StoredProcedure (for a stored procedure). These values are defined in the CommandType enumeration.
- If the command takes parameters, set them. For more information, see Setting and Getting Data Command Parameters.
- Open the connection associated with the data command.
- Call the command's ExecuteScalar method, setting the result to a variable of the appropriate data type.
- Close the connection.
The following example shows how to use a data command to execute an SQL statement that returns a scalar value. The statement queries a products table. Its takes a product ID code passed as a parameter, and returns an integer value indicating the quantity in stock for that product. In this example, there is no aggregate function (for example, SUM), because the quantity in stock is stored as a column value in the Products table.
' Visual Basic Dim scalarcmd As String Dim qtyinstock As Integer scalarcmd = "SELECT UnitsInStock FROM Products WHERE (ProductID = ?)" OleDbCommand3.CommandType = CommandType.Text OleDbCommand3.CommandText = scalarcmd OleDbCommand3.Parameters("productid").Value = txtProductID.Text OleDbConnection1.Open() qtyinstock = CType(OleDbCommand3.ExecuteScalar(), Integer) OleDbConnection1.Close() MessageBox.Show("QtyinStock = " & qtyinstock.ToString) // C# string scalarcmd; int qtyinstock; scalarcmd = "SELECT UnitsInStock FROM Products WHERE (ProductID = ?)"; OleDbCommand3.CommandType = CommandType.Text; OleDbCommand3.CommandText = scalarcmd; OleDbCommand3.Parameters["productid"].Value = txtProductID.Text; OleDbConnection1.Open(); qtyinstock = (int)(OleDbCommand3.ExecuteScalar()); OleDbConnection1.Close(); MessageBox.Show("QtyinStock = " + qtyinstock.ToString());
Introduction to DataCommand Objects in Visual Studio | Working with Data Commands: High-Level Process | Setting and Getting Data Command Parameters | Executing a Data Command that Returns a Result Set | Executing Updates or Database Commands using a Data Command