Store result of SQL count into a variable VB

Im trying to get the result of an SQL statement and store it in an integer variable "count" in vb so it can then be displayed on my website showing the user the total Open records:

    SELECT COUNT (recordID) FROM [tbl_Records] WHERE [Status] = 'Open'

any 开发者_运维知识库help appreciated.

Try this :

Public Function GetOpenRecordCount(ByVal connString As String) As Integer
    Dim RecordCount As Int32 = 0
    Dim sql As String = "SELECT COUNT (recordID) FROM [tbl_Records] WHERE [Status] = 'Open'"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        Try
            conn.Open()
            RecordCount = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

    Return RecordCount
End Function

Make use of : SqlCommand.ExecuteScalar Method

Dim connetionString As String = Nothing
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim sql As String = Nothing

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "Select Count(*) from product"

cnn = New SqlConnection(connetionString)
Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    Dim count As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
    cmd.Dispose()
    cnn.Close()
    MessageBox.Show(" No. of Rows " & count)
Catch ex As Exception
    MessageBox.Show("Can not open connection ! ")
End Try