Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Obtaining a Single Value from a Database

You may need to return database information that is simply a single value rather than in the form of a table or data stream. For example, you may want to return the result of an aggregate function such as COUNT(*), SUM(Price), or AVG(Quantity). The Command object provides the capability to return single values using the ExecuteScalar method. The ExecuteScalar method returns, as a scalar value, the value of the first column of the first row of the result set.

The following code example inserts a new value in the database using a SqlCommand. The ExecuteScalar method is used to return the identity column value for the inserted record.

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer 
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);" 

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar)
        cmd.Parameters("@Name").Value = newName
        Try
            conn.Open()
            newProdID = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
    End Using 

    Return newProdID
End Function
Show:
© 2015 Microsoft