Question using OleDbCommand

[jaspalsinghkhokhar]

hi,

i am trying to insert some data from a dataset into a database.

i have used the following code that works absolutly fine.

For Each dr In myDataSet.Tables(0).Rows
            strSQL = "INSERT INTO [~AgeBandsEmpty] (a,b,c) VALUES ('" & dr ("a") & "','" & dr ("b") & "','" & dr ("c") & "')"

            ' Add command text to the command object
            mySQLCommand.CommandText = strSQL

            ' Execute the command
            mySQLCommand.ExecuteNonQuery()
 Next

however some of the data that i will be inserting has a ' in the data field and after some reading i figured i should be using parameter queries.

so i have now changed my code as follows

For Each dr In myDataSet.Tables(0).Rows
            strSQL = "INSERT INTO [~AgeBandsEmpty] (a,b,c) VALUES (@a,@b,@c)"

            ' Add command text to the command object
            mySQLCommand.CommandText = strSQL

            mySQLCommand.Parameters.Add(New OleDbParameter("@a", dr ("a")))
            mySQLCommand.Parameters.Add(New OleDbParameter("@b", dr ("b")))
            mySQLCommand.Parameters.Add(New OleDbParameter("@c", dr ("c")))

            ' Execute the command
            mySQLCommand.ExecuteNonQuery()
        Next

there are 10 lines of data in the datset. when this second routine is run i get 10 lines added to the [~AgeBandsEmpty] but all data rows added are the same (the data that is held in the first line of teh dataset reopeated 10 times).
any thoughts much appreciated.

edit

i have run similar code gathering a dataset from a webservice and instting the dataset into a sqlServerCompact databsae and all data is inserted as i would expect. please see below

For Each drHeader In dsHeader.Tables(0).Rows
            strSQl = "INSERT INTO tblMobConfig (ID,clientName) "
            strSQl = strSQl & "VALUES (1,@clientName)"

            ' Create Sql Command
            Dim mySQLCommand As SqlCeCommand = myConnection.CreateCommand()

            ' Add command text to the command object
            mySQLCommand.CommandText = strSQl

            ' Add parameters to the command object
            mySQLCommand.Parameters.Add(New SqlCeParameter("@clientName", drHeader("clientName")))

            ' Execute the command
            mySQLCommand.ExecuteNonQuery()
        Next

 

[jaspalsinghkhokhar]

ok, think i found a solution,

Dim mySQLCommand As OleDbCommand = myConnection.CreateCommand()

was placed outside the for loop, by putting it in the for loop all data is loaded correctly.