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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexcryout

ASKER

Thanks HainKurt.
I use datatable and it works perfectly.