Binding combobox

[wjburke2]

I am new to VB.net so I am trying to learn how to create the same project with several methods. My first attempt is trying to use code instead of wizards. I seem to be having a problem creating a combobox that will fill from a table called Personnel and bind to a field in a table called Shift_log.Operator. Shift_log.Operator contains the initials of the operator. I want to display all the operators names allowing the user to select one and then store that value in the shift_Log.Operator field.
Imports System.Data.SqlClient
Imports System.Data

Class frmShift_Log

    Private Conn As New SqlConnection("Data Source=O2WBURKE\SQLEXPRESS;Initial Catalog=LaserMiaintLog;Integrated Security=True")

    Private da As New SqlDataAdapter("SELECT * FROM Shift_Log", Conn)
    Private ds As New DataSet

    Private SQLBld As New SqlCommandBuilder(da)
    Dim MaxRows As Integer
    Dim inc As Integer = 0

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

        Conn.Open()

        da.Fill(ds, "ShiftLog")

        MaxRows = ds.Tables("ShiftLog").Rows.Count
        inc = 0
        NavigateRecords()

        Dim daOper As New SqlDataAdapter("SELECT Name, Initials FROM Personnel", Conn)
        Dim dsOper As New DataSet
        daOper.Fill(dsOper, "Personnel")

        Conn.Close()

        txtOperator.DisplayMember = "Name"
        txtOperator.ValueMember = "Initials"
        txtOperator.DataSource = dsOper


    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If

    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If

    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

        inc = 0
        NavigateRecords()

    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

        inc = MaxRows - 1
        NavigateRecords()

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim strmsg As String
        Try

            ds.Tables("ShiftLog").Rows(inc).Item("Shift_Key") = txtShift_Key.Text
            ds.Tables("ShiftLog").Rows(inc).Item("Operator") = txtOperator.Text
            ds.Tables("ShiftLog").Rows(inc).Item("Log_Date") = txtLogDate.Text
            ds.Tables("ShiftLog").Rows(inc).Item("Shift_Hours") = txtShiftHours.Text
            ds.Tables("ShiftLog").Rows(inc).Item("Operator_Comments") = txtOperComments.Text

            da.Update(ds, "ShiftLog")
            MsgBox("Data updated")
        Catch ex As Exception
            strmsg = "Error - Update " & ex.Message
            MsgBox(strmsg)
        End Try

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        btnCommit.Enabled = True
        btnAdd.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False

        txtShift_Key.Clear()
        txtShift_Key.Clear()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        btnCommit.Enabled = False
        btnAdd.Enabled = True
        btnUpdate.Enabled = True
        btnDelete.Enabled = True

        inc = 0
        NavigateRecords()

    End Sub

    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click

        If inc <> -1 Then
            Dim dsNewRow As DataRow
            dsNewRow = ds.Tables("ShiftLog").NewRow()
            dsNewRow.Item("Shift_Key") = txtShift_Key.Text
            dsNewRow.Item("Operator") = txtOperator.Text
            dsNewRow.Item("Log_Date") = txtLogDate.Text
            dsNewRow.Item("Shift_Hours") = txtShiftHours.Text
            dsNewRow.Item("Operator_Comments") = txtOperComments.Text

            ds.Tables("ShiftLog").Rows.Add(dsNewRow)
            da.Update(ds, "ShiftLog")

            MsgBox("New Record added to the Database")

            btnCommit.Enabled = False
            btnAdd.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True
        End If

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        If MessageBox.Show("Do you really want to Delete this Record?", _
            "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then

            MsgBox("Operation Cancelled")
            Exit Sub

        End If

        ds.Tables("ShiftLog").Rows(inc).Delete()

        MaxRows = MaxRows - 1
        inc = 0
        NavigateRecords()
        da.Update(ds, "ShiftLog")
        MsgBox("Record deleted from the Database")

    End Sub

    Private Sub NavigateRecords()

        txtShift_Key.Text = ds.Tables("ShiftLog").Rows(inc).Item("Shift_Key")
        txtOperator.Text = ds.Tables("ShiftLog").Rows(inc).Item("Operator")
        txtLogDate.Text = ds.Tables("ShiftLog").Rows(inc).Item("Log_Date")
        txtShiftHours.Text = ds.Tables("ShiftLog").Rows(inc).Item("Shift_Hours")
        If Not IsDBNull(ds.Tables("ShiftLog").Rows(inc).Item("Operator_Comments")) Then
            txtOperComments.Text = ds.Tables("ShiftLog").Rows(inc).Item("Operator_Comments")
        Else
            txtOperComments.Text = " "
        End If

    End Sub

End Class

[jmcilhinney]

Please wrap your code snippets in formatting tags so that it can be read comfortably. Unfortunately the buttons have not yet been restored to the simple editor since the site upgrade but the advanced editor provides buttons for Code and VBCode tags, or you can simply type them yourself. You can see what they look like by quoting someone else's post who has used them.

[jmcilhinney]

Without having tried to read your code, you need to use both complex and simple data-binding in this case, e.g.

'Use complex data-binding to display a list of personnel.
With operatorComboBox
    .DisplayMember = "Name" 'Display the names of all the personnel in the drop-down list.
    .ValueMember = "ID" 'Expose the ID of the selected person via the SelectedValue.
    .DataSource = personnelTable
End With
 
'Use simple data-binding to propagate the selection in the drop-down to a child record.
operatorComboBox.DataBindings.Add("SelectedValue", shiftTable, "OperatorID")

Now, when the user selects a Name from the Personnel table in the drop-down list, the ID of that record is exposed via the SelectedValue property. Thanks to the last line, that ID is then propagated to the OperatorID of the current record from the Shift table. That works the other way to, i.e. as you navigate through the Shift records, the OperatorID value of each record is assigned to the SelectedValue of the ComboBox and the corresponding Name is displayed.

[wjburke2]

Thank you for your responce. I have spent a better part of the morning researching Simple binding and it appears to be exactly what I am looking for. I added your code so now my code after the Conn.(Close) looks like this. I also changed the field ShiftLog.Operator to OperatorID as Operator is a reserverd word.

Conn.Close()

With txtOperator
.DisplayMember = "Name"
.ValueMember = "Initials"
.DataSource = dsOper
End With
txtOperator.DataBindings.Add("SelectedValue", ds, "OperatorID")

inc = 0
NavigateRecords()

Now I am getting a differant error on the databinding line.
"Cannot bind to the property or column OperatorID on the DataSource. Parameter name: dataMember"

[jmcilhinney]

What is 'dsOper' and what is 'ds'? Are they DataSets or DataTables or one of each?

On a separate note, I abhor Hungarian Notation but, if you're going to use it then at least use it properly. You have a ComboBox named 'txtOperator'. If I didn;t know better I'd think that you were trying to make your code confusing.

[wjburke2]

Sorry for the confusion. Keep in mind I am new and most of this is code I wrote. This program is a time keeping program. Operators are employees. A Operator can run 1 to 4 machines during their shift. I need to keep track of how long they worked and how many pieces they produced.
“txtOperator” started as a text box and now I am trying to change it to a combo box. I probably should change the name to cmbOperator.
"dsOper" is a dsataset that contains the name and initial of employees. It used by the combo box to allow the user to select an operator.
"ds" is a data set of shift logs. “Shift_log table” contains records on when an employee starts and ends their shift.
Shift_Printers is another table I have not gotten to yet. It holds records for the machines they worked on and begin / end piece count.

I have actually created a program in Access that does this. I am trying to migrate it to VB. (see attached screen shot)

[jmcilhinney]

First up, you should not have two separate DataSets unless they actually represent different data sources.

As for the binding, you need to either specify the DataTable as the data source or, if you use the DataSet, you have to qualify the column name with the name of the table it's in. If you just provide a DataSet and a column name then the binding would have to guess what table it's in.

[wjburke2]

jmcilhinney, I wanted to thank you for your advise. I was confused as to the roll of the datasets, datatables, and adapters. After some research I was able to rewrite my application so it is easier to read and acually works. Thank you for pointing me in the right direction.