First record dropped from DataReader

E

evaleah

Well-known member
Joined
May 14, 2003
Messages
55
I am using a DataReader to fill a DropDownListBox using the following code:

Code: 
    Private Sub FillDepartmentList()
        Dim cmdDepartment As New SqlCommand("SELECT Department, DepartmentID FROM tb_Department", sqlCon)
        Dim drDepartment As SqlDataReader = cmdDepartment.ExecuteReader

        With ddlDepartment
            While drDepartment.Read
                .DataSource = drDepartment
                .DataTextField = "Department"
                .DataValueField = "DepartmentID"
                .DataBind()
            End While
            .Items.Insert(0, New ListItem())
            .SelectedIndex = 0
        End With

        drDepartment.Close()
    End Sub

It does a wonderful job except it keeps leaving out the first record in the table. In other words if there were four departments (Office, Yard, Garage, Fence), then only Yard, Garage, and Fence are showing up in the list.

Any ideas would be *very* helpful.

Thanks,
Eva
 
you shouldnt have to keep reseting the data properties and calling DataBind for each row in the data reader. Try the following and see if it works

Code: 
Private Sub FillDepartmentList()
        Dim cmdDepartment As New SqlCommand("SELECT Department, DepartmentID FROM tb_Department", sqlCon)
        Dim drDepartment As SqlDataReader = cmdDepartment.ExecuteReader

With ddlDepartment
         .DataSource = drDepartment
         .DataTextField = "Department"
         .DataValueField = "DepartmentID"
         .DataBind()
         .SelectedIndex = 0
End With          

drDepartment.Close()
End Sub
 
evaleah, keep in mind that if you use Databinding, then you dont need to loop the DataReader.
If you want to Add Items one at a time (no binding) then you can loop through the Reader.
 
When I use the code PlausiblyDamp recommends then I dont get the value in the dropdownlist. Instead I get sqlclient.command.db... something. I dont remember exactly. When I used the while this gave me all the values except for the first one. Without the while, in debug mode there is never a value present.

Thanks for the suggestion though. Any other ideas?

Eva
 
What PlausiblyDamp recommended should work, except you cant get value in your DataReader.

Check whether your DataReader got value? this is just a simple Data Binding, it should work fine.
 
to get the values from the selected item of the dropdown...

Code: 
x = ddlDepartment.SelectedItem.Value  (from ValueField)  

x = ddlDepartment.SelectedItem.text (from TextField)
 
Actually, I got that to work but now I have another issue with this same thing.

I want to put this all in a class to I can call the routine from several forms. I dont want to make it a user control because there are actually several of these routines repeated and I want to be able to put a dropdownlist on my form and fill it with one of the routines from the class.

However, when I attempt to call the routine from the class I get a message that the Object reference is not set to an instance of an object. I have named the dropdownlist the same thing in both the class and the form.

If that is not clear please feel free to ask me for clarification.

Thanks again!

Eva
 
Yes, what do you mean by call the routine in a class? If the error message is object reference is not set... then I think you should show your code here... :)
 
Ah... I realized this was a failure in my own logic. It needs to be a user control. I give in!

Thanks!
 
Back
Top