Error when updating database from datatable after adding New Row VB.net ADODB to ADO.NEt-CodePudding

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

CodePudding user response：

There was way too much code in a single method doing too many different things.

The button code deals strictly with the user interface. I have declared all the variables. I don't know where the values for these come from so I just added a default value. The button calls the various methods and displays message boxes for errors.

Database objects like connections, commands and data adapter need to be disposed. Using...End Using blocks handle this. You can dispose the DataAdapter after you are finished with it.

Always use Parameters to avoid Sql injection. I had to guess at the datatypes of the parameters. Check your database for the correct types.

The DataAdapter will open and close the connection. I didn't see the need for a DataSet or a name for the 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

I think there are way too many parameters being passed. If you could create a class the models you database fields it would be easy to pass a single object with all its properties. Intellisense is helpful here.

CodePudding user response：

Your problem looks to be that you using commandBuilder, and ONLY getting the update command - you need the insert command.

Just ONLY create the command builder - it will and should build all 3 for you automatic (insert, update, delete)

Also, command builder has a reader, and a connection, so no need to make more.

In fact, with a reader, and NOT needing up todate, then do NOT create a data adaptor.

Go this way:

    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

So you only need a "adaptor" if you going to update the data - which is your case.

So, try this:

    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")

I am not sure if you need to close, and then re-open (but try it either way).

So, you are grabbing only the "update" command from command builder. (don't need to - just let command builder build as per above, and you can then EITHER update, insert rows into that data table, and the one daX.Update(dtUni) should work.

CodePudding user response：

The ? placeholders are correct for OleDb. This is how parameterized queries work for OleDb (other providers have an even better mechanism using named parameters). The ? is a placeholder for the final value, which is NEVER substituted into the sql command string, even on the database itself. The values are kept in a separate place as variables, almost as if you'd written a stored procedure. In this way, any possibility of sql injection is prevented.

I don't know if this will help your issue, but I did go through the code and made a few changes as I went to match newer practices, and this often seems to help quite a bit. Specifically, I did spot a mistake around how the new row is added to the dataset that would result in always setting Count to zero. Otherwise, I tend not to use CommandBuilder or DataSet.Update() personally, and instead build INSERT or UPDATE statements in code (with valid query parameters) as appropriate.

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

CodePudding user response：

"Count" is a reserved word which was throwing the invalid INSERT syntax when trying to use the daX.update command. Changed to field and all references to "xCount" and the ADO.Net code above worked just fine. A commenter who deleted his comment for some reason is the one to thank for this answer.