[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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

Thing is that when no students are found I want to display a in DropDownList ("ddlStudents")
a message - "<red>No Students Found</red>"
I have a datareader which like this:

Private Sub BindStudent()
 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 dtr As SqlDataReader
        Dim cmdStuSelect As SqlCommand

        thisConn.Open()
        cmdStuSelect = New SqlCommand(stu_SQL, thisConn)
        dtr = cmdStuSelect.ExecuteReader
        lblInfo.Text = dtr.RecordsAffected
        ddlStudents.DataSource = dtr
        ddlStudents.DataTextField = "SName"
        ddlStudents.DataValueField = "StudentID"
        ddlStudents.DataBind()

        thisConn.Close()
End Sub
0
MargusLehiste
Asked:
MargusLehiste
  • 9
  • 5
  • 4
  • +1
1 Solution
 
trevorhartmanCommented:
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
0
 
trevorhartmanCommented:
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")
0
 
MargusLehisteAuthor Commented:
Error:

dtr.hasrows is not a member of System.Data.Sqlclient.SqlDataReader
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
trevorhartmanCommented:
hasrows isn't a member, but HasRows is :)
0
 
MargusLehisteAuthor Commented:
I develop with VB.net - so Im not sure if this dtr.hasrows is for C# only or there is something else wrong
0
 
trevorhartmanCommented:
one thing is, it is only available in version 1.1 of the .NET framework.
0
 
MargusLehisteAuthor Commented:
Same thing:

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

Note - vb.net is NOT case sensitive (I think C# is)
0
 
MargusLehisteAuthor Commented:
So what about FW 1.0 -

I have Visual Studio 2002
0
 
trevorhartmanCommented:
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
0
 
trevorhartmanCommented:
another option would be to have your dropdownlist by default have a no students found option in it

<asp:dropdownlist runat="server" id="ddlStudents">
  <listItem>No Students Found</listItem>
</asp:dropdownList>

then in your bind student sub:

If (dtr.Read())
        ddlStudents.DataSource = dtr
        ddlStudents.DataTextField = "SName"
        ddlStudents.DataValueField = "StudentID"
        ddlStudents.DataBind()
End If

-Trevor
0
 
daffodilsCommented:
Try..

If dtr Is Nothing Then ddlStudents.Items.Insert(0,"No students found")
0
 
DotNetLover_BaanCommented:
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
0
 
trevorhartmanCommented:
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
0
 
daffodilsCommented:
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.
0
 
DotNetLover_BaanCommented:
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" :)
0
 
daffodilsCommented:
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 :))
0
 
daffodilsCommented:
on second thoughts... we might as well let it pass for now :)).

MargusLehiste .. did any of these options work out for you?
0
 
trevorhartmanCommented:
I'm still wanting to know about the DataReader w/ DDL issue - maybe we could discuss elsewhere?
0
 
MargusLehisteAuthor Commented:
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
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now