filling data in combo box from SQL server

[buffdaemon_live]

Dear all,

I wanted to fill the combo box with the data from my SQL server database.
There are two column in the table, product_id and product_name.

for example:

product_id| product_name
---------- --------------
1| pencil
2| NoteBook

I was successful in displaying the product name in combo box. The code is given below. I wanted to show the product_name in the combo box and when the user selects the product_name from combo box i should be able to get its product_id. How do i do it? Please help me guys for my academic project.

Below is my code.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim conn As New System.Data.SqlClient.SqlConnection("Network Library=DBMSSOCN;" & "Data Source=abacus\mydatabase;" & "Initial Catalog=CADCAM2;" & "Integrated Security =True;" & "MultipleActiveResultSets=True;")
Dim strSQL As String = "SELECT * FROM product"
Dim da As New System.Data.SqlClient.SqlDataAdapter(strSQL, conn)
Dim ds As New DataSet
da.Fill(ds, "product")

With Me.cmbDropDown
.DataSource = ds.Tables("product")
.DisplayMember = "product_name"
.ValueMember = "product_id"
.SelectedIndex = 0
End With

end sub

Private Sub cmbCproduct_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCproduct.SelectedValueChanged

'when the user selects the product_name from the combo box i should be able to get the product_id.

End Sub

[tuse]

Use another SQL statement. Construct it like this-

Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem

[buffdaemon_live]

The problem with this query is that i can have some of the "product_name" same. Any idea??

Use another SQL statement. Construct it like this-

Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem

[Jx_Man]

you must have other key to differentiating the same product_name.

[Airheart]

hi all
i want when i select data from my combobox the data apprear in my textbox1
from my sql database server there are two column in my table itemname,itemcode
itemname is already in my combobox
i want itemcode appear in textbox1 when i select itemname from combobox
thank you
and this is my code

Imports System.Data.SqlClient
Public Class Received_Items
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim dr As SqlDataReader
    Dim strsql As String, i As Integer = 0


    Private Sub Received_Items_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con = New SqlConnection("Data Source=win7-pc\sqlexpress;Initial Catalog=catering;Integrated Security=True;Pooling=False")
        con.Open()
        cmd = New SqlCommand(strsql, con)
        strsql = "select distinct itemname from bstore"
        cmd = New SqlCommand(strsql, con)

        dr = cmd.ExecuteReader

        selectComboBox1.Items.Clear()
        Do While dr.Read
            selectComboBox1.Items.Add(dr("itemname"))
        Loop
        dr.Close()


    End Sub

        Private Sub selectComboBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles selectComboBox1.TextChanged
        If Txtitmcode.Text = "" Then
            Exit Sub
        End If
        Dim strsql As String, i As Integer = 0
        con = New SqlConnection("Data Source=win7-pc\sqlexpress;Initial Catalog=catering;Integrated Security=True;Pooling=False")
        con.Open()

        strsql = "select * from bstore where itemcode=" & selectComboBox1.SelectedItem


        cmd = New SqlCommand(strsql, con)

        dr = cmd.ExecuteReader

        If Not dr.HasRows Then
            MsgBox("not find")
        Else

            dr.Read()
            Txtitmcode.Text = dr("itemcode")

        End If


        dr.Close()

    End Sub
End Class

[bluehangook629]

buff see below

Private Sub cmbCproduct_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCproduct.SelectedValueChanged
	'when the user selects the product_name from the combo box i should be able to get the product_id.
	Dim productID As Integer
	productID = cmbCproduct.SelectedValue
	
	msgbox(productID)
End Sub

[bluehangook629]

for you airheart,
set your item name displayed member and item code as valued member.
At .selectedvaluechanged have textbox1 = .selectedvalue
but before you need to populate your combobox and set your combobox properties described above.
see buffdaemon's code for populating combobox.

If you need further help start a new thread

[MARWAN_3]

Try this code.....

 Dim DT As New DataTable
Dim Product_ID As Integer

 For i As Integer = 0 To DT.Rows.Count - 1

            If Trim(cmbDropDown.Text) = DT.Rows(i).Item(1) Then

                Product_ID = DT.Rows(i).Item(0)


            End If
        Next

[Mike Bishop]

What i do in this case is use a datagrideview and make it behave like a combo box.
I set the datagridview to one coloum in width, and when you select your required
option use the datagridview row click option to bring back the data you require