CODEWITHSUNDEEP
sql-servervb.netwinformscombobox
Mark C.
Mark C.

Reputation: 6450

Populate Combobox with table fields and use to insert into new record

I am entering new employees into a database via WinForms.

I have Company table that looks like:

+----------+--------------+
| CMPNY_ID |   CMPNY_NM   |
+----------+--------------+
|    01    |      ABC     |
+----------+--------------+
|    02    |      DEF     |
+----------+--------------+

I want to populate a Combobox so the user sees CMPNY_NM --> "ABC". When the user enters a new employee, they can choose a company name. The CMPNY_ID associated with theCMPNY_NM they chose would be added to the employee's record.

If you were entering a new record you would enter in Employee first name, last name, and select from the combobox ABC. That would then hold the PK associated with ABC --> "01" and would be inserted into the database with the rest of the information. The data would look like:

+----------+--------------+------------+
|  EMP_ID  |    EMP_NM    |  CMPNY_ID  |
+----------+--------------+------------+
|    01    |      ABC     |     01     |
+----------+--------------+------------+

I don't have much, but so far I have:

    Dim ds As New DataSet()
    Dim conn As New SqlConnection("Data Source=SQLTEST_HR,4000\SQLEXPRESS;Integrated Security=True")

    Dim qry As String

    qry &= "SELECT CMPNY_SEQ_ID, CMPNY_NM"
    qry &= "FROM CMPNY"

    conn.Open()
    Using da As New SqlDataAdapter(qry, conn)
        da.Fill(ds, "CMPNY")
    End Using

    With PaymentCbx
        .DisplayMember = "CMPNY_NM"
        .DataSource = ds.Tables("CMPNY")
        .SelectedIndex = 0
    End With

    MyBase.OnLoad(e)

Which doesn't seem to be doing anything, really.

Any help is welcomed.

Upvotes: 1

Views: 1889

Answers (2)

Heena Chhatrala
Heena Chhatrala

Reputation: 242

try this ON FORM LOAD 

    Dim ds As New DataSet()
    Dim conn As New SqlConnection("YOUR CONNECTION STRING")

    Dim qry As String = ""

    qry &= "SELECT CMPNY_ID, CMPNY_NM "
    qry &= " FROM CMPNY"

    conn.Open()
    Using da As New SqlDataAdapter(qry, conn)
        da.Fill(ds, "CMPNY")
    End Using

    PaymentCbx.DataSource = ds.Tables("CMPNY")
    PaymentCbx.DisplayMember = "CMPNY_NM"
    PaymentCbx.ValueMember = "CMPNY_ID"
    PaymentCbx.SelectedIndex = 0

and on Combobox SelectedValueChanged event write code

 Dim companyid As String = PaymentCbx.SelectedValue.ToString

Upvotes: 2

Jeremy
Jeremy

Reputation: 108

I think you're just missing the combo box's ValueMember. DisplayMember sets what's displayed in the combobox, and ValueMember selects the value that the displayed value represents. 

Dim dt As New DataTable()
Dim conn As New SqlConnection("Data Source=SQLTEST_HR,4000\SQLEXPRESS;Integrated Security=True")

Dim qry As String = "SELECT CMPNY_SEQ_ID, CMPNY_NM FROM CMPNY"

conn.Open()
Using da As New SqlDataAdapter(qry, conn)
    da.Fill(dt)
End Using

With PaymentCbx
    .DisplayMember = "CMPNY_NM"
    .ValueMember = "CMPNY_SEQ_ID"
    .DataSource = dt
    .SelectedIndex = 0
End With

MyBase.OnLoad(e)

In your button click event, once your user fills out the rest of the information, you can get the value by using the .SelectedValue property of the combo box.

Dim companyId as Integer = PaymentCbx.SelectedValue

Hope that helps.

Upvotes: 1

Related Questions