I am looping thru DataReader and constructing a sql query to insert to
another database.
When the data type of the field is string I insert the field value using a
single quote.
When the value of the field has a single quote in it like "O'Toole", how can
I construct the string ?
Do While drSQL.Read
sSQL = "insert into " & sTableName & " values ("
sSQL2 = ""
For x = 0 To iFieldCnt - 1
If IsDBNull(drSQL.Item(x)) Then
sSQL2 = sSQL2 & ",null"
ElseIf drSQL.GetFieldType(x) Is GetType(String) Or
drSQL.GetFieldType(x) Is GetType(Date) Then
sSQL2 = sSQL2 & ",'" & "''" & drSQL.Item(x) &
'" -WHEN the value is O'Toole this becomes 'O'Toole'
Else
sSQL2 = sSQL2 & "," & drSQL.Item(x)
End If
Next x
sSQL2 = Mid(sSQL2, 2, Len(sSQL2))
sSQL = sSQL & sSQL2 & ")"
cmd.CommandText = sSQL -WHEN the value of 1 of the field
is O'Toole this becomes "Insert into myTable values ('O'Toole') and got an
error when executing the ExecuteNonQuery
cmd.ExecuteNonQuery()
Loop