how to set result of aggregate sql query as lable caption

Question

I am using this code to retrieve no of machines (count)...

    Dim strCntSr As String = "SELECT count(sr_no) FROM Vendor_Machine WHERE chaln_no='" & cmbChal_no.Text & "'"
    comm_getCnt = New OleDb.OleDbCommand(strCntSr, cnnOLEDB)
    comm_getCnt.ExecuteNonQuery()

    ***Here I want to set result of the above query [count(sr_no)] as text of lblMachine***

    lblMachine.Text = 

Please suggest me the code.. Thank you..

Answer 1

ExecuteNonQuery return only the number of the rows affected not the value/s returned by the query.
In your case the correct method to use is ExecuteScalar that returns the first column of the first row obtained by the query.

Notice also that is a very bad practice to build query text using string concatenation.
The problems are Sql Injection and correct parsing of text provided by you.

Dim strCntSr As String = "SELECT count(sr_no) FROM Vendor_Machine WHERE chaln_no=?"
comm_getCnt = New OleDb.OleDbCommand(strCntSr, cnnOLEDB)
comm_getCnt.Parameters.AddWithValue("@p1", cmbChal_no.Text)
Dim result = comm_getCnt.ExecuteScalar()
lblMachine.Text = Convert.ToInt32(result);