Resolved counting rows from a query

[ideprize]

Hi All
I am using the VFPOLEDB provider. It has been a good "provider"! In the name of shorting code I am trying to use the count command/function to retrieve the rows found from a previous query. Here is my code

sqry = "select * from httrk where ht like 'j310'"
Dim fxcmd As New OleDbCommand(sqry, conn2)
Dim reader As OleDbDataReader = fxcmd.ExecuteReader()

Dim cnt As Integer
sqry = "SELECT ht, Count(ht) FROM httrk group by ht"

Dim rsult As String
Dim cmd1 = New OleDbCommand(sqry, conn2)
rsult = cmd1.ExecuteScalar().ToString
cnt = Convert.ToInt32(rsult)

The initial query does indeed work - it returns rows. I have tried a number of constructs without success. I know there is an issue with the cnt = convert.toint32(rsult) statement. But the real issue is rsult is empty following the rsult = cmd1.ExecuteScalar().tostring statement. Any insights would be greatly appreciated.
Respectfully,
Ideprize

 

[jmcilhinney]

The point of ExecuteScalar is to return the value from the first column of the first row of the result set of a query. It is generally used with a query that produces a result set with one column and one row. Your query produces a result set with two columns and potentially multiple rows. The first column is ht so ExecuteScalar will return the first ht value, which, I'm guessing, is not even an integer value.

If the purpose here is to get the count of the records returned by the first query then your second query is wrong. Also, you should be getting the count first.

Using connection As New OleDbConnection("connection string here")
    connection.Open()

    Dim countCommand As New OleDbCommand("SELECT COUNT(*) FROM httrk WHERE ht = 'j310'", connection)
    Dim count = CInt(countCommand.ExecuteScalar())

    'Use count as desired here.

    Dim dataCommand As New OleDbCommand("SELECT * FROM httrk WHERE ht = 'j310'", connection)

    Using reader = dataCommand.ExecuteReader()
        While reader.Read()
            'Use reader here to get current record.
        End While
    End Using
End Using

 

[ideprize]

You nailed it JM. The real meat was to do it first! Thank you so much!! One more tool in the toolbag. I will mark it as resolved!
Respectfully,
Ideprize