SqlDataReader.Read

[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?

 

[Marina Levit [MVP]]

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.

 

[Mark Rae]

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.

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?

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