jaspalsinghkhokhar
Member
- Joined
- Sep 1, 2009
- Messages
- 6
- Programming Experience
- 5-10
hi,
i am trying to insert some data from a dataset into a database.
i have used the following code that works absolutly fine.
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
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
i am trying to insert some data from a dataset into a database.
i have used the following code that works absolutly fine.
VB.NET:
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
so i have now changed my code as follows
VB.NET:
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
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
VB.NET:
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
Last edited: