Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-08-13
20
Medium Priority
?
429 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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