asked on

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

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
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Try
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()"?

Matt

kaufmed

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.

Paul Jackson

Use :
While (Reader.Read)
...
End While

excellis

ASKER

To:kaufmed

Here is the Stored Procedure

ALTER PROCEDURE dbo.uspGET_Clients

      (
      @param_ClientID nvarchar(10) = NULL
      )
AS

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

RETURN

excellis

ASKER

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.

Paul Jackson

You need to call reader.read :

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

ASKER CERTIFIED SOLUTION

excellis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

excellis

ASKER

No one answered the initial question. Got work arounds.