Error will multiple inserts in VB.Net

[andycharger]

Hi,

Im building an application that cycles through records in an access database and applies a check on the data. If it is not compliant, it sticks a row_id and error message into a table called "errors"

However, When there is more than one error (i.e, there is multiple error results) the sql statemt seems to fail.

Here is my code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strNewSql As String
        Dim strID As Integer
        Dim i As Integer
        Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\lynda.mdb"
        Dim strSQL As String = "SELECT * FROM HBMID"
         create Objects of ADOConnection and ADOCommand
        Dim myConn As New OleDbConnection(strDSN)
        Dim myConn2 As New OleDbConnection(strDSN)
        Dim myCmd As New OleDbCommand(strSQL, myConn)

        Dim strItem As String
        Dim datareader As OleDbDataReader = Nothing

        myConn.Open()
        datareader = myCmd.ExecuteReader()
        i = 0
        While datareader.Read()
            i = i + 1
            permitted driver rules
            check for Insured and Named Drivers or Ex Under 25 years O/T
            If datareader("field9") = "7" Or datareader("field9") = "N" Then
                they must be conditon "2". Check this
                If datareader("field19") = "2" Then
                    do nothing

                    ListBox1.Items.Add(datareader("field6"))
                Else
                    must have the wrong code. Put them in the errors list.

                    strNewSql = strNewSql + " Insert into Errors (row_id, error_text) Values (" & i & ",Column S should be 2); "

                End If
            Else
                ok so they are not N or 7.
            End If
        End While


        myConn.Close()

        check there are errors
        If strNewSql = "" Then
            do nothing

        Else
            open the connection
            myConn2.Open()
            Dim MyCmd2 As New OleDbCommand(strNewSql, myConn2)
            myCmd2.ExecuteNonQuery()
            myConn2.Close()
        End If

    End Sub

ThestrNewSql that is output lookslike this:

strNewSql	" Insert into Errors (row_id, error_text) Values (6,Column S should be 2);  Insert into Errors (row_id, error_text) Values (117,Column S should be 2); "

Anyone got any ideas?

 

[karimgarza]

There are (I think) two errors in your program:

the first one is that you are concatenating the insert statements in one string and you want to execute them all at once which is not allowed. You have to do each one at a time.

the second error is related to the first you are reading all the records and then trying to execute the insert.

What you have to do is to make the insert right before your while statement. this way you will execute inserts one at a time.

Hope this helps. I included more or less the way you should code it try it and see what happens I did not have time to run it, but i did ran the Insert example you provided and Access did not accept it.