Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.


VB
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


What I have tried:

I've tried using bound and unbound columns. It's been days with all the tweaks I've tried.
Posted
Updated 5-Oct-17 9:28am
v2
Comments
Richard Deeming 5-Oct-17 14:22pm    

I received some help on another forum and finally got it resolved. Here is the final code if it can help someone else.

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
 
Share this answer
 
Take the refence of the Table tblmaster to
tblAircraft
and on combobox selectionindex change update the db.. using ID.
 
Share this answer
 
Comments
CHill60 5-Oct-17 3:24am    
I've been around a while and I can't understand your answer so I suspect the OP won't either. Use the Improve Solution link and make this more understandable
I agree with CHill60. Please use full sentences and dhow which lines of my code you are referring to and what changes you are specifically suggesting. Thank you for the help.

I also wanted to post a picture of my DataGridView on here for clarification but I don't see a way to do that. Anyone know how?
 
Share this answer
 
Comments
Richard Deeming 5-Oct-17 14:21pm    
If you want to comment on a solution, then click the "Have a Question or Comment?" button under that solution.

DO NOT post your comment as a new solution!
I was able to get the combobox to work but now the dataadapter update doesn't work. I get the error "Missing the DataColumn 'ACName' in the DataTable 'Table1' for the SourceColumn 'ACName'. Here is my updated 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 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
 
Share this answer
 
v2
Comments
Richard Deeming 5-Oct-17 14:24pm    
Is this a solution to your question? The text at the start makes it seem like it should be an update to your question instead.

But you've accepted it as the solution to your problem, so your question is now "solved".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900