combo box question regarding data reading

halu,

my friend and i got this problem a while ago. we don't know what wrong. it's about combo box with data reading. i don't know if my question meets our need here. sorry for that. hehe. cheers...

it's about retreiving Categories Table in the NorthWind and adding the CategoryName in the combo box. when name selected it supposed to display the CategoryID on the combo box text property. i don't know what's wrong... what event should we override? i tried this one...

VB Code:

Dim cn As New SqlConnection("integrated security=true;initial catalog=northwind")
    Dim cm As New SqlCommand()
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cn.Open()
        cm.Connection = cn
        cm.CommandType = CommandType.StoredProcedure
        With cm
            .Parameters.Clear()
            .CommandText = "GetCategoriesTable"
            Dim dr As SqlDataReader = .ExecuteReader
            While dr.Read
                ComboBox1.Items.Add(dr.GetValue(1))
            End While
            dr.Close()
        End With
    End Sub
 
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        With cm
            .Parameters.Clear()
            .CommandText = "GetCategoriesRowOnName"
            .Parameters.Add("@Name", SqlDbType.NVarChar, 15).Direction = ParameterDirection.Input
            .Parameters("@Name").Value = ComboBox1.SelectedItem
            Dim dr As SqlDataReader = .ExecuteReader
            dr.Read()
            ComboBox1.Text = dr.GetValue(0)
            dr.Close()
        End With
    End Sub

what did i miss? thanx in advance guys...

--ayan

i would do somthing easier than that i would use a dataset and read all the data from the categories table into that like this:

set up your sql dataadapter using command type as text

dim dsTable as new dataset

dsTable.Tables.Add("Table")

sqldataadapter.commandtext ="Select * From Categories"

sqldataadapter.fill(dsTable.Tables("Table"))

With Combobox
.datasource = dsTable.Tables("Table")
.displaymember = "CategoryName"
.Valuemember = "CategoryID"
end with

that should load them all into the combobox

here is the code needed:

thanx nokia... i just got something today. i learn something... nice code you have. thanx man, i know what you mean... i would have a masking-like. having the name on combo box and the id as value... thanx so much...

cheers,
--ayan