SqlDataReader.Read

  • Thread starter Thread starter rn5a
  • Start date Start date
R

rn5a

The following code calls a function in a DLL which returns a
SqlDataReader (the function in the DLL calls a stored procedure which
returns 6 columns):

Dim boCart As Cart
Dim sqlReader As SqlDataReader

boCart = New Cart
boCart.GetRecords(iUserID)

'Next I want to just display the record in the last column (whose
ordinal no=5) corresponding to the UserID

While (sqlReader.Read)
Response.Write(sqlReader.GetValue(5))
End While

The above does display the appropriate record. Next I want to display
the rest of the records in a DataList but when I do

dlCart.DataSource = sqlReader
dlCart.DataBind()

the DataList doesn't display the records. How do I populate the
DataList with the rest of the records that the stored procedure
retrieves?
 
You've already read through the entire result set. Now it's done. You have
to rerun the query again if you want to bind to it. Or, you have to use a
more persistent data storage mechanism such as a datatable, which keeps the
result set in memory. The datareader just fetches the results from the
database as you request each row, then throws the old one away.
 
The following code calls a function in a DLL which returns a
SqlDataReader (the function in the DLL calls a stored procedure which
returns 6 columns):

Dim boCart As Cart
Dim sqlReader As SqlDataReader

boCart = New Cart
boCart.GetRecords(iUserID)

'Next I want to just display the record in the last column (whose
ordinal no=5) corresponding to the UserID

While (sqlReader.Read)
Response.Write(sqlReader.GetValue(5))
End While

The above does display the appropriate record. Next I want to display
the rest of the records in a DataList but when I do

dlCart.DataSource = sqlReader
dlCart.DataBind()

the DataList doesn't display the records.
Click to expand...

That's correct - because you've already iterated right through the
DataReader in your While loop. DataReader objects are read-only
forward-scrolling only - once you've got to the end of your DataReader, you
can't go back to the start again and use it for something else.
How do I populate the DataList with the rest of the records that the
stored
procedure retrieves?
Click to expand...

Several options...

1) Don't use a DataReader - use a DataSet instead.

2) Bind your dlCart object first, close the DataReader, then iterate through
the dlCart object to do your Response.Write...

3) Query the database twice - really stupid idea, but of course it would
solve your problem!

Plus many other options, I'm sure...
 
Back
Top