Apostrophe Messing Up SQL String


R

Ron

I am using the following code:

strSQL = "INSERT into InvoiceDetail(InvoiceRecno, RXNbr, QtyFilled,
Description, Filldate, Amount, Source ) " & _
"Values(" & InvoiceID & ",'" & RXNbr & "'," & quantity & ",'" & Description
& "',#" & Filldate & "#," & copay & ",'B')"

db.Execute strSQL, dbFailOnError

The problem I'm encountering is that I just found out that users are
sometimes entering apostrophes in the description data, so the Execute
statement is misinterpreting the SQL String. Is there a to build the SQL
string so that the Description field will be dropped in literally?
 
Ad

Advertisements

R

RoyVidar

Ron wrote in message said:
I am using the following code:

strSQL = "INSERT into InvoiceDetail(InvoiceRecno, RXNbr, QtyFilled,
Description, Filldate, Amount, Source ) " & _
"Values(" & InvoiceID & ",'" & RXNbr & "'," & quantity & ",'" & Description
& "',#" & Filldate & "#," & copay & ",'B')"

db.Execute strSQL, dbFailOnError

The problem I'm encountering is that I just found out that users are
sometimes entering apostrophes in the description data, so the Execute
statement is misinterpreting the SQL String. Is there a to build the SQL
string so that the Description field will be dropped in literally?

To solve the single quote, you might try something like this

....",'" & Replace(Description, "'", "''") & "',#" ...

which should make Jet understand it.
 
Ad

Advertisements

R

Ron

Thanks Roy! Did the trick!

RoyVidar said:
To solve the single quote, you might try something like this

...",'" & Replace(Description, "'", "''") & "',#" ...

which should make Jet understand it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top