vb.net - 如何从 Visual Studio 将新记录添加到 Access 数据库。(VB.NET)
问题描述
免责声明:我对编程很陌生,所以我的编码技能充其量是低于标准的。
我的目标是让我的 Visual Studio 2019 项目(名为 Detailing Error Log )根据选中的复选框将新记录行添加到我的 Microsoft Access 数据库表(名为 Database1 的 accdb 文件,名为 Data Collection 的表)中。每当单击“导入”按钮时,我只想添加行,而不是删除行。然后将保存数据库,并且取消选中我的 Visual Studio 项目中的复选框。数据库将用于存储该数据,直到协调的 Visual Studio 程序使用它来计算特定月份内特定文本的出现次数。从那里它将以图表的形式显示。
我已经使用 Excel 成功完成了这项工作,但是当文件变得太大时,会导致很大的延迟;每当我运行调试器和刷新图形时。我知道很可能会有明显的加载时间,但我希望将其最小化。
我的问题是我在 con.Open(): "System.InvalidOperationException: 'The 'Microsoft.ACE.OLEDB.12.0Data Source = S:\software\System\DPD & DEL (KPI)\Database1 .accdb' 提供程序未在本地计算机上注册。”
这个错误是什么意思?这也是目前唯一抛出的错误。
这是我的代码供参考,在此先感谢您的帮助!
*NEATOL = "记录时无条目"
Private Sub ConnectionPrep(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim dbProvider As String
Dim dbSource As String
Dim sql As String
Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0"
dbSource = "Data Source = S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb; Persist Security Info = False"
con.ConnectionString = dbProvider & dbSource
con.Open()
End Sub
Private Sub InputInformation(sender As System.Object, e As System.EventArgs) Handles ImporttBUT.Click
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim cb As New OleDbCommandBuilder()
Dim dsnewrow As DataRow
dsnewrow = ds.Tables("Data Collection").NewRow()
dsnewrow.Item("M/Y OF LOG") = Me.MonthList2021.SelectedItem
dsnewrow.Item("TIME OF LOG") = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")
dsnewrow.Item("USER") = UserName
dsnewrow.Item("STOCK NUMBER") = Me.StockNumberTXTB.Text
If MissedPartCHKB.Checked = True Then
dsnewrow.Item("MISSED PART") = Me.MissedPartCHKB.Text
ElseIf MissedPartCHKB.Checked = False Then
dsnewrow.Item("MISSED PART") = "NEATOL"
End If
If NotInEpicorCHKB.Checked = True Then
dsnewrow.Item("NOT IN EPICOR") = Me.NotInEpicorCHKB.Text
ElseIf NotInEpicorCHKB.Checked = False Then
dsnewrow.Item("NOT IN EPICOR") = "NEATOL"
End If
If MissedBuyoutCHKB.Checked = True Then
dsnewrow.Item("MISSED BUYOUT") = Me.MissedBuyoutCHKB.Text
ElseIf MissedBuyoutCHKB.Checked = False Then
dsnewrow.Item("MISSED BUYOUT") = "NEATOL"
End If
If NonStockCHKB.Checked = True Then
dsnewrow.Item("MISSED NON STOCK ITEM") = Me.NonStockCHKB.Text
ElseIf NonStockCHKB.Checked = False Then
dsnewrow.Item("MISSED NON STOCK ITEM") = "NEATOL"
End If
If MissedSTKItemCHKB.Checked = True Then
dsnewrow.Item("MISSED STOCK ITEM") = Me.MissedSTKItemCHKB.Text
ElseIf MissedSTKItemCHKB.Checked = False Then
dsnewrow.Item("MISSED STOCK ITEM") = "NEATOL"
End If
If MissedAutomatedPartCHKB.Checked = True Then
dsnewrow.Item("MISSED AUTOMATED") = Me.MissedAutomatedPartCHKB.Text
ElseIf MissedAutomatedPartCHKB.Checked = False Then
dsnewrow.Item("MISSED AUTOMATED") = "NEATOL"
End If
If MissingPrintAfterQTYCHKB.Checked = True Then
dsnewrow.Item("MISSING PRINTS AFTER QUANTITY") = Me.MissingPrintAfterQTYCHKB.Text
ElseIf MissingPrintAfterQTYCHKB.Checked = False Then
dsnewrow.Item("MISSING PRINTS AFTER QUANTITY") = "NEATOL"
End If
If MissedPrintsNOTSentChadCHKB.Checked = True Then
dsnewrow.Item("MISSED PRINT NOT SENT TO CHAD") = Me.MissedPrintsNOTSentChadCHKB.Text
ElseIf MissedPrintsNOTSentChadCHKB.Checked = False Then
dsnewrow.Item("MISSED PRINT NOT SENT TO CHAD") = "NEATOL"
End If
If OtherCHKB.Checked = True Then
dsnewrow.Item("OTHER") = Me.OtherTXTB.Text
ElseIf OtherCHKB.Checked = False Then
dsnewrow.Item("OTHER") = "NEATOL"
End If
If AddedMissingDimCHKB.Checked = True Then
dsnewrow.Item("ADDED MISSING DIMENSION") = Me.AddedMissingDimCHKB.Text
ElseIf AddedMissingDimCHKB.Checked = False Then
dsnewrow.Item("ADDED MISSING DIMENSION") = "NEATOL"
End If
If FixedDimensionCHKB.Checked = True Then
dsnewrow.Item("FIXED DIMENSION") = Me.FixedDimensionCHKB.Text
ElseIf FixedDimensionCHKB.Checked = False Then
dsnewrow.Item("FIXED DIMENSION") = "NEATOL"
End If
ds.Tables("Counting").Rows.Add(dsnewrow)
da.Update(ds, "Counting")
MsgBox("Entry succesfully added to database.")
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 = ""
End Sub
解决方案
以这种方式构建连接字符串似乎是个好主意,但事实并非如此。如果你想打破它,那么正确地这样做:
Dim builder As New OleDbConnectionStringBuilder
builder.Provider = "Microsoft.ACE.OLEDB.12.0"
builder.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb"
builder.PersistSecurityInfo = False
Using connection As New OleDbConnection(builder.ConnectionString)
'...
End Using
做得好,你不能忘记点点滴滴,因为没有点点滴滴。您只需提供值。当它与数据源无关时,您也不会最终将PersistSecurityInfo
值放入一个名为的变量中。dbSource
假装分离你的连接字符串组件但实际上没有正确地做它比根本不做更糟糕。
您还可以像这样简化上面的代码:
Dim builder As New OleDbConnectionStringBuilder
With builder
.Provider = "Microsoft.ACE.OLEDB.12.0"
.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb"
.PersistSecurityInfo = False
End With
或者,更进一步:
Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = "S:\software\Melton System\DPD & DEL (KPI)\Database1.accdb",
.PersistSecurityInfo = False}
推荐阅读
- owasp - OWASP zap-api-scan.py url 排除
- php - GuzzleHttp\Exception\ClientException:客户端错误:`POST https://mms.shaparak.ir/merchant/webService/writeExternalRequest`400 错误请求
- python - 如何在不标记所有代码的情况下在 PyCharm 中运行多行函数调用?
- python - 大熊猫行之间的减法
- gcc - gprof 不为需要合理时间执行的程序产生任何输出
- php - 检查最后一次请求后 caldav 是否有变化
- python - 存储计算字段时,它在 DB 中存储 0,为什么?
- python - Appium + android + python 如何获取我刚刚点击的 URL?
- mongodb - 如何在 typegoose (mongoose) 中正确引用其他类并仅保存引用 (ObjectId)?
- gdb - GDB 显示 LLVM IR 变量失败