Row count

[Ooogaleee]

I need to return the number of rows produced into the datagrid by this code. Can someone give me a push in the right direction?

Thanks much....Ooogs


Dim myQuery As String = "SELECT * FROM [tbl * F002 > 4 J] WHERE MUFLID2 =" & Chr(39) & ddlWho.SelectedItem.Text & Chr(39) & " ORDER BY" & sortee
OleDbConnection1.Open()
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = OleDbConnection1
cmd.CommandText = myQuery
Dim datareader As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
dgF002.DataSource = datareader
dgF002.DataBind()

[Lord_Rat]

There is a programmatical way, but I usually just run the same select you have, but instead of
SELECT * ...
I use
SELECT Count(*) ...

Then, I read row1,col1 to get the count and then close the recordset, run the SELECT * and press on.

[Ooogaleee]

Do you mean to do something like this?

Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = OleDbConnection1

Dim myCount As String = "SELECT count(*) FROM [tbl * F002 > 4 J] WHERE MUFLID2 =" & Chr(39) & ddlWho.SelectedItem.Text
OleDbConnection1.Open()
cmd.CommandText = myCount
Dim datareader As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

(Here is where I guess I need to reference the count(*))

Dim myQuery As String = "SELECT * FROM [tbl * F002 > 4 J] WHERE MUFLID2 =" & Chr(39) & ddlWho.SelectedItem.Text & Chr(39) & " ORDER BY" & sortee
OleDbConnection1.Open()
cmd.CommandText = myQuery
datareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
dgF002.DataSource = datareader
dgF002.DataBind()
OleDbConnection1.Close()

If so, how would I then reference the count number I obtained in the first block?

Ooogs

[Lord_Rat]

Correct.

Something like:

if not objReader.Read() Then
   'Oh My. Something went wrong...
   return false
end if

myCount = objReader.Items(0)

objReader.Close()

Just FYI. This is actually better than trying to get the row count from the objReader itself for the following reason.

If you attempted to get the reader's row count, you would have to fetch to the end of the recordset first in order to assure you that the dataset has dumped you the entire result set (in order to get an accurate count) which also means that you have to open the recordset with a backward-readable cursor, which in turn uses more environment space and is a slower cursor.