Counting items in a datareader?

stu20001 · January 5th, 2005, 11:59 AM

Hi there - I'm a little stuck with something and could do with some help. The situation I have is as follows.

On my webform, the user will enter a zip code and be presented with a list of centres that are within a set distance from there

This involves two tables. Table_ZipCodes contains grid references (a northing and an easting) for a whole bunch of zip codes; Table_centres contains address details of training centres.

On clicking submit I need to run a process that retrieves the northing and easting for the zip code and then passes this onto a stored procedure that performs the distance calculation and returns the details of the centres that meet with the criteria.

I have got this working as follows:

1) User enters zip code
2) A datareader object is declared which then uses a stored procedure to return the 'easting' and 'northing' for the zip code. These values are then stored as variables

Code:

cnCentreFinder.Open()
   drPCodeValues = cmdPostcodeSelect1.ExecuteReader
   drPCodeValues.Read()
   varEasting = drPCodeValues("Easting")
   varNorthing = drPCodeValues("Northing")

3) These variables are then used as parameters for the 2nd stored procedure, along with the distance value (selected from a dropdown menu):

Code:

cmdSelectCentres.Parameters("@InNorthing").Value = intNorthing

Code:

   cmdSelectCentres.Parameters("@InEasting").Value = intEasting
   cmdSelectCentres.Parameters("@Distance").Value = ddlDistance.SelectedItem.Value
   drPCodeValues.Close()

4) Finally the results are bound to a datagrid and displayed:

Code:

daCentreResults.Fill(dsCentreResults)

Code:

   dgCentreResults.DataSource = dsCentreResults
   dgCentreResults.DataBind()

This all works fine. However, what I now need to add is a way of telling the user if the zip code they entered does not exist. The obvious way is to see if there are any values in the datareader that stores the Northing and Easting, but everything I read seems to suggest that you can't do a .Count operation on a datareader. Is this correct? If so, how can I get round this?

Imar · January 5th, 2005, 12:46 PM

Hi there,

Well, you can, sort of. In .NET 1.1, a DataReader has a HasRows property. It doesn't give you an exact count, other than that it is zero or not.

Take a look here for more details:

http://msdn.microsoft.com/library/de...srowstopic.asp

Alternatively, you can request the count from the object you bound your data to. E.g.:

If myGrid.Items.Count = 0 Then
myGrid.Visible = False
lblMessage = No Zips found"
Else
' Records found, no need to do anything.
End If

HtH,

Imar

stu20001 · January 6th, 2005, 07:03 AM

Great - thanks for that.

The problem I had is that I don't want the data to bind to anything, just to pass on variables to the parameters. So I can't do a count on a datagrid, etc.

The HasRows property is perfect for this particular situation as the datareader will only ever have 1 row, so I just need to know if its true or false. But if this property is going in the next version of .NET, how would I get round this then?

Imar · January 6th, 2005, 07:43 AM

This property is still available in .NET 2:

http://msdn2.microsoft.com/library/7fafbb26.aspx

I mention 1.1 because it's *not* available in 1.0

If you're not binding the data, you can also

a) Use a Command with an output parameter that returns the count

b) Use the Read method to see if there's data in the reader.

Cheers,

Imar