DropDownList Data Binding

[Joe Delphi]

Hi,

I am attempting to bind a dropdown list to a SQLDataReader My code
looks like this:

Dim DBComm As New SqlCommand
Dim DBReader As SqlDataReader
DBComm.Connection = DBConn
DBComm.CommandText = "SELECT TESTER FROM PERSONNEL ORDER BY TESTER"
DBReader = DBComm.ExecuteReader()

'Bind the drop down list to the DBReader'
If DBReader.HasRows() Then
ddlstSubBy.DataSource = DBReader
ddlstSubBy.DataTextField = "TESTER"
ddlstSubBy.DataValueField = "TESTER"
ddlstSubBy.DataBind()
End If

The PERSONNEL database table has a single field named TESTER.

When the program attempts to execute the .DataBind() statement, I get an
exception message that says:

"Index 0 is not non-negative and below total rows count."

What does this mean? I know that the query returned 6 rows so the problem
is not that the SQLDataReader is empty.

Any help appreciated.


JD

 

[Teemu Keiski]

Hi,

and you have opened the db connection earlier in that code?

 

[Joe Delphi]

Yes, in fact I moved the SQLConnection opening to inside of this same
routine, as shown below, but I still get the exact same error message:

Private Sub PopulateSubByBox()
Dim DBConn As New SqlConnection
Dim DBComm As New SqlCommand
Dim DBReader As SqlDataReader
DBConn.ConnectionString = ConnStr
DBComm.Connection = DBConn
DBComm.CommandText = "SELECT TESTER FROM PERSONNEL ORDER BY TESTER"
DBConn.Open()
DBReader = DBComm.ExecuteReader(CommandBehavior.CloseConnection)

'Bind the drop down list to the DBReader'
If (DBReader.HasRows) Then
ddlstSubBy.DataSource = DBReader
ddlstSubBy.DataTextField = "TESTER"
ddlstSubBy.DataValueField = "TESTER"
ddlstSubBy.DataBind()
End If

DBReader.Close()
DBConn.Close()
End Sub

 

[Teemu Keiski]

Try changing the code to looping the DBReader.

While DBReader.Read()
Dim litem As New ListItem(DBReader("TESTER"))
ddlstSubBy.Items.Add(litem)
End While
DBReader.Close()

Normally the error indeed tries to say that there's not that data available,
so test it by chabnging the code to this. Does it now show the records?