Store result of SQL count into a variable VB

Question

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.

how are you trying so far? you should create a SqlCommand and call executeScalar... — Davide Piras
– Davide Piras, Commented Oct 11, 2011 at 12:36
strQuery2 = "SELECT COUNT(*) FROM [tbl_Records] WHERE [Status] = 'Open'" oRs2 = New DbConn(strQuery2, "Records") — Gavin Crawley
– Gavin Crawley, Commented Oct 11, 2011 at 12:37
i havent used the executeScalar before so not sure how to use it...will look this up now — Gavin Crawley
– Gavin Crawley, Commented Oct 11, 2011 at 12:38

Upendra Chaudhari · Accepted Answer · 2011-10-11 12:39:24Z

4

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

answered Oct 11, 2011 at 12:39

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pranay Rana · Accepted Answer · 2011-10-11 12:36:29Z

4

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

answered Oct 11, 2011 at 12:36

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Collectives™ on Stack Overflow

Store result of SQL count into a variable VB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related