none
Update Access DB from DataGridView with Two Data Tables RRS feed

  • Question

  • I have 2 tables in the same database. 

    tblAircraft - ACID, ACName

    tblMaster - MasterID, ACID, and many others that are not relevant to my question.

    tblAircraft is a lookup table with all the aircraft listed. tblMaster is the main table and holds all the detail information.

    I have a DataGridView that has several columns. Column 1 is a combobox column that has the ACName from tblAircraft. The rest of the columns are textbox columns with data from tblMaster. My application works as far as displaying all the data and I can use a dataadapter update command to update tblMaster (good) and the other datasource updates tblAircraft (bad) with the DataGridView. My problem is that I want ACID in tblMaster to be updated with the ACID from the combobox. Of course it won't because it belongs to the datasource for tblAircraft. What am I missing?

    I have spent hours and hours trying to figure this out. I hope my explanation is clear. Thank you in advance for any help you can provide.

        Private Sub cboAC_SelectionChangeCommitted(sender As Object, e As EventArgs) Handles cboAC.SelectionChangeCommitted
    
            dsACGrid = LoadDataSet()
    
            'Refreshes DataGridView
            If dgvTasks.ColumnCount > 0 Then
                For i As Integer = 0 To dgvTasks.ColumnCount - 1
                    dgvTasks.Columns.RemoveAt(0)
                Next
            End If
    
            'Connection obj to database
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SupportGeneral.accdb"
    
            Dim cbColumn As New DataGridViewComboBoxColumn With
                {
                    .DataPropertyName = "ACName",
                    .DataSource = dsACGrid.Tables(1),
                    .DisplayMember = "ACName",
                    .DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing,
                    .Name = "cbColumn",
                    .HeaderText = "Aircraft",
                    .SortMode = DataGridViewColumnSortMode.NotSortable,
                    .ValueMember = "ACName"
                }
    
            dgvTasks.Columns.Insert(0, cbColumn)
    
            Dim GSTask As New DataGridViewTextBoxColumn With {.DataPropertyName = "GSTask", .HeaderText = "Gen Spt Task"}
    
            Dim LCOMTask As New DataGridViewTextBoxColumn With {.DataPropertyName = "LCOMTask", .HeaderText = "LCOM Task"}
    
            Dim AFSC As New DataGridViewTextBoxColumn With {.DataPropertyName = "AFSC", .HeaderText = "AFSC"}
    
            Dim ReqSkill As New DataGridViewTextBoxColumn With {.DataPropertyName = "ReqSkill", .HeaderText = "Req Skill"}
    
            Dim ReqGrade As New DataGridViewTextBoxColumn With {.DataPropertyName = "ReqGrade", .HeaderText = "Req Grade"}
    
            Dim NotesQuestions As New DataGridViewTextBoxColumn With {.DataPropertyName = "NotesQuestions", .HeaderText = "Notes/Questions"}
    
            Dim AvgTimeHours As New DataGridViewTextBoxColumn With {.DataPropertyName = "AvgTimeHours", .HeaderText = "Avg Time-Hours"}
    
            Dim CrewSizeMin As New DataGridViewTextBoxColumn With {.DataPropertyName = "CrewSizeMin", .HeaderText = "Crew Size Min"}
    
            Dim CrewSizeMax As New DataGridViewTextBoxColumn With {.DataPropertyName = "CrewSizeMax", .HeaderText = "Crew Size Max"}
    
            Dim Manhours As New DataGridViewTextBoxColumn With {.DataPropertyName = "Manhours", .HeaderText = "Manhours"}
    
            Dim FreqQty As New DataGridViewTextBoxColumn With {.DataPropertyName = "FreqQty", .HeaderText = "Freq Qty"}
    
            Dim FreqRate As New DataGridViewTextBoxColumn With {.DataPropertyName = "FreqRate", .HeaderText = "Freq Rate"}
    
            Dim PAFSC As New DataGridViewTextBoxColumn With {.DataPropertyName = "PAFSC", .HeaderText = "PAFSC"}
    
            Dim PAFSCQty As New DataGridViewTextBoxColumn With {.DataPropertyName = "PAFSCQty", .HeaderText = "PAFSC Qty"}
    
            Dim AltAFSC1 As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC1", .HeaderText = "Alt AFSC1"}
    
            Dim AltAFSC1Qty As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC1Qty", .HeaderText = "Alt AFSC1 Qty"}
    
            Dim AltAFSC2 As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC2", .HeaderText = "Alt AFSC2"}
    
            Dim AltAFSC2Qty As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC2Qty", .HeaderText = "Alt AFSC2 Qty"}
    
            Dim AltAFSC3 As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC3", .HeaderText = "Alt AFSC3"}
    
            Dim AltAFSC3Qty As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC3Qty", .HeaderText = "Alt AFSC3 Qty"}
    
            Dim AltAFSC4 As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC4", .HeaderText = "Alt AFSC4"}
    
            Dim AltAFSC4Qty As New DataGridViewTextBoxColumn With {.DataPropertyName = "AltAFSC4Qty", .HeaderText = "Alt AFSC4 Qty"}
    
            Dim ACSelected As New DataGridViewCheckBoxColumn With {.DataPropertyName = "ACSelected", .HeaderText = "Selected"}
    
            With dgvTasks
                .AutoGenerateColumns = False
                .Columns.AddRange(New DataGridViewColumn() {GSTask, LCOMTask, AFSC, ReqSkill, ReqGrade, NotesQuestions,
                                                           AvgTimeHours, CrewSizeMin, CrewSizeMax, Manhours, FreqQty, FreqRate,
                                                           PAFSC, PAFSCQty, AltAFSC1, AltAFSC1Qty, AltAFSC2, AltAFSC2Qty,
                                                           AltAFSC3, AltAFSC3Qty, AltAFSC4, AltAFSC4Qty, ACSelected})
            End With
    
            'Bind the dataset after all operation to the datagrid
            dgvTasks.DataSource = dsACGrid.Tables(0)
    
    
        End Sub
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SupportGeneral.accdb"
    
            'Loads dropdown for aircraft type
            Dim strSQL As String = "Select * from tblAircraft"
    
            Dim daAC As OleDbDataAdapter = New OleDbDataAdapter(strSQL, Conn)
    
            daAC.Fill(dsAC, "tblAircaft")
    
            Dim dr As DataRow = dsAC.Tables(0).NewRow()
            dr("ACName") = ""
            dsAC.Tables(0).Rows.InsertAt(dr, 0)
    
            Using cmd As New OleDbCommand(strSQL, Conn)
                With cboAC
                    .DataSource = dsAC.Tables(0)
                    .DisplayMember = "ACName"
                    .ValueMember = "ACName"
                End With
            End Using
    
            dsAC.Tables.RemoveAt(0)
    
        End Sub
    
        Private Function LoadDataSet() As DataSet
    
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SupportGeneral.accdb"
    
            'This code refreshes the datasets and data tables.
            If dtACGrid.Rows.Count > 0 Then
                dsACGrid.Tables.RemoveAt(0)
                dtACGrid.Clear()
            End If
    
            If dtAircraft.Rows.Count > 0 Then
                dsACGrid.Tables.RemoveAt(0)
                dtAircraft.Clear()
            End If
    
            dsACGrid.Tables.Add(dtACGrid)
    
            'Load Master table
            strACGrid = "select * from tblMaster where ACName = '" & cboAC.SelectedValue & "'"
    
            daACGrid = New OleDbDataAdapter(strACGrid, Conn)
            cbACGrid = New OleDbCommandBuilder(daACGrid)
    
            cbACGrid.QuotePrefix = "["
            cbACGrid.QuoteSuffix = "]"
    
            daACGrid.Fill(dtACGrid)
    
            dsACGrid.Tables.Add(dtAircraft)
    
            'Load Aircraft table
            strACGrid = "select * from tblAircraft"
    
            daACGrid = New OleDbDataAdapter(strACGrid, Conn)
            cbACGrid = New OleDbCommandBuilder(daACGrid)
    
            cbACGrid.QuotePrefix = "["
            cbACGrid.QuoteSuffix = "]"
    
            daACGrid.Fill(dtAircraft)
    
            Return dsACGrid
    
        End Function
    
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    
            daACGrid.Update(dtACGrid)
            Me.Close()
    
        End Sub
    
    
    End Class


    Wednesday, October 4, 2017 9:17 PM

All replies

  • Hi BillSut,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about VB development. Since our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum to seek for a more professional support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, October 5, 2017 3:20 AM

  • Hi BillSut,

    According to your further description, I suggest you can upload a running demo to OneDrive(Including your test material). We can download it and debugging. This will help us quickly analyze your problem.
    Share OneDrive files and folders:
    https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

    Best Regards,

    Yohann Lu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 5, 2017 4:55 AM
    Moderator
  • >>My problem is that I want ACID in tblMaster to be updated with the ACID from the combobox. Of course it won't because it belongs to the datasource for tblAircraft. What am I missing?

    Hi BillSut,

    I am not sure what you mean by this sentence above? Can you describe more detailed info about this? It is helpful to us to know what is your problem。

    best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, October 8, 2017 4:09 AM
    Moderator