Avatar of alexcryout
alexcryout
Flag for United States of America asked on

Databind with Stored Procedures

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.
ASP.NETVisual Basic.NET

Avatar of undefined
Last Comment
alexcryout

8/22/2022 - Mon
Nasir Razzaq

I would suggest you change code to fill DataTables and use them as datasource because the readers keep the connection to the DB open.
sammySeltzer

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

ASKER CERTIFIED SOLUTION
HainKurt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
alexcryout

ASKER
Thanks HainKurt.
I use datatable and it works perfectly.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes