Insert renders error

snufse · October 13th, 2008, 04:03 PM

Trying to insert a record using variables from a data set. Get error on variable myRow.Item(63).

insert into rjadevlib.f5514ar (f1, f2, f3) " values(" & "'" myRow.Item(63) & "'" & "," & "'" & myRow.Item(64) & "'" & "," & "'" & myRow.Item(65) & "'" & ")"

The content is:

insert into rjadevlib.f5514ar(f1, f2, f3) values('101 Sansbury's Way ','West Palm Beach ','Fl')

SQL0104: Token S was not valid. Valid tokens: ) ,.
Cause . . . . . : A syntax error was detected at token S. Token S is not a valid token.

Is there a way around this? I would like to test the variable "myRow.Item(63)" beforehand

dparsons · October 13th, 2008, 05:42 PM

You need to escape the ' in Sansbury's.

So you would need to execute the insert the as:
insert into rjadevlib.f5514ar(f1, f2, f3) values('101 Sansbury''s Way ','West Palm Beach ','Fl')

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Old Pedant · October 13th, 2008, 09:28 PM

Since you are using VB, it's easy to do the escaping DParsons noted:

Code:

... = "insert into rjadevlib.f5514ar (f1, f2, f3) " _
    & " values('" Replace(myRow.Item(63),"'","''") & "'," _
    & "'" & Replace(myRow.Item(64),"'","''") & "'," _
    & "'" & Replace(myRow.Item(65),"'","''") & "')"

But can't you come up with better names for your DB fields than f1, f2, and f3????