• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Resolved counting rows from a query

ideprize

Well-known member
Joined
Oct 19, 2011
Messages
72
Programming Experience
10+
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
VB.NET:
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
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,027
Location
Sydney, Australia
Programming Experience
10+
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.
VB.NET:
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

Well-known member
Joined
Oct 19, 2011
Messages
72
Programming Experience
10+
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
 
Top Bottom