New to .Net sqldatareader question

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

Thanks in advance.

I have two listboxes on a single asp page. I am trying to
use sqldatareader to populate both using two seperate sql
stored procs. I can populate each box seperately using
two different methods but when I try to populate them both
no the same page I get "InvalidOperationException: Invalid
attempt to FieldCount when reader is closed"

WHY???

Here is the code on the VB code behind page:

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'pop1()
'pop2()

End Sub
Private Sub pop1()
Dim cnstr As String = "serverinfo is here"
Dim cn As New SqlConnection()
Dim cmd As New SqlCommand()
cn.ConnectionString = cnstr
Try

'*******Populate the Info Packets
'Set up Stored Proc
cmd.CommandText = "LookupFind"
cmd.CommandType() = CommandType.StoredProcedure
cmd.Connection = cn

'Pass Parameters
cmd.Parameters.Add("@LookupType", "Info")

'Execute
cn.Open()

Dim dr As SqlDataReader = cmd.ExecuteReader()

chkInfo().DataSource = dr
chkInfo().DataTextField = "LookupDesc"
chkInfo().DataValueField = "pkLookupId"
DataBind()

'Cleanup
dr.Close()

Catch

'Throw the previous exception to the caller
Throw

Finally

'Dispose is used in place of close
'Close is called by dispose
cn.Dispose()
cn = Nothing

End Try

End Sub

Private Sub pop2()
Dim cnstr As String = "serverinfo is here"
Dim cn As New SqlConnection()
Dim cmd As New SqlCommand()

cn.ConnectionString = cnstr

Try

'*******Populate the Info Packets
'Set up Stored Proc
cmd.CommandText = "ResortFill"
cmd.CommandType() = CommandType.StoredProcedure
cmd.Connection = cn

'Pass Parameters
'cmd.Parameters.Clear()

'Execute
cn.Open()

Dim dr As SqlDataReader = cmd.ExecuteReader()

chkResort().DataSource = dr
chkResort().DataTextField = "ResortName"
chkResort().DataValueField = "pkResortId"
DataBind()

'Cleanup
dr.Close()

Catch

'Throw the previous exception to the caller
Throw

Finally

'Dispose is used in place of close
'Close is called by dispose
cn.Dispose()
cn = Nothing

End Try

End Sub
 
You should not be binding to a datareader.. Use a dataset, dataview or
datatable...
an alternative if it's not appropriate to bind to a dataset might be
something like this..

(warning: this is pseudo code)
With dr
While .Read
Dm objListItem As New
ListItem(.GetValue(.GetOrdinal("LookupDesc")),.GetValue(.GetOrdinal("pkLooku
pId")),))
ListBox2.Items.Add(Item)
End While
End With

When your page is rendering the controls datasource is enumerated during the
binding process, this won't work because your datareader is closed..
 
I have two listboxes on a single asp page. I am trying to
use sqldatareader to populate both using two seperate sql
stored procs.
Click to expand...

A DataReader by design is a fast, forward-only mechanism to read your
data one row at a time. It does *not* give you back a set of rows
which could be used as a DataSource.

If you want to use DataReader, you'll have to read the data yourself,
store it in e.g. an ArrayList, and then use that ArrayList as the
DataSource for your comboboxes.

Alternatively, you can fill a DataTable with the values from your
stored proc, and then use that as a DataSource for your combo boxes -
the DataReader won't work.

Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch
 
Back
Top