DataReader - can you determine number of rows retrieved with SELECT?

the only way to determine the number of rows with a DataReader is to itterate through them all and count them manually.  However, you can tell whether or not its empty by using the HasRows feature.

If !(dtr.HasRows) Then ddlStudents.Items.Insert("No students found",0)

-Trevor

oops.  had my parameters backwards. the index comes first in the Insert method:

If !(dtr.HasRows) Then ddlStudents.Items.Insert(0,"No students found")

or you could use the Add method:

If !(dtr.HasRows) Then ddlStudents.Items.Add("No students found")

Error:

dtr.hasrows is not a member of System.Data.Sqlclient.SqlDataReader

hasrows isn't a member, but HasRows is :)

I develop with VB.net - so Im not sure if this dtr.hasrows is for C# only or there is something else wrong

one thing is, it is only available in version 1.1 of the .NET framework.

Same thing:

HasRows is not a member of System.Data.Sqlclient.SqlDataReader

Note - vb.net is NOT case sensitive (I think C# is)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclasshasrowstopic.asp

So what about FW 1.0 -

I have Visual Studio 2002

you can get .NET 1.1 here: http://www.msdn.microsoft.com/netframework/downloads/framework1_1/

i would advise that.  if you don't want to you can use the Read() method of the reader since it returns a bool:

If !(dtr.Read()) Then ddlStudents.Items.Add("No students found")

-Trevor

Try..

If dtr Is Nothing Then ddlStudents.Items.Insert(0,"No students found")

Another way to check if any data is found is :    dtr.IsdbNull(0)  , this is true if there is no data.

Alright, one more thing, it is not a good idea to bind ddl to a datareader.  I suggest you use DataAdapter/DataSet. If you use DataReader use AddItem to populate data., but again it is not BINDING.

** Precaution: You can NOT add item to a ddl when it is bound.

Try this modified code:

        Dim thisConnString As String = Session("connString")
        Dim stu_SQL As String
        stu_SQL = " SELECT StudentID, LocationID, Firstname+' '+LastName AS SName " & _
                " FROM Students WHERE Firstname+' '+LastName LIKE '%" & txtStudentSearch.Text & "%'" & _
                " ORDER BY Firstname"
        Dim thisConn As New SqlConnection(ConfigurationSettings.AppSettings(thisConnString))
        Dim DA As New SqlDataAdapter(stu_SQL,thisConn)
        Dim DS AS New DataSet()
        thisConn.Open()
        DA.Fill(DS)
        thisConn.Close()
        lblInfo.Text = DS.Tables(0).Rows.Count
        If DS.Tables(0).Rows.Count>0 Then
            ddlStudents.DataSource = DS.Tables(0)
            ddlStudents.DataTextField = "SName"
            ddlStudents.DataValueField = "StudentID"
            ddlStudents.DataBind()
       Else
            ddlStudents.Items.Add("No Students Found")
       End If

-Baan

DotNetLover_Baan,
out of curiosity why is it a bad idea to bind a ddl to a datareader?  also, I have bound a datareader to a ddl and added items afterward and it worked for me..
-Trevor

Hey Baan.. good to see you.
whats with  >> it is not a good idea to bind ddl to a datareader

DataReader is a lighter version of DataSet.. one would have thought it is better to use the former than the latter for read-only purposes.

The purpose of my statement, is to use minimum resources, definitely with better functionalities. >>DataReader is a lighter version of DataSet<< you see.. it says everything. Moreover Datareader is a forward only binding.
Once again, here I am not comparing DataSet and DataReader. I know that, it is strictly on the designer, and  what and how needs. They discussed a lot on this, here  http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/default.aspx  .  
Even I use DataReader for all my filtering database queries, in a lot of other issues too. I was just trying to , you know.. as they say... "Broadening the horizon" :)

Baan.. I do believe you are digressing from the topic :))
The question was - why NOT dataReader with DDL and not DataReader vs DataSet !

It is an interesting topic of discussion 'DataReader vs DataSet'. And a programmer based on whether he wants fast read-only access to data (datareader) or the whole functionality of update-able, forward-backward acccess of data(dataset).
By the by that article was really good.. Thanks... now to the real question - why NOT dataReader with DDL :))

on second thoughts... we might as well let it pass for now :)).

MargusLehiste .. did any of these options work out for you?

I'm still wanting to know about the DataReader w/ DDL issue - maybe we could discuss elsewhere?

Hey thanks for all of you for such a good feedback - however I have to give point to the comment which came closest to the solution at hand (I do take under consideration using datasets but it seems in this case reader is fast)

Daffodils - your code not working:
 thisConn.Open()
        cmdStuSelect = New SqlCommand(stu_SQL, thisConn)
        dtr = cmdStuSelect.ExecuteReader
'Modified Daffodil's code went here
If dtr Is Nothing Then
            ddlStudents.Items.Add(New ListItem(0, "No Students Found"))
End If
' ....

Code WHAT WORKED:

Private Sub BindStudent()
        ddlStudents.Items.Clear()
'.... connections declarations etc
 thisConn.Open()
        cmdStuSelect = New SqlCommand(stu_SQL, thisConn)
        dtr = cmdStuSelect.ExecuteReader

        If (dtr.Read) Then
            ddlStudents.DataSource = dtr
            ddlStudents.DataTextField = "SName"
            ddlStudents.DataValueField = "LSID"
            ddlStudents.DataBind()
        Else
            ddlStudents.Items.Add(New ListItem("No Students Found", 9999))
        End If

thisConn.Close()
End Sub