Databind with Stored Procedures

alexcryout
alexcryout used Ask the Experts™
on
Hi Experts,

I use one Stored Procedures to fill two dropdown list boxes:

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("GetCodes", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try
   myConnection.Open()
   Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
   Return result
Catch Ex As Exception
   Return Nothing
End Try

And my databind is:
Dim SNs As SqlDataReader = CodesDB.GetAvailableCodes()

dlstSN.DataSource = SNs
dlstSN.DataTextField = "SN"
dlstSN.DataValueField = "ID"
dlstSN.DataBind()
 
dlstCodes.DataSource = SNs
dlstCodes.DataTextField = "Code"
dlstCodes.DataValueField = "ID"
dlstCodes.DataBind()

Then I got the "Invalid attempt to call FieldCount when reader is closed." error on the second databind.

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
I would suggest you change code to fill DataTables and use them as datasource because the readers keep the connection to the DB open.
Top Expert 2011

Commented:
I think this is your problem:

 
  Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Open in new window


It should be more like:

Try
   myConnection.Open()
   Dim result As SqlDataReader = myCommand.ExecuteReader()
   Return result
Catch Ex As Exception
   Return Nothing
End Try

And my databind is:
Dim SNs As SqlDataReader = CodesDB.GetAvailableCodes()

dlstSN.DataSource = SNs
dlstSN.DataTextField = "SN"
dlstSN.DataValueField = "ID"
dlstSN.DataBind()
result.close()
 
dlstCodes.DataSource = SNs
dlstCodes.DataTextField = "Code"
dlstCodes.DataValueField = "ID"
dlstCodes.DataBind()
result.close()

Open in new window

Sr. System Analyst
Commented:
convert datareader to datatable

dim dt as new DataTable();
dt.Load(SNs)

and then use dt not SNs to bind your data...

Author

Commented:
Thanks HainKurt.
I use datatable and it works perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial