Foreign key updating as -1

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
I am having a problem and I'm pretty sure it is me missing something somewhere.

I have set up a project in VS2008 and for most of the application I am using the Windows controls and their default behaviour. However, I have started programming in some functionality for the combo boxes that link to other tables. I set this up using the VS ComboBox Tasks.

What I am trying to do is let people type a client name into a client combobox. If that entry is not in the combobox, then a message box will ask the user whether this should be saved to the database. This uses the Comboboxes Leave event. However, when I save the record, close the program and open it up again, I get an error message saying System.ArgumentException: DataGridViewComboBoxCell value is not valid.

On further examination of the table with the foreign key, I notice that this has been stored as a -1. The code for this functionality is as follows:

VB.NET:
Private Sub ClientComboBox_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClientComboBox.Leave

        Dim con As New OleDb.OleDbConnection
        Dim dsClients As New DataSet
        Dim cmdClients As New OleDb.OleDbDataAdapter
        Dim strQryClients As String = "SELECT ID, ClientName FROM Clients ORDER BY ClientName ASC"
        Dim strDataSource As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
                                        "Data Source=C:\Documents and Settings\bowringj\Desktop\Bid Status.accdb"
        Dim strClientSelection As String
        Dim intClientRecordCounter As Integer
        Dim boolClientFound As Boolean
        'Dim cbClients As New OleDb.OleDbCommandBuilder(cmdClients)
        Dim dsNewRow As DataRow

        strClientSelection = ClientComboBox.Text
        boolClientFound = False

        con.ConnectionString = strDataSource

        Try

            con.Open()

        Catch ex As Exception

            MsgBox("An error occurred while trying to connect to the database." & vbCrLf & _
                   "You will not be able to use the update functionality from the Client Combo Box.", _
                    MsgBoxStyle.Critical, "Database Connection Error")

        End Try

        Try

            cmdClients = New OleDb.OleDbDataAdapter(strQryClients, con)
            cmdClients.Fill(ClientsDataSet, "Clients")
            Dim cbClients As New OleDb.OleDbCommandBuilder(cmdClients)

        Catch ex As Exception

            MsgBox("An error occurred while trying to retrieve data from the database." & vbCrLf & _
                   "You will not be able to use the update functionality from the Client Combo Box.", _
                    MsgBoxStyle.Critical, "Data Retrieval Error")

        Finally

            con.Close()

        End Try

        For intClientRecordCounter = 0 To ClientsDataSet.Tables("Clients").Rows.Count - 1

            If ClientsDataSet.Tables("Clients").Rows(intClientRecordCounter).Item(1) = strClientSelection Then

                boolClientFound = True
                Exit For

            End If

        Next intClientRecordCounter

        If Not boolClientFound Then

            If MessageBox.Show("The client entered does not appear in the list." & vbCrLf & _
                                "Do you want to add this client?", _
                                "Add Client", _
                                MessageBoxButtons.YesNo, _
                                MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                Try

                    dsNewRow = ClientsDataSet.Tables("Clients").NewRow()
                    dsNewRow.Item("ClientName") = strClientSelection

                    ClientsDataSet.Tables("Clients").Rows.Add(dsNewRow)
                    cmdClients.Update(ClientsDataSet, "Clients")

                    ClientsBindingSource.ResetBindings(False)

                    ClientComboBox.Refresh()


                    MsgBox("The Client has been added to the database.", MsgBoxStyle.Information, "Client Added")

                Catch ex As Exception

                    MsgBox("There was a problem adding the new client to the database.", MsgBoxStyle.Exclamation, "Client Adding Error")

                End Try

            End If

        End If

    End Sub
If anyone could get me over this stumbling block, then I would be eternally grateful!!! :D
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
At what point do you calculate the new ID for the client? I'd expect it to be in the INSERT sql or close to it in the db, not the client side code
 

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
The ID is an Access auto increment field. Working through this I have just assumed that both the ClientName, which is string and the ID field would update to the combo box.

I am assuming that as far as that Clients table is concerned, this gets updated with:

ClientsDataSet.Tables("Clients").Rows.Add(dsNewRow)
cmdClients.Update(ClientsDataSet, "Clients")

So I was hoping that when I did a refresh like:

ClientsBindingSource.ResetBindings(False)
ClientComboBox.Refresh()

This would update the combobox with the new ID and ClientName. However, it appears that although the client table has been updated sucessfully, the combobox hasn't and only get the displaymember of <newstring>. However, the valuemember is -1.

So I am just trying to guess where abouts I would pull the correct data from the clients table rather than just assigning to the displaymember.

Thank you for you quick response cjard.
 

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
Please help me. All I want to do is enter text into a combo box and if it is not in the list then update the database. I have been able to do this, but the ID returned is always -1 and I can not work out how to retrieve the correct id number.

Please help. I am working on a project at work and I have to demo the application tomorrow and I am already a week behind because of this problem.
 

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
Found the Answer

For anyone who comes across this problem I found an answer. It may not be the best way of achieving the desired results, but it worked for me. The code is as follows:

VB.NET:
Private Sub ClientComboBox_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClientComboBox.Leave

        Dim con As New OleDb.OleDbConnection
        Dim dsClients As New DataSet
        Dim cmdClients As New OleDb.OleDbDataAdapter

        Dim strQryClients As String = "SELECT ID, ClientName FROM Clients ORDER BY ClientName ASC"
        
        Dim strClientSelection As String
        Dim intClientRecordCounter As Integer
        Dim boolClientFound As Boolean
        Dim dsNewRow As DataRow

        strClientSelection = ClientComboBox.Text
        boolClientFound = False

        con.ConnectionString = strDataSource

        Try

            con.Open()

        Catch ex As Exception

            MsgBox("An error occurred while trying to connect to the database." & vbCrLf & _
                   "You will not be able to use the update functionality from the Client Combo Box.", _
                    MsgBoxStyle.Critical, "Database Connection Error")

        End Try

        Try

            cmdClients = New OleDb.OleDbDataAdapter(strQryClients, con)
            cmdClients.Fill(ClientsDataSet, "Clients")
            Dim cbClients As New OleDb.OleDbCommandBuilder(cmdClients)

        Catch ex As Exception

            MsgBox("An error occurred while trying to retrieve data from the database." & vbCrLf & _
                   "You will not be able to use the update functionality from the Client Combo Box.", _
                    MsgBoxStyle.Critical, "Data Retrieval Error")

        Finally

            con.Close()

        End Try

        For intClientRecordCounter = 0 To ClientsDataSet.Tables("Clients").Rows.Count - 1

            If ClientsDataSet.Tables("Clients").Rows(intClientRecordCounter).Item(1) = strClientSelection Then

                boolClientFound = True
                Exit For

            End If

        Next intClientRecordCounter

        If Not boolClientFound Then

            If MessageBox.Show("The client entered does not appear in the list." & vbCrLf & _
                                "Do you want to add this client?", _
                                "Add Client", _
                                MessageBoxButtons.YesNo, _
                                MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                Try

                    dsNewRow = ClientsDataSet.Tables("Clients").NewRow()
                    dsNewRow.Item("ClientName") = strClientSelection

                    ClientsDataSet.Tables("Clients").Rows.Add(dsNewRow)

                    cmdClients.Update(ClientsDataSet, "Clients")
                    
                    ClientsDataSet.Tables(0).Rows.Clear()
                    cmdClients.Fill(ClientsDataSet, "Clients")

                    For intClientRecordCounter = 0 To ClientsDataSet.Tables(0).Rows.Count - 1

                        If ClientsDataSet.Tables(0).Rows(intClientRecordCounter).Item(1) = strClientSelection Then

                            ClientComboBox.SelectedValue = ClientsDataSet.Tables(0).Rows(intClientRecordCounter).Item(0)
                            Exit For

                        End If

                    Next intClientRecordCounter

                    MsgBox("The Client has been added to the database.", MsgBoxStyle.Information, "Client Added")

                Catch ex As Exception

                    MsgBox("There was a problem adding the new client to the database.", MsgBoxStyle.Exclamation, "Client Adding Error")

                Finally

                    cmdClients.Dispose()
                    cmdClients = Nothing

                End Try

            End If

        End If

    End Sub
 
Top Bottom