VS 2015 Update Access DB from DataGridView with Two Data Tables-VBForums
Results 1 to 13 of 13

Thread: Update Access DB from DataGridView with Two Data Tables

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Update Access DB from DataGridView with Two Data Tables

    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.

    Code:
        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
    Last edited by si_the_geek; Oct 4th, 2017 at 03:18 AM. Reason: added Code tags

  2. #2

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Re: Update Access DB from DataGridView with Two Data Tables

    I apologize for the formatting of my code. I tried to find a way to edit it but I don't see the edit button.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,542

    Re: Update Access DB from DataGridView with Two Data Tables

    You probably can't edit a post until you've made a minimum number, which may be 10. Use the Reply With Quote button to get a copy of your original post and then edit that.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: Update Access DB from DataGridView with Two Data Tables

    You should probably just set Datagridview combobox column properly. Assuming that your ACID is actually your tblAircraft record ID you should set Datagridview combobox column displayMember as "ACName" and ValueMember to "ACID". Then you will store this value in Datagridview column. And If this column is bound to your table tblMaster then you should update wihout problem. You can bound field to whatever table column also in design mode of Datagridview.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,542

    Re: Update Access DB from DataGridView with Two Data Tables

    Quote Originally Posted by LuckyLuke82 View Post
    You should probably just set Datagridview combobox column properly. Assuming that your ACID is actually your tblAircraft record ID you should set Datagridview combobox column displayMember as "ACName" and ValueMember to "ACID". Then you will store this value in Datagridview column. And If this column is bound to your table tblMaster then you should update wihout problem. You can bound field to whatever table column also in design mode of Datagridview.
    For information on how to do that, follow the CodeBank link in my signature below and check out my thread on a ComboBox Column In A DataGridView.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Re: Update Access DB from DataGridView with Two Data Tables

    I may not have been articulate in asking my question so I'll try using a picture.

    Name:  SupGen.jpg
Views: 28
Size:  35.7 KB

    This is my DataGridView. The "Aircraft" column is a lookup of a list of aircraft in datatable dtAircraft from table tblAircraft. The rest of the columns belong to the datatable dtACGrid from table tblMaster. When I click the Save button (dataadapter.update), the first column updates tblAircraft while the rest update tblMaster. What I want to happen is for the value in column one (and the rest) to update the ACID and ACName columns of tblMaster.

    I know WHY it is doing what it's doing, I just don't know how to make it do what I want.

    Does this make more sense?

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,542

    Re: Update Access DB from DataGridView with Two Data Tables

    Firstly, your tblMaster should not have both ACID and ACName. It should just contain a foreign key, i.e. a column that corresponds to the primary key of the related table. That means ACID only. For one thing, that means that, should the name of an aircraft change, you don't then have numerous incorrect values in other tables.

    As for the issue, if you configure things the way I have in my CodeBank thread then it will work as you want. The grid will be bound to tblMaster and any changes in the combo box column will change the ACID in tblMaster. You then just save changes from your tblMaster DataTable back to tblMaster in the database and you're done. If you don't want to save any changes to any other tables then don't.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: Update Access DB from DataGridView with Two Data Tables

    This is my DataGridView. The "Aircraft" column is a lookup of a list of aircraft in datatable dtAircraft from table tblAircraft. The rest of the columns belong to the datatable dtACGrid from table tblMaster. When I click the Save button (dataadapter.update), the first column updates tblAircraft while the rest update tblMaster. What I want to happen is for the value in column one (and the rest) to update the ACID and ACName columns of tblMaster.

    I know WHY it is doing what it's doing, I just don't know how to make it do what I want.

    Does this make more sense?
    As JM said - in other words - change your SQL query for Datagridview column to "Select ACID from...", to show only ACID. Then go to design of Datagridview, click on Edit columns>> click Datagridview combobox column and bound this column to "ACID" or whatever your table column name is for "ACID" in Tblmaster, in DataPropertyName property. Or do It all in code....And If I were you I would remove Selection_change commited code, you should bind your combobox only once, like in Form_Load. If you need to refresh datagridview during runtime, you just refresh datatable of Tblmaster, combobox column will allready have It's data and will change It's display value when you'll change underlying Tblmaster value in that column.

  9. #9

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Re: Update Access DB from DataGridView with Two Data Tables

    I think we have a communication problem and I'm sure it's on my side. I can't bind the combobox column to tblMaster because then the list will only show aircraft that have detail records. It needs to show ALL aircraft available, even if there are no detail records. It must be bound to tblAircraft (lookup) so that the user can add a new record for an aircraft that doesn't already exist in tblMaster. For example, if there are records in tblMaster for CV22 and MC130 then the combobox will only show those. If the user wants to add a new record in the datagridview for an F16, it needs to show F16 in that combobox list. If I bind it to tblMaster then it won't show F16 in the list.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,348

    Re: Update Access DB from DataGridView with Two Data Tables

    Yes you do need to have the combobbox column bound to tblMaster, then you use tblAircraft as the datasource for the combobox list.

  11. #11

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Re: Update Access DB from DataGridView with Two Data Tables

    OK, the combobox is working now but the dataadapter update is not working. The error I get is "Missing the DataColumn 'ACName' in the DataTable for the SourceColumn 'ACName'. Here's my updated code.

    Code:
        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 = "ACID",
                    .DataSource = dsACGrid.Tables(1),
                    .DisplayMember = "ACName",
                    .DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing,
                    .Name = "cbColumn",
                    .HeaderText = "Aircraft",
                    .SortMode = DataGridViewColumnSortMode.NotSortable,
                    .ValueMember = "ACID"
                }
    
            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 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 to fill DataGridView
            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 = "ACID"
                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 & "'"
            strACGrid = "select * from tblMaster where ACID = " & 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
    Last edited by Bill1961; Oct 5th, 2017 at 10:09 AM.

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,348

    Re: Update Access DB from DataGridView with Two Data Tables

    From what I can make of your code, you use daACGrid to fill both dtACGrid and dtAircraft, then you call the update method using the dtACGrid datatable. But you last used daACGrid to fill dtAircraft, so that won't work.

    I don't see where Conn, dsACGrid, daACGrid or the two datatables are created so I'm guessing their in a module and are Public/Global objects.

    Anyway you really should use two dataadapters, one for dtACGrid and one for dtAircraft.

  13. #13

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    6

    Re: Update Access DB from DataGridView with Two Data Tables

    Thank you wes4dbt! I changed a few lines to create a second dataadapter and the update is working now. Thanks to all for your comments! Here is my final code for anyone who is interested.

    Code:
    Public Class Form1
    
        Dim strACGrid As String = ""
        Dim strSQL As String = ""
        Dim dsACGrid As New DataSet
        Dim dsAircraft As New DataSet
        Dim dsAC As New DataSet
        Dim dtACGrid As New DataTable
        Dim dtAircraft As New DataTable
        Dim daACGrid As OleDbDataAdapter
        Dim daAircraft As OleDbDataAdapter
        Dim cbACGrid As OleDbCommandBuilder
        Dim Conn As New OleDbConnection
        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 = "ACID",
                    .DataSource = dsACGrid.Tables(1),
                    .DisplayMember = "ACName",
                    .DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing,
                    .Name = "cbColumn",
                    .HeaderText = "Aircraft",
                    .SortMode = DataGridViewColumnSortMode.NotSortable,
                    .ValueMember = "ACID"
                }
    
            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 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 to fill DataGridView
            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 = "ACID"
                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 ACID = " & 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"
    
            daAircraft = New OleDbDataAdapter(strACGrid, Conn)
            cbACGrid = New OleDbCommandBuilder(daACGrid)
    
            cbACGrid.QuotePrefix = "["
            cbACGrid.QuoteSuffix = "]"
    
            daAircraft.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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.