Solved

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

Posted on 2004-08-13
20
424 Views
Last Modified: 2008-03-17
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
Comment
Question by:MargusLehiste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 8

Expert Comment

by:trevorhartman
ID: 11795627
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
 
LVL 8

Expert Comment

by:trevorhartman
ID: 11795646
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
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11795911
Error:

dtr.hasrows is not a member of System.Data.Sqlclient.SqlDataReader
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 8

Expert Comment

by:trevorhartman
ID: 11795924
hasrows isn't a member, but HasRows is :)
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11795928
I develop with VB.net - so Im not sure if this dtr.hasrows is for C# only or there is something else wrong
0
 
LVL 8

Expert Comment

by:trevorhartman
ID: 11795949
one thing is, it is only available in version 1.1 of the .NET framework.
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11795955
Same thing:

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

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

Expert Comment

by:trevorhartman
ID: 11795963
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11795964
So what about FW 1.0 -

I have Visual Studio 2002
0
 
LVL 8

Expert Comment

by:trevorhartman
ID: 11795993
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
 
LVL 8

Accepted Solution

by:
trevorhartman earned 500 total points
ID: 11796116
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
 
LVL 8

Expert Comment

by:daffodils
ID: 11796817
Try..

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

Expert Comment

by:DotNetLover_Baan
ID: 11796831
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
 
LVL 8

Expert Comment

by:trevorhartman
ID: 11796842
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
 
LVL 8

Expert Comment

by:daffodils
ID: 11796855
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11797291
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
 
LVL 8

Expert Comment

by:daffodils
ID: 11797362
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
 
LVL 8

Expert Comment

by:daffodils
ID: 11797375
on second thoughts... we might as well let it pass for now :)).

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

Expert Comment

by:trevorhartman
ID: 11797381
I'm still wanting to know about the DataReader w/ DDL issue - maybe we could discuss elsewhere?
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11814994
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question