SQL apostrophe question

  • Thread starter Thread starter kaosyeti via AccessMonster.com
  • Start date Start date
K

kaosyeti via AccessMonster.com

i have an insert into SQL issue where a user may be using an apostrophe and
it's messing with the SQL. here's the code:

strSQL = "insert into tblNewoptions (optiondesc, optionCode, AllocGroup)
values('" & Me.txtboxOption & "'" & ", '" & Me.txtboxOptionCode & "'" & ", '"
& Me.txtboxAllocGroup & "')"

and here's the debug.print line from it:

insert into tblNewoptions (optiondesc, optionCode, AllocGroup) values('Power
Driver's Seat', 'AE8', '123987')

it's the driver's part that is messing things up, obviously. i know there
are numerous tricks with doubling-up the apostrophe but how do i use that
trick when there won't always be one in the corresponding textbox? Thanks
for your help!

Greg
 
Greg,

You should validate the users data before entering it into the database. I
would likely use the replace function to find any apostrophe's - and replace
with 2...

Then when the insert statement is run, you will keep the users format, but
also not break your query. There are other issues here as well (like the
user already entering 2 - would give 4), but this would work for what you
posted...

strTemp = Replace(Me.txtboxOption," ' ", " ' ' ",1,1)
- FInd single ' - replace with 2 (spaces are for clarity only - remove
them)

Then use the new variable in the insert. (Don't forget to dim it)

HTH,

Corey
 
kaosyeti via AccessMonster.com said:
i have an insert into SQL issue where a user may be using an apostrophe and
it's messing with the SQL. here's the code:

strSQL = "insert into tblNewoptions (optiondesc, optionCode, AllocGroup)
values('" & Me.txtboxOption & "'" & ", '" & Me.txtboxOptionCode & "'" & ",
'"
& Me.txtboxAllocGroup & "')"

and here's the debug.print line from it:

insert into tblNewoptions (optiondesc, optionCode, AllocGroup)
values('Power
Driver's Seat', 'AE8', '123987')

it's the driver's part that is messing things up, obviously. i know there
are numerous tricks with doubling-up the apostrophe but how do i use that
trick when there won't always be one in the corresponding textbox? Thanks
for your help!

Greg
Click to expand...

If you use the Replace function, it will only replace the character if it
occurs. Something like:

strSQL = "insert into tblNewoptions (optiondesc, optionCode, AllocGroup)
values('" & Replace(Me.txtboxOption,"'","''") & "'" & ", '" &
Me.txtboxOptionCode & "'" & ", '"
& Me.txtboxAllocGroup & "')"

For clarity, here is the Replace call with extra spaces inserted so you can
see where the quotes are:

Replace(Me.txtboxOption," ' "," ' ' ")

Carl Rapson
 
nice. i had not known about the replace function. this will not only solve
my problem now, but will likely come in very handy all over the place.
thanks to you both!
 
Back
Top