In ASP.NET, how come .HasRows does not work properly?

Posted on 2011-03-18
Last Modified: 2012-05-11
I have the following code:

Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString1"))
                Dim command As New SqlCommand(queryString, connection)
                command.Parameters.Add("@param_ClientID", Data.SqlDbType.NVarChar, 10).Value = ClientID.Trim.ToString
                Dim reader As SqlDataReader = command.ExecuteReader()
                    If reader.HasRows Then
                        stPrompt = "Client Id Check: " & reader(0).ToString

I am working with test data so i know the client does not exist.  When it gets to "If reader.HasRows" it is always coming up as TRUE.  When it gets to the "reader(0).ToString", it gives an error "Invalid attempt when no data is present."

Am I using .HasRows properly?

Should I do something other than "SqlDataReader = command.ExecuteReader()"?

Question by:excellis
  • 4
  • 2
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
What does your query look like? A query that return no records will result in HasRows being false; a query that returns a count of 0 to indicate no records has 1 records, so HasRows would be true.
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
Use :
While (Reader.Read)
End While

Author Comment

Comment Utility

Here is the Stored Procedure

      @param_ClientID nvarchar(10) = NULL

      SELECT *
      FROM Master_Clients
      WHERE Client_ID LIKE COALESCE(@param_ClientID, '%');

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


Author Comment

Comment Utility
The Stored Procedure is used in other parts.  I did not want to make one specific for this part of the program.  

Before I get to this part of the program, I have already checked that there is something in the Client ID field so I know that the parameter will be something other than a blank or null.
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
You need to call :

If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine(reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Console.WriteLine("No rows found.")
        End If


Accepted Solution

excellis earned 0 total points
Comment Utility

Doesn't solve the problem of why reader.HasRows is always coming up as TRUE.  I will try working with your 1st suggestion "While (Reader.Read)" and see if i can get that to work.

Author Closing Comment

Comment Utility
No one answered the initial question.  Got work arounds.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 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