vb.net - OleDbAdapter错误，在封闭 block 中隐藏变量错误

Question

关于某些OleDb命令，我遇到了一些问题/错误。它们显示在下图中:

我的项目目标是根据选中的复选框将信息(在本例中为文本)导入到Access数据库(名为Database1)中。从那里开始，将基于几个不同的因素对数据进行计数，然后由协调的Visual Studio项目使用该数据以图形形式显示数据。
我只希望添加记录，而不是删除记录。
我感觉到我缺少一些很小的东西；可能只是使用了错误的值类型或插入了错误的变量。对于代码非常业余，我深表歉意。毕竟，我是编程/编码 Realm 的新手。我只是不确定如何开始修复这些错误。
如果需要，这是整个代码部分，供您引用:

Private Sub InputInformation(sender As System.Object, e As System.EventArgs) Handles ImporttBUT.Click

    Dim con As New OleDb.OleDbConnection(My.Settings.Database1ConnectionString)
    con.Open()
    MsgBox("OPEN")
    Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0",
                                                        .DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb",
                                                         .PersistSecurityInfo = False}
    Dim cmdSQL As SqlCommand = New SqlCommand
    Using cmdSQL As New OleDbCommand{"SELECT * from [DataCollection] WHERE ID = 0",
                                     New OleDbConnection(My.Settings.Database1ConnectionString)}
                                     End Using


    Dim dt As New DataTable

    Dim usertables As DataTable = Nothing
    Dim da As OleDb.OleDbDataAdapter(con)
    Dim cb As OleDbCommandBuilder
    cb = New OleDbCommandBuilder(da)
    da.Fill(dt)

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Database1.accdb; Persist Security Info=False;"
    Dim myrow As DataRow = dt.Rows.Add

    With dt.Rows.Add
        .Item("M/Y Of LOG") = Me.MonthList2021.SelectedItem
        .Item("TIME OF LOG") = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")
        .Item("USER") = UserName
        '' ADD STK ITEM HERE ONCE AUTOFILL IS COMPLETE
        If MissedPartCHKB.Checked = True Then
            .Item("MISSED PART") = MissedPartCHKB.Text
        ElseIf MissedPartCHKB.Checked = False Then
            .Item("MISSED PART") = "NEATOL"
        End If
        If NotInEpicorCHKB.Checked = True Then
            .Item("NOT IN EPICOR") = NotInEpicorCHKB.Text
        ElseIf NotInEpicorCHKB.Checked = False Then
            .Item("NOT IN EPICOR") = "NEATOL"
        End If
        If MissedBuyoutCHKB.Checked = True Then
            .Item("MISSED BUYOUT") = MissedBuyoutCHKB.Text
        ElseIf MissedBuyoutCHKB.Checked = False Then
            .Item("MISSED BUYOUT") = "NEATOL"
        End If
        If NonStockCHKB.Checked = True Then
            .Item("MISSED NON STOCK ITEM") = NonStockCHKB.Text
        ElseIf NonStockCHKB.Checked = False Then
            .Item("MISSED NON STOCK ITEM") = "NEATOL"
        End If
        If MissedSTKItemCHKB.Checked = True Then
            .Item("MISSED STOCK ITEM") = MissedSTKItemCHKB.Text
        ElseIf MissedSTKItemCHKB.Checked = False Then
            .Item("MISSED STOCK ITEM") = "NEATOL"
        End If
        If MissedAutomatedPartCHKB.Checked = True Then
            .Item("MISSED AUTOMATED") = MissedAutomatedPartCHKB.Text
        ElseIf MissedAutomatedPartCHKB.Checked = False Then
            .Item("MISSED AUTOMATED") = "NEATOL"
        End If
        If MissingPrintAfterQTYCHKB.Checked = True Then
            .Item("MISSING PRINTS AFTER QUANTITY") = MissingPrintAfterQTYCHKB.Text
        ElseIf MissingPrintAfterQTYCHKB.Checked = False Then
            .Item("MISSING PRINTS AFTER QUANTITY") = "NEATOL"
        End If
        If MissedPrintsNOTSentChadCHKB.Checked = True Then
            .Item("MISSED PRINT NOT SENT TO CHAD") = MissedPrintsNOTSentChadCHKB.Text
        ElseIf MissedPrintsNOTSentChadCHKB.Checked = False Then
            .Item("MISSED PRINT NOT SENT TO CHAD") = "NEATOL"
        End If
        If OtherCHKB.Checked = True Then
            .Item("OTHER") = OtherTXTB.Text
        ElseIf OtherCHKB.Checked = False Then
            .Item("OTHER") = "NEATOL"
        End If
        If AddedMissingDimCHKB.Checked = True Then
            .Item("ADDED MISSING DIMENSION") = AddedMissingDimCHKB.Text
        ElseIf AddedMissingDimCHKB.Checked = False Then
            .Item("ADDED MISSING DIMENSION") = "NEATOL"
        End If
        If FixedDimensionCHKB.Checked = True Then
            .Item("FIXED DIMENSION") = FixedDimensionCHKB.Text
        ElseIf FixedDimensionCHKB.Checked = False Then
            .Item("FIXED DIMENSION") = "NEATOL"
        End If
    End With

    da.update(dt)

    con.Close()
    MsgBox("CLOSED")

    MissedPartCHKB.Checked = False

    MissedAutomatedPartCHKB.Checked = False

    NotInEpicorCHKB.Checked = False

    NonStockCHKB.Checked = False

    MissedSTKItemCHKB.Checked = False

    MissedBuyoutCHKB.Checked = False

    MissedPrintsNOTSentChadCHKB.Checked = False

    MissingPrintAfterQTYCHKB.Checked = False

    AddedMissingDimCHKB.Checked = False

    FixedDimensionCHKB.Checked = False

    OtherCHKB.Checked = False

    OtherTXTB.Text = ""
    ' eventually change the month list to automatically select based on the current date
    MonthList2021.SelectedItem = False
End Sub

这是我的访问数据库的预览:

Answer 1

Using块中最重要的数据库对象是连接。
如果要在If语句中求值的表达式返回Boolean，例如复选框的Checked属性，则不需要= True。由于此属性的值只能是True或False，因此您无需使用ElseIf对其进行重新检查。可以使用Else。
我当然希望Time Log字段是Date。
在插入语句中，带空格和/或保留字的字段名称必须放在方括号[]中。 Access会忽略参数名称。我们使用它们使代码可读。对于Access，参数在sql语句中出现的顺序必须与它们添加到参数集合的顺序匹配。
无需检索在Select查询中不使用的数据并两次访问数据库，一次使用.Fill和一次使用.Update，我们将直接插入新记录。如果确实有机会使用DataAdapter，则如果发现连接已关闭，它将为您连接Open和Close。但是，如果找到Open，它将保留为Open。
请注意，直到紧接.Execute...之前，连接才被打开，并且被End Using与命令一起关闭并释放。
我将控件的重置移动到单独的Sub。尝试让您的方法只做一件事。尽管.SelectedItem可以接受任何Object，但False不会满足您的期望。

Private Sub InputInformation(sender As System.Object, e As System.EventArgs) Handles ImporttBUT.Click
    Dim strSql = "Insert Into [Data Collection] (
            [M/Y Of LOG],
            [TIME OF LOG],
            [USER],
            [MISSED PART],
            [NOT IN EPICOR],
            [MISSED BUYOUT],
            [MISSED NON STOCK ITEM],
            [MISSED STOCK ITEM],
            [MISSED AUTOMATED],
            [MISSING PRINTS AFTER QUANTITY],
            [MISSED PRINT NOT SENT TO CHAD],
            OTHER,
            [ADDED MISSING DIMENSION],
            [FIXED DIMENSION]
            )
            Values (@MYLog,@TimeLog, @User, @MissedPart, @NotEpicor, MissedBuyout, @MissedNonStock, @MissedStock,@MissedAutomated, @MissedPrints, @NotSent, @Other, @MissingDimension, @FixedDimension);
            "

    Using con As New OleDb.OleDbConnection(My.Settings.Database1ConnectionString),
            cmdSQL As New OleDbCommand(strSql, con)

        With cmdSQL.Parameters
            .Add("@MYLog", OleDbType.Date, 100).Value = CDate(MonthList2021.SelectedItem.ToString)
            .Add("@TimeLog", OleDbType.Date, 100).Value = DateTime.Now
            .Add("@User", OleDbType.VarChar, 100).Value = UserName
            .Add("@MissedPart", OleDbType.VarChar, 100)
            If MissedPartCHKB.Checked Then
                cmdSQL.Parameters("@MissedPart").Value = MissedPartCHKB.Text
            Else
                cmdSQL.Parameters("@MissedPart").Value = "NEATOL"
            End If
            .Add("@NotEpicor", OleDbType.VarChar, 100)
            If NotInEpicorCHKB.Checked Then
                cmdSQL.Parameters("@NotEpicor").Value = NotInEpicorCHKB.Text
            Else
                cmdSQL.Parameters("@NotEpicor").Value = "NEATOL"
            End If
            .Add("@MissedBuyout", OleDbType.VarChar, 100)
            If MissedBuyoutCHKB.Checked = True Then
                cmdSQL.Parameters("@MissedBuyout").Value = MissedBuyoutCHKB.Text
            Else
                cmdSQL.Parameters("@MissedBuyout").Value = "NEATOL"
            End If
            .Add("@MissedNonStock", OleDbType.VarChar, 100)
            If NonStockCHKB.Checked = True Then
                cmdSQL.Parameters("@MissedNonStock").Value = NonStockCHKB.Text
            Else
                cmdSQL.Parameters("@MissedNonStock").Value = "NEATOL"
            End If
            .Add("@MissedStock", OleDbType.VarChar, 100)
            If MissedSTKItemCHKB.Checked = True Then
                .cmdSQL.Parameters("@MissedStock").Value = MissedSTKItemCHKB.Text
            Else
                cmdSQL.Parameters("@MissedStock").Value = "NEATOL"
            End If
            .Add("@MissedAutomated", OleDbType.VarChar, 100)
            If MissedAutomatedPartCHKB.Checked = True Then
                cmdSQL.Parameters("@MissedAutomated").Value = MissedAutomatedPartCHKB.Text
            Else
                cmdSQL.Parameters("@MissedAutomated").Value = "NEATOL"
            End If
            .Add("@MissedPrints", OleDbType.VarChar, 100)
            If MissingPrintAfterQTYCHKB.Checked = True Then
                cmdSQL.Parameters("@MissedPrints").Value = MissingPrintAfterQTYCHKB.Text
            Else
                cmdSQL.Parameters("@MissedPrints").Value = "NEATOL"
            End If
            .Add("@NotSent", OleDbType.VarChar, 100)
            If MissedPrintsNOTSentChadCHKB.Checked = True Then
                cmdSQL.Parameters("@NotSent").Value = MissedPrintsNOTSentChadCHKB.Text
            Else
                cmdSQL.Parameters("@NotSent").Value = "NEATOL"
            End If
            .Add("@Other", OleDbType.VarChar, 100)
            If OtherCHKB.Checked = True Then
                cmdSQL.Parameters("@Other").Value = OtherTXTB.Text
            Else
                cmdSQL.Parameters("@Other").Value = "NEATOL"
            End If
            .Add("@MissingDimension", OleDbType.VarChar, 100)
            If AddedMissingDimCHKB.Checked = True Then
                cmdSQL.Parameters("@MissingDimension").Value = AddedMissingDimCHKB.Text
            Else
                cmdSQL.Parameters("@MissingDimension").Value = "NEATOL"
            End If
            .Add("@FixedDimension", OleDbType.VarChar, 100)
            If FixedDimensionCHKB.Checked = True Then
                cmdSQL.Parameters("@FixedDimension").Value = FixedDimensionCHKB.Text
            Else
                cmdSQL.Parameters("@FixedDimension").Value = "NEATOL"
            End If
        End With
        con.Open()
        cmdSQL.ExecuteNonQuery()
    End Using
    ResetControls()
End Sub

Private Sub ResetControls()
    Dim lstChkBx As New List(Of CheckBox) From {MissedPartCHKB, MissedAutomatedPartCHKB, NotInEpicorCHKB, NonStockCHKB, MissedSTKItemCHKB, MissedBuyoutCHKB, MissedPrintsNOTSentChadCHKB, MissingPrintAfterQTYCHKB, AddedMissingDimCHKB, FixedDimensionCHKB, OtherCHKB}

    For Each chk As CheckBox In lstChkBx
        chk.Checked = False
    Next
    OtherTXTB.Text = ""
    ' eventually change the month list to automatically select based on the current date
    'If this is a ListBox
    MonthList2021.SelectedIndex = -1
End Sub

我的访问数据库的预览: