I am attempting to convert code to ADO.Net and I am running into an error on updating. Here is the old code:
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
Here is the new code:
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
Getting an "Invalid Insert Syntax" at "daX.Update(ds, "AddStdUnit")" and when I check the "daX.UpdateCommand.CommandText" all the values are "?" but when I view the table data in "dtUni" the row was added and populated, it just won't update to the actual database with the updatecommand.
What am I doing wrong here?
Here are the CommandTexts for insert and update right before daX.Update(ds, "AddStdUnit") is called:
?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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Notice all the "?'s".
And here is the DataTable Visualizer of dtUni right before the update command is called: datatable visualizer
"Count" is a reserved word which was throwing the invalid INSERT syntax when trying to use the daX.update command. Changed the field and all references to "xCount" and the ADO.Net code version above worked just fine. A commenter who deleted his comment for some reason is the one to thank for this answer.
