使用两个数据表从datagridview更新访问数据库 [英] Update access DB from datagridview with two data tables

问题描述

我在同一个数据库中有2个表。



tblAircraft - ACID，ACName



tblMaster - MasterID，ACID以及其他许多不相关的我的问题。



tblAircraft是一张列出所有飞机的查询表。 tblMaster是主表并保存所有详细信息。



我有一个DataGridView，它有几列。第1列是一个组合框列，其中包含来自tblAircraft的ACName。其余列是文本框列，其中包含来自tblMaster的数据。我的应用程序可以显示所有数据，我可以使用dataadapter update命令更新tblMaster（好），其他数据源使用DataGridView更新tblAircraft（坏）。我的问题是我希望使用组合框中的ACID更新tblMaster中的ACID。当然它不会，因为它属于tblAircraft的数据源。我错过了什么？



我花了几个小时试图解决这个问题。我希望我的解释清楚。提前感谢您提供的任何帮助。

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

我尝试过：



我尝试过使用绑定和未绑定的列。这是我尝试过的所有调整的日子。

What I have tried:

I've tried using bound and unbound columns. It's been days with all the tweaks I've tried.

推荐答案

我在另一个论坛上得到了一些帮助，最后得到了解决。这是最终代码，如果它可以帮助其他人。

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

Tak表格tblmaster对

Take the refence of the Table tblmaster to

tblAircraft

的反应和组合框选择索引更改使用ID更新数据库。

and on combobox selectionindex change update the db.. using ID.

我同意CHill60 。请使用完整的句子和单桅帆船，我指的是我的代码的哪一行，以及你具体建议的变化。谢谢你的帮助。



我也想在这里发布我的DataGridView图片以便澄清，但我没有办法做到这一点。谁知道怎么做？

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?

这篇关于使用两个数据表从datagridview更新访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！