Apostrophe Messing Up SQL String

  • Thread starter Thread starter Ron
  • Start date Start date
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?
 
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?
Click to expand...

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

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

which should make Jet understand it.
 
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.
Click to expand...
 
Back
Top