Ad:

VB.NET Discussion Thread
Unsolved
Views: 10215

Jul 22nd, 2008

filling data in combo box from SQL server

Expand Post »

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.

vb.net Syntax (Toggle Plain Text)
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 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

vb.net Syntax (Toggle Plain Text)
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
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

Similar Threads

Understanding ASP classes in ASP

buffdaemon_live

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jul 2008

Permalink

Jul 22nd, 2008

-1

Re: filling data in combo box from SQL server

Use another SQL statement. Construct it like this-

vb.net Syntax (Toggle Plain Text)
Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem
Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem

tuse

Reputation Points: 32

Solved Threads: 14

Junior Poster

Offline

173 posts
since Jul 2007

Permalink

Jul 22nd, 2008

Re: filling data in combo box from SQL server

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

Click to Expand / Collapse Quote originally posted by tuse ...

Use another SQL statement. Construct it like this-
vb.net Syntax (Toggle Plain Text)
Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem
Dim strSQL2 As String = "SELECT product_id FROM product where product_name=" & Me.cmbDropDown.SelectedItem

buffdaemon_live

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jul 2008

Permalink

Jul 23rd, 2008

Re: filling data in combo box from SQL server

you must have other key to differentiating the same product_name.

Jx_Man

Reputation Points: 1182

Solved Threads: 392

Posting Sensei

Offline

3,140 posts
since Nov 2007

Permalink

Aug 20th, 2011

Re: filling data in combo box from SQL server

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

VB.NET Syntax (Toggle Plain Text)
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
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

Last edited by Narue; Aug 22nd, 2011 at 2:44 pm. Reason: Added code tags

Airheart

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2011

Permalink

Dec 8th, 2011

Re: filling data in combo box from SQL server

buff see below

VB.NET Syntax (Toggle Plain Text)
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
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

Reputation Points: 11

Solved Threads: 14

Posting Whiz in Training

Offline

204 posts
since Dec 2009

Permalink

Dec 8th, 2011

Re: filling data in combo box from SQL server

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

Last edited by bluehangook629; Dec 8th, 2011 at 12:56 pm.

bluehangook629

Reputation Points: 11

Solved Threads: 14

Posting Whiz in Training

Offline

204 posts
since Dec 2009

Previous Thread in VB.NET Forum Timeline: Moving 2 Listbox

Next Thread in VB.NET Forum Timeline: select in vb.net

DaniWeb Message

Cancel Changes

User Name
Password