Solved

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

Posted on 2004-08-13
20
415 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now