在添加新行VB.net ADODB到ADO.NEt后从datatable更新数据库时出错

Question

我正在尝试将代码转换为ADO。Net和我在更新时遇到了错误。下面是旧代码:

Private Sub TrackStandardUnit(ByVal iMode As Short, ByVal sSpecies As String, ByVal sProdDesc As String, ByVal sLength As String, ByVal sMultiLgth As String, ByVal sGrades As String, ByVal sCure As String, ByVal sSurface As String, ByVal sThick As String, ByVal sWidth As String, ByVal sgUnits As Single, ByVal iFtg As Short, ByVal iPcs As Short, ByVal iStdPcs As Short, ByVal iStdFtg As Short)
Dim sNewProd As String
If Len(sGrades) = 3 Then
sNewProd = sProdDesc
Else
sNewProd = $"{Mid(sProdDesc, 1, 2)}({Strings.Left(sGrades, Len(sGrades) - 1)}){Mid(sProdDesc, 5)}"
End If
Dim sSql As String = $"SELECT * FROM tblStdUnitTracking WHERE Species = '{sSpecies}' AND ProdDesc = '{sProdDesc}' AND StdUnits = {sgUnits}"
Dim bLgths As Boolean = StdUnitLgthUsed(sThick, sWidth, sSpecies, sGrades, sCure, sSurface) ' Lengths present in std unit file
If bLgths = True Then ' If lengths present in std unit file
sSql = $"{sSql} AND Length = '{sLength}' AND MultiLgth = '{sMultiLgth}'"
End If
Dim rsUnit As ADODB.Recordset = NewRs()
OpenRsOptimistic(sSql, rsUnit)
If rsUnit.RecordCount = 0 Then
rsUnit.AddNew()
rsUnit.Fields("Species").Value = sSpecies
rsUnit.Fields("ProdDesc").Value = sNewProd
rsUnit.Fields("StdUnits").Value = sgUnits
If sgUnits = 0 Then
rsUnit.Fields("PieceCnt").Value = 0
ElseIf iStdPcs > 0 Then
rsUnit.Fields("StdPieceCnt").Value = iStdPcs
rsUnit.Fields("StdFootage").Value = 0
rsUnit.Fields("PieceCnt").Value = iPcs
ElseIf iStdFtg > 0 Then
rsUnit.Fields("StdFootage").Value = iStdFtg
rsUnit.Fields("StdPieceCnt").Value = 0
rsUnit.Fields("PieceCnt").Value = 0
End If
If bLgths = True Then                                           ' If lengths present in std unit file
rsUnit.Fields("Length").Value = sLength                         ' Save length info
rsUnit.Fields("MultiLgth").Value = sMultiLgth
Else                                                            ' Else - no lengths
rsUnit.Fields("Length").Value = ""
rsUnit.Fields("MultiLgth").Value = ""
End If
End If
rsUnit.Fields("Count").Value = rsUnit.Fields("Count").Value + 1
Try
rsUnit.Update()
rsUnit.Close()
Catch ex As Exception
End Try
End Sub

下面是新代码:

Private Sub TrackStandardUnit(ByVal iMode As Short, ByVal sSpecies As String, ByVal sProdDesc As String, ByVal sLength As String, ByVal sMultiLgth As String, ByVal sGrades As String, ByVal sCure As String, ByVal sSurface As String, ByVal sThick As String, ByVal sWidth As String, ByVal sgUnits As Single, ByVal iFtg As Short, ByVal iPcs As Short, ByVal iStdPcs As Short, ByVal iStdFtg As Short)
Dim sNewProd As String
If Len(sGrades) = 3 Then
sNewProd = sProdDesc
Else
sNewProd = $"{Mid(sProdDesc, 1, 2)}({Strings.Left(sGrades, Len(sGrades) - 1)}){Mid(sProdDesc, 5)}"
End If
Dim sSql As String = $"SELECT * FROM tblStdUnitTracking WHERE Species = '{sSpecies}' AND ProdDesc = '{sProdDesc}' AND StdUnits = {sgUnits}"
Dim bLgths As Boolean = StdUnitLgthUsed(sThick, sWidth, sSpecies, sGrades, sCure, sSurface) ' Lengths present in std unit file
If bLgths = True Then ' If lengths present in std unit file
sSql = $"{sSql} AND Length = '{sLength}' AND MultiLgth = '{sMultiLgth}'"
End If

Dim dtUni As New DataTable
Dim constring As String = GetDbConnectionString(gDataPath & "Lisa.mdb", "")
Dim con As OleDbConnection = GetOleDBConnection(constring)
Dim cmd As OleDbCommand = GetOleDBCommand(sSql, con)
Dim daX As New OleDbDataAdapter(cmd)
con.Open()
daX.Fill(dtUni)
ds.Tables.Add(dtUni)
dtUni.TableName = ("AddStdUnit")
con.Close()

Dim dtBuilder As New OleDbCommandBuilder(daX)
dtBuilder.GetUpdateCommand()
daX.UpdateCommand = dtBuilder.GetUpdateCommand()
Dim r As DataRow = dtUni.NewRow
If dtUni.Rows.Count = 0 Then
r("Species") = sSpecies
r("ProdDesc") = sNewProd
r("StdUnits") = sgUnits
r("Footage") = 0
If sgUnits = 0 Then
r("PieceCnt") = 0
ElseIf iStdPcs > 0 Then
r("StdPieceCnt") = iStdPcs
r("StdFootage") = 0
r("PieceCnt") = iPcs
ElseIf iStdFtg > 0 Then
r("StdFootage") = iStdFtg
r("StdPieceCnt") = 0
r("PieceCnt") = 0
End If
If bLgths = True Then                                           ' If lengths present in std unit file
r("Length") = sLength                         ' Save length info
r("MultiLgth") = sMultiLgth
Else                                                            ' Else - no lengths
r("Length") = "0"
r("MultiLgth") = "0"
End If                   ' THIS IS WHERE YOU STOPPED, IT IS NOT UPDATING TO THE MDB BELOW!!!!!!!!!!!!!!!
End If
If IsDBNull(r("Count")) = True Then
r("Count") = 0
Else
r("Count") += 1
End If
Try
dtUni.Rows.Add(r)
daX.AcceptChangesDuringUpdate = True
daX.Update(ds, "AddStdUnit")
Catch ex As Exception
MsgBox(ex.Message)
End Try
ds.Tables.Remove("AddStdUnit")
End Sub

获取"无效的插入语法";在"daX指数。更新(ds,"AddStdUnit";当我检查"daX.UpdateCommand.CommandText"所有值都是"?"但当我在"dtUni"中查看表数据时行已添加并填充，但它不会使用updatcommand更新到实际的数据库。

我在这里做错了什么?

这里是在daX之前插入和更新的命令文本。Update(ds， " addstunit ")被调用:

?dax.UpdateCommand.CommandText
"UPDATE tblStdUnitTracking SET Species = ?, ProdDesc = ?, Length = ?, MultiLgth = ?, StdUnits = ?, StdFootage = ?, StdPieceCnt = ?, PieceCnt = ?, Footage = ?, Count = ? WHERE ((Species = ?) AND (ProdDesc = ?) AND (Length = ?) AND (MultiLgth = ?) AND (StdUnits = ?) AND (StdFootage = ?) AND (StdPieceCnt = ?) AND (PieceCnt = ?) AND ((? = 1 AND Footage IS NULL) OR (Footage = ?)) AND ((? = 1 AND Count IS NULL) OR (Count = ?)))"
?dax.InsertCommand.CommandText
"INSERT INTO tblStdUnitTracking (Species, ProdDesc, Length, MultiLgth, StdUnits, StdFootage, StdPieceCnt, PieceCnt, Footage, Count) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

注意所有的"? "

这里是dtUni的DataTable Visualizer，就在update命令被调用之前:

Answer 1

在一个方法中有太多的代码做了太多不同的事情。

按钮代码严格处理用户界面。我已经声明了所有的变量。我不知道这些值从哪里来，所以我只添加了一个默认值。该按钮调用各种方法并显示错误消息框。

需要处理连接、命令和数据适配器等数据库对象。Using...End Using块处理这个。您可以在使用完DataAdapter后处理它。

总是使用以避免Sql注入。我不得不猜测参数的数据类型。检查数据库是否有正确的类型。

DataAdapter将打开和关闭连接。我不认为有必要为DataSet或DataTable命名。

Private constring As String = GetDbConnectionString(gDataPath & "Lisa.mdb", ""))
Private daX As OleDbDataAdapter
Private dt As DataTable
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim sThick As String = ""
Dim sWidth As String = ""
Dim sSpecies As String = ""
Dim sGrades As String = ""
Dim sCure As String = ""
Dim sSurface As String = ""
Dim sProdDesc As String = ""
Dim sgUnits As Single = 0
Dim sLength As String = ""
Dim sMultiLgth As String = ""
Dim iFtg As Short = 0
Dim iPcs As Short = 0
Dim iStdPcs As Short = 0
Dim iStdFtg As Short = 0

Dim bLgths As Boolean = StdUnitLgthUsed(sThick, sWidth, sSpecies, sGrades, sCure, sSurface) ' Lengths present in std unit file
Try
FillDataTable(bLgths, sSpecies, sProdDesc, sgUnits, sLength, sMultiLgth)
Catch ex As Exception
MessageBox.Show(ex.Message,"Error Filling DataTable")
Exit Sub
End Try
Dim sNewProd As String
If sGrades.Length = 3 Then
sNewProd = sProdDesc
Else
sNewProd = $"{sProdDesc.Substring(0, 2)}({sGrades.Substring(0, sGrades.Length - 1)}){sProdDesc.Substring(5)}"
End If
AddRowtoDataTable(sSpecies, sProdDesc, sGrades, sNewProd, sgUnits,iStdPcs,iPcs,iStdFtg,bLgths,sLength,sMultiLgth)
Try
UpdateDatabase()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Updating Database")
End Try
End Sub
Private Sub FillDataTable(bLgths As Boolean, sSpecies As String, sProdDesc As String, sgUnits As Single, sLength As String, sMultiLgth As String) As DataTable
Dim sSql As String = $"SELECT * FROM tblStdUnitTracking WHERE Species = @Species 
AND ProdDesc = @ProdDesc 
AND StdUnits = @Units"
dt = New DataTable
Using con As New OleDbConnection(constring),
cmd As New OleDbCommand()
With cmd.Parameters
.Add("@Species", OleDbType.VarChar).Value = sSpecies
.Add("@ProdDesc", OleDbType.VarChar).Value = sProdDesc
.Add("@Units", OleDbType.SmallInt).Value = sgUnits
End With
If bLgths Then ' If lengths present in std unit file
sSql &= $" AND Length = @Length AND MultiLgth = @MultiLgth"
cmd.Parameters.Add("@Length", OleDbType.VarChar).Value = sLength
cmd.Parameters.Add("@MultiLgth", OleDbType.VarChar).Value = sMultiLgth
End If
sSql &= ";"
cmd.CommandText = sSql
cmd.Connection = con
daX = New OleDbDataAdapter(cmd)
daX.Fill(dt)
End Using
End Sub
Private Sub AddRowtoDataTable(sSpecies As String, sProdDesc As String, sGrades As String, sNewProd As String, sgUnits As Single, iStdPcs As Integer, iPcs As Integer, iStdFtg As Integer, bLgths As Boolean, sLength As String, sMultiLgth As String)
Dim r As DataRow = dt.NewRow
If dt.Rows.Count = 0 Then
r("Species") = sSpecies
r("ProdDesc") = sNewProd
r("StdUnits") = sgUnits
r("Footage") = 0
If sgUnits = 0 Then
r("PieceCnt") = 0
ElseIf iStdPcs > 0 Then
r("StdPieceCnt") = iStdPcs
r("StdFootage") = 0
r("PieceCnt") = iPcs
ElseIf iStdFtg > 0 Then
r("StdFootage") = iStdFtg
r("StdPieceCnt") = 0
r("PieceCnt") = 0
End If
If bLgths Then  ' If lengths present in std unit file
r("Length") = sLength ' Save length info
r("MultiLgth") = sMultiLgth
Else    ' Else - no lengths
r("Length") = "0"
r("MultiLgth") = "0"
End If ' THIS IS WHERE YOU STOPPED, IT IS NOT UPDATING TO THE MDB BELOW!!!!!!!!!!!!!!!
End If
r("Count") = 0 'The If statement is useless, this is a New Row with no value set for Count.
dt.Rows.Add(r) 'Adding a blank row if rows.count =0
End Sub
Private Sub UpdateDatabase()
Using dtBuilder As New OleDbCommandBuilder(daX)
dtBuilder.GetUpdateCommand()
daX.UpdateCommand = dtBuilder.GetUpdateCommand()
Try
daX.AcceptChangesDuringUpdate = True
daX.Update(dt)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
End Sub

我认为传递的参数太多了。如果你可以创建一个类的模型，你的数据库字段，将很容易传递一个单一的对象与它的所有属性。智能感知在这里很有用。

Answer 2

您的问题看起来是您使用commandBuilder，并且只获得更新命令-您需要插入命令。

只需创建命令构建器-它将自动构建所有3个(插入，更新，删除)

此外，命令生成器有一个阅读器和一个连接，所以不需要再创建更多。

实际上，如果有阅读器，并且不需要更新，那么就不要创建数据适配器。

这样走:

Dim dtUni As New DataTable
Using cmd As New OleDbCommand(Sql,
New OleDbConnection(GetDBConnectionString(gDataPath & "Lisa.mdb", "")))
cmd.Connection.Open()
dtUni.Load(cmd.ExecuteReader)
End Using

所以你只需要一个"适配器"如果你要更新数据——这是你的情况。

那么，试试这个:

Dim dtUni As New DataTable
Using cmd As New OleDbCommand(Sql,
New OleDbConnection(GetDBConnectionString(gDataPath & "Lisa.mdb", "")))
cmd.Connection.Open()
dtUni.Load(cmd.ExecuteReader)
Dim daX As New OleDbDataAdapter(cmd)
Dim dtBuilder As New OleDbCommandBuilder(daX)
cmd.Connection.Open()
daX.Fill(dtUni)
ds.Tables.Add(dtUni)
dtUni.TableName = ("AddStdUnit")
cmd.Connection.Close()
Dim r As DataRow = dtUni.NewRow
' your code here, then
Try
cmd.Connection.Open()
dtUni.Rows.Add(r)
daX.AcceptChangesDuringUpdate = True
daX.Update(dtUni) '<---- just table here. 
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
ds.Tables.Remove("AddStdUnit")

我不确定您是否需要关闭，然后重新打开(但无论哪种方式都可以尝试)。

所以，你只抓取"update"命令生成器中的命令。(不需要-只需让命令构建器按照上面的方式构建，然后您可以更新，插入行到该数据表中，并且一个daX.Update(dtUni)应该工作。

Answer 3

?占位符正确OleDb。这就是OleDb的参数化查询的工作方式(其他提供程序使用命名参数有更好的机制)。?是最终值的占位符，该值为NEVER替换为SQL命令字符串，甚至在数据库本身上也是如此。这些值作为变量保存在单独的位置，就像编写了一个存储过程一样。通过这种方式，可以防止任何sql注入的可能性。

我不知道这是否会帮助你的问题，但我确实通过代码，并做了一些改变，因为我去匹配新的做法，这往往似乎有很大的帮助。具体来说，我确实发现了一个错误，即如何将新行添加到数据集中，这将导致始终将Count设置为零。否则，我个人倾向于不使用CommandBuilder或DataSet.Update()，而是在代码中构建INSERT或UPDATE语句(使用有效的查询参数)。

Private Sub TrackStandardUnit(ByVal Mode As Short, ByVal Species As String, ByVal ProdDesc As String, ByVal Length As String, ByVal MultiLgth As String, ByVal Grades As String, ByVal Cure As String, ByVal Surface As String, ByVal Thick As String, ByVal Width As String, ByVal Units As Single, ByVal Ftg As Short, ByVal Pcs As Short, ByVal StdPcs As Short, ByVal StdFtg As Short)
Dim NewProd As String = ProdDesc
If Grades.Length <> 3 Then
NewProd = $"{ProdDesc.SubString(1, 2)}({Grades.Left(Grades.Length -1)}){ProdDesc.SubString(5)}"
End If
Dim sql As String = "SELECT * FROM tblStdUnitTracking WHERE Species = ? AND ProdDesc = ? AND StdUnits = ?"
Dim Lgths As Boolean = StdUnitLgthUsed(Thick, Width, Species, Grades, Cure, Surface) ' Lengths present in std unit file
If Lgths Then ' If lengths present in std unit file
sql += " AND Length = ? AND MultiLgth = ?"
End If
Dim ds As New DataSet
Dim constring As String = GetDbConnectionString(gDataPath & "Lisa.mdb", "")
Using con As OleDbConnection = GetOleDBConnection(constring), _
cmd As New OleDbCommand(sql, con), _
da As New OleDbDataAdapter(cmd)
' OleDb uses positional parameters. They work in the order they are found in the string
' I have to guess at parameter types and lengths. You should use the exact types from the database.
cmd.Parameters.Add("?", OleDbType.VarWChar, 30).Value = Species
cmd.Parameters.Add("?", OleDbType.VarWChar, 120).Value = ProdDesc
cmd.Parameters.Add("?", OleDbType.Single).Value = StdUnits
If Lgths Then
cmd.Parameters.Add("?", OleDbType.Integer).Value = Convert.ToInt32(Length)
cmd.Parameters.Add("?", OleDbType.Integer).Value = Convert.ToInt32(MultiLgth)
End If
da.Fill(ds)
Dim Uni As DataTable = ds.Tables(0)
Dim Builder As New OleDbCommandBuilder(da)
da.UpdateCommand = Builder.GetUpdateCommand()
If Uni.Rows.Count = 0 Then
Dim r As DataRow = Uni.NewRow
r("Species") = Species
r("ProdDesc") = NewProd
r("StdUnits") = Units
r("Footage") = 0
If sgUnits = 0 Then
r("PieceCnt") = 0
ElseIf iStdPcs > 0 Then
r("StdPieceCnt") = StdPcs
r("StdFootage") = 0
r("PieceCnt") = Pcs
ElseIf iStdFtg > 0 Then
r("StdFootage") = StdFtg
r("StdPieceCnt") = 0
r("PieceCnt") = 0
End If
If Lgths Then                                          
r("Length") = Length                         
r("MultiLgth") = MultiLgth
Else                                                           
r("Length") = "0"
r("MultiLgth") = "0"
End If  
Uni.Rows.Add(r)                
End If
Dim row As DataRow = Uni.Rows(0)
If IsDBNull(row("Count")) Then
row("Count") = 0
Else
row("Count") += 1
End If
Try
da.AcceptChangesDuringUpdate = True
con.Open()
da.Update(ds)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
End Sub

Answer 4

Count"是一个保留字，在尝试使用daX时抛出无效的INSERT语法。更新命令。将字段和所有引用改为"xCount"和ADO。Net代码版本以上工作很好。一个因为某种原因删除了他的评论的评论者是感谢这个答案的人。