Adding single quotes to dataset fields ready for input to table

[toon10]

Hi



I have a dataset (MyDataSet400) which I use as input to a SQL table called tblDRAAS400. I use the following code to loop through the dataset to populate the table. This works fine until one of the fields in the dataset contains an single quote, i.e. “O’BRIEN”. Here’s my code:



Dim dsCounter As Integer

With MyDataSet400.Tables(0)

For dsCounter = 0 To .Rows.Count - 1 'loop

Sql2 = "INSERT INTO tblDRAAS400 ([Account Code], [Credit

Limit], [Payment Terms], [Oldest Invoice]) VALUES ('" & MyDataSet400.Tables(0).Rows(dsCounter).Item(0) & "', '" & MyDataSet400.Tables(0).Rows(dsCounter).Item(1) & "', '" & MyDataSet400.Tables(0).Rows(dsCounter).Item(2) & "', '" & MyDataSet400.Tables(0).Rows(dsCounter).Item(3) & "')"

MyDataAdapter = New System.Data.SqlClient.SqlDataAdapter(Sql2, MyConnection)

MyDataAdapter.Fill(MyDataSet, "tblDRAAS400")

Next

End With



I’ve had this problem in the past with VB 6 and managed to get around it by using the replace command and adding an extra single quote to surround all of my fields. I’m not sure how to take the code about and somehow achieve the same in .NET.



Any ideas?



Thanks

Andrew

 

[Administrator]

From my understanding ADO handled this, then ADO.NET regressed back to the way you mentioned. You can do a String.Replace to double it up to resolve the issue like you did prior.

 

[toon10]

Yeah, I'm looking into trying that now.

I've converted my code to read...

...
Dim dsCounter AsInteger
Dim ImportString, FItemArray, strTemp AsString
With MyDataSet400.Tables(0)
For dsCounter = 0 To .Rows.Count - 1 'loop
strTemp = CType(MyDataSet400.Tables(0).Rows(dsCounter), String)
ImportString = Replace(strTemp, "'", "''")
FItemArray = Split(ImportString, ",")
f1 = "'" & FItemArray(0) & "'"
f2 = "'" & FItemArray(1) & "'"
f3 = "'" & FItemArray(2) & "'"
f4 = "'" & FItemArray(3) & "'"

Sql2 = "INSERT INTO tblDRAAS400 ([Account Code], [Credit Limit], [Payment
Terms], [Oldest Invoice]) VALUES (" & f1 & ", " & f2 & ", " & f3 & ", " & f4 &
")"
MyDataAdapter = New System.Data.SqlClient.SqlDataAdapter(Sql2,
MyConnection)
MyDataAdapter.Fill(MyDataSet, "tblDRAAS400")
ImportString = ""
Next
EndWith

Unlike VB6, it says that I can't covert MyDataSet400.Tables(0).Rows(dsCounter) into a string to do the compare.

I'm still trying!

Cheers
Andrew

 

[Administrator]

Let's give this a try, a few tweaks:


Dim dsCounter As Integer
Dim ImportString As String
Dim FItemArray() As String
Dim strTemp as String = ""
With MyDataSet400.Tables(0)
For dsCounter = 0 To .Rows.Count - 1 'loop
strTemp = MyDataSet400.Tables(0).Rows(dsCounter).ToString()
FItemArray = Split(ImportString.Replace("'","''"), ",")
f1 = "'" & FItemArray(0) & "'"
f2 = "'" & FItemArray(1) & "'"
f3 = "'" & FItemArray(2) & "'"
f4 = "'" & FItemArray(3) & "'"

Sql2 = "INSERT INTO tblDRAAS400 ([Account Code], [Credit Limit], [Payment
Terms], [Oldest Invoice]) VALUES (" & f1 & ", " & f2 & ", " & f3 & ", " & f4 &
")"
MyDataAdapter = New System.Data.SqlClient.SqlDataAdapter(Sql2,
MyConnection)
MyDataAdapter.Fill(MyDataSet, "tblDRAAS400")
ImportString = ""
Next
EndWith

 

[toon10]

I'll give that a try, thanks.

I used the CType method to try to convert the dataset to a string but that only worked when I drilled down to the individual dataset row items so it ended up being rather a long piece of code!

Andrew

 

[bloukewer]

HERE's SOME CODE

You can use this piece of code I wrote for an application of mine. I placed the function in a module so that I were able to call it up whenever I wrote to my database. Feel welcome to copy and paste it. It works 100%.


'******************************************************

Friend Function ParseToSql(ByVal InputString As String) As String
Dim strtemp As String
strtemp = ""
Dim cnt As Integer

For cnt = 0 To InputString.Length - 1
If InputString.Substring(cnt, 1).Equals("'") Then
strtemp &= "''"
ElseIf InputString.Substring(cnt, 1).Equals("""") Then
strtemp &= """"
'MessageBox.Show(cnt & " replaced. New string = " & strtemp)
Else
strtemp &= InputString.Substring(cnt, 1)
End If
Next
Return strtemp
End Function

'**********************************************


Please note... It might look like double quotes, but it's not. It's double single quotes inside single double quotes ENJOY!