Combo Help VB.Net

[Mike Bishop]

I have an sql table called Stock with two fields called StockID and StockCode.

I want the user to select the stockcode from a combo which in turn populates the
stockid for that item into a lable on my form.

I have already populated the stockcode into the combo but dont know how to compelte the rest.

I have only been using Visual Studio for about 4 days, hope there is someone out there with the knowledge to help a newbie.

Imports System.Data.SqlClient

Public Class cbo2
    Private Sub cbo2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connString As String = "Data Source=localhost;Initial Catalog=invsystem;User ID=mbish;Password=mbish"
        Dim conn As New SqlConnection(connString)
        ' fillComboBox1()
        Dim strSQL As String = "Select * From Stock"
        Dim DA As New SqlDataAdapter(strSQL, conn)
        Dim DS As New DataSet
        DA.Fill(DS, "Stock")
        'Create and populate the DataTable to bind to the ComboBox:
        Dim dt As New DataTable
        dt.Columns.Add("Stockcode", GetType(System.String))
        dt.Columns.Add("StockID", GetType(System.String))
        ' Populate the DataTable to bind to the Combobox.
        Dim drDSRow As DataRow
        Dim drNewRow As DataRow
        For Each drDSRow In DS.Tables("Stock").Rows()
            drNewRow = dt.NewRow()
            drNewRow("StockCode") = drDSRow("Stockcode")
            drNewRow("StockID") = drDSRow("StockID")
            dt.Rows.Add(drNewRow)
        Next
        'Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property. 
        ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
        With ComboBox1
            .DataSource = dt
            .DisplayMember = "StockCode"
            .ValueMember = "StockID"
            .SelectedIndex = 0
        End With
    End Sub
End Class

[crapulency]

Hi Mike, You can fill a datatable directly without having to create a dataset. When you set a combobox's datasource to a datatable, you don't have to loop through the rows manually. So you can trim down your code quite a lot:

Dim connString As String = "Data Source=localhost;Initial Catalog=invsystem;User ID=mbish;Password=mbish"
        Dim conn As New SqlConnection(connString)
        Dim strSQL As String = "Select * From Stock"
        Dim DA As New SqlDataAdapter(strSQL, conn)
        Dim dt As New DataTable
        'Populate the datatable
        DA.Fill(dt)
        'Bind the stock datatable to the combobox
        With ComboBox1
	    .DropDownStyle = ComboBoxStyle.DropDownList
            .DataSource = dt
            .DisplayMember = "StockCode"
            .ValueMember = "StockID"
        End With

In order to update your label with the stockid, use the combobox SelectedIndexChanged event like this:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Label1.Text = Me.ComboBox1.SelectedValue.ToString
    End Sub

[Mike Bishop]

thank you fot that, i have been trying this for over 8 hours today.

[Mike Bishop]

Is there any way using this method i can pull more information from sql.

Ie at the moment this just pulls through the stockid, can i also pull through
the stockDes as .valuemember

?

[crapulency]

yes. You can always keep a reference to your stock datatable using a variable with form scope and access any field of any row. Place this declaration towards the top of your form code:

Private StockDataTable as Datatable

After you have successfully filled your local datatable (dt) you can do this

StockDataTable  = dt

THereafter, you can get at any row using:

DIm row as dataRow = StockDataTable.Rows(ComboBox1.SelectedIndex)

Like this, for example:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim row As DataRow = StockDataTable.Rows(ComboBox1.SelectedIndex)
        Label1.Text = row.Item(0).ToString
        Label2.text = row.Item(1).ToString
        Label3.text = row.Item(2).ToString
        'etc etc
    End Sub

Alternatively, if you want to link many controls to your datatable, consider databinding using wizards - Create a datasource in the datasource window, select "Detai" next to your Stock table, and drag the table onto a new form. Experiment from there.