ADO SQL Variables

[jenkinsp]

(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

To insert a single quote you need to replace it with two single quotes. Therefore, "O'Neil" becomes "O''Neil". Use the replace function

tobeinserted = replace(originalString, "'", "''")

I'm not sure how to handle the double quotes because I usually just use

  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]

[lychew]

I think if you use:

rs.open ....
rs.addnew
rs.fields("test")="test's"
rs.update

Those problem won't occur.