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.

correctcode.vb