If you only want one value back, check the ExecuteScalar method of the
Command object. Here's an example, where this is the query:
Dim selectCmd as SqlCommand = new SqlCommand("Select customerID
From Customers where customerID = 'ALFKI'", conn)
And this is how to execute it using a command object called cmd:
Dim custID as String = DirectCast(cmd.ExecuteScalar, String)
If custID = Nothing Then
Throw New ApplicationException("Customer Not Found.")
Else
'do something
End If
If you want to return move than one value, you can use output parameters.
Here's an example that runs against Northwind.
Dim ss As String = "SELECT @UnitPrice = UnitPrice, " & _
" @UnitsInStock = UnitsInStock " & _
"FROM Products WHERE ProductName = @ProductName"
Dim cn As New SqlConnection(My.Settings.NorthwindConnectionString)
cn.Open()
Dim cmd As New SqlCommand(ss, cn)
Dim pUnitPrice, pInStock, pProductName As SqlParameter
pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)
pUnitPrice.Direction = ParameterDirection.Output
pInStock = cmd.Parameters.Add("@UnitsInStock", _
SqlDbType.NVarChar, 20)
pInStock.Direction = ParameterDirection.Output
pProductName = cmd.Parameters.Add("ProductName", _
SqlDbType.NVarChar, 40)
pProductName.Value = "Chai"
cmd.ExecuteNonQuery()
If pUnitPrice.Value Is DBNull.Value Then 'none were found
Console.WriteLine("No product found named {0}", _
pProductName.Value)
Else
Console.WriteLine("Unit price: {0}", pUnitPrice.Value)
Console.WriteLine("In Stock: {0}", pInStock.Value)
End If
cn.Close()
Good luck.
Robin S.
