-
(Thanks for people who replied to my last query about .dbf to .mdb, it was a lot of help, however....)
I am constructing an SQL string which INSERTS data into an mdb database. I build the string by placing the variable for each field (i.e. '" & varname & "' ), but one of the variables has a double quotation mark in the string (e.g. tyre 19.5").
So, the SQL string errors because the resulting string shows 'tyre 19.5"'. Is there any way of getting around the problem of strings containing double (and single) quotation marks, so that the resulting SQL statement does not error?
this is the code:
strSend = "INSERT INTO flat "
strSend = strSend & "("
strSend = strSend & Flat01 & ", " & Flat02 & ", " & Flat03 & ", " & Flat04 & ", " & Flat05 & ", " & Flat06 & ", " & Flat07 & ", " & Flat08 & ", " & _
Flat09 & ", " & Flat10 & ", " & Flat11 & ", " & Flat12 & ", " & Flat13 & ", " & Flat14 & ", " & Flat15 & ", " & Flat16 & ", " & Flat17 & ", " & Flat18 & ", " & _
Flat19 & ", " & Flat20 & ", " & Flat21 & ", " & Flat22 & ", " & Flat23 & ", " & Flat24 & ", " & Flat25 & ", " & Flat26 & ", " & Flat27 & ", " & Flat28 & ", " & _
Flat29 & ", " & Flat30 & ", " & Flat31 & ", " & Flat32 & ", " & Flat33 & ", " & Flat34 & ", " & Flat35 & ", " & Flat36 & ", " & Flat37 & ", " & Flat38 & ", " & _
Flat39 & ", " & Flat40 & ", " & Flat41 & ", " & Flat42 & ", " & Flat43 & ", " & Flat44 & ", " & Flat45 & ", " & Flat46 & ", " & Flat47 & ", " & Flat48 & ", " & Flat49 & ")"
strSend = strSend & " VALUES ("
For x = 0 To 47
strSend = strSend & "'" & rsDBF.Fields(x) & "', "
Next x
strSend = strSend & ")"
retval = cnMDB.Execute(strSend)
(It's one of the rsdbf.Fields(x) strings that contains a ".
Thanks if anyone can help me, I'm on a really tight deadline........
Phil
-
Single/Double Quotes
To insert a single quote you need to replace it with two single quotes. Therefore, "O'Neil" becomes "O''Neil". Use the replace function
Code:
tobeinserted = replace(originalString, "'", "''")
I'm not sure how to handle the double quotes because I usually just use
Code:
rsname!fieldname = variablename
and that takes care of double quotes (you still have to replace single quotes though).
-John
[Edited by RoyceWindsor1 on 05-04-2000 at 04:37 PM]
-
I think if you use:
rs.open ....
rs.addnew
rs.fields("test")="test's"
rs.update
Those problem won't occur.
