"wrapper" for raw INSERT INTO text?

[jonceramic]

Hi,

Question... Is there any way to put a wrapper on a raw string going
through SQL, or should I bite the bullet and use a recordset method
instead?

Situation:
I'm trying to have the user add comment data to a table. I calculate
and/or determine all of the variables except for that comment.

So, currently, I'm using an inputbox to grab a string, and then using
that string as part of an INSERT INTO SQL command...

strComments = InputBox("Add any comments about this
addition below. (can be blank)", "Comment")
strSQL = "INSERT INTO tblInstallationOrInUse " & _
"(relEquipmentID, relInstrumentID, CurrentlyInstalled,
Comments, DateInstalled, Location)" & _
" VALUES " & _
"(" & Forms!frminstall.EquipmentID & ", " &
Me.InstrumentID & ", Yes, '" & strComments & _
"', #" & datDate & "#, '" & strLocation & _
"')"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Works fine... Until someone decides to use a ", ', comma, or other
nasty punctuation in my comment box. Then it hoses my SQL statement
because it's just a simple text concatenation.

So....
- Scrub/wrap my comment somehow?
OR
- Just get over myself and use a recordset like a grownup Access
user...

Thanks,

Jon

[Terry Kreft]

A comma shouln't be a problem, neither should a " as you are string
delimiting using ' .

To stop an ' embedded in the string being a problem simply double it up.

strComments = InputBox( _
"Add any comments about this addition below. (can be blank)", _
"Comment")

strComments = Replace(strComments , "'", "''",compare:=vbTextCompare)

' Rest of your code

Notes
"'" is a ' with a " either side
"''" is 2 ' with a " either side

--

Terry Kreft
"jonceramic" <jo********@gmail.comwrote in message
news:11**********************@a34g2000cwb.googlegr oups.com...

Hi,

Question... Is there any way to put a wrapper on a raw string going
through SQL, or should I bite the bullet and use a recordset method
instead?

Situation:
I'm trying to have the user add comment data to a table. I calculate
and/or determine all of the variables except for that comment.

So, currently, I'm using an inputbox to grab a string, and then using
that string as part of an INSERT INTO SQL command...

strComments = InputBox("Add any comments about this
addition below. (can be blank)", "Comment")
strSQL = "INSERT INTO tblInstallationOrInUse " & _
"(relEquipmentID, relInstrumentID, CurrentlyInstalled,
Comments, DateInstalled, Location)" & _
" VALUES " & _
"(" & Forms!frminstall.EquipmentID & ", " &
Me.InstrumentID & ", Yes, '" & strComments & _
"', #" & datDate & "#, '" & strLocation & _
"')"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Works fine... Until someone decides to use a ", ', comma, or other
nasty punctuation in my comment box. Then it hoses my SQL statement
because it's just a simple text concatenation.

So....
- Scrub/wrap my comment somehow?
OR
- Just get over myself and use a recordset like a grownup Access
user...

Thanks,

Jon

[jonceramic]

Yep, that's it. The replace method with two single quotes worked.
Thanks.

The reason the commas and " (double quotes) were issues was because of
how I was testing. If you have a ' (single quote) that causes the
initial problem, subsequent commas and double quotes _are_ parsed.

By eliminating the original problem of the single quote using the
"replace" method, then the comma and double quotes don't have chance
to be a problem.

Thanks Terry,

Jon

On Jan 31, 9:09 am, "Terry Kreft" <[e-mail]:

A comma shouln't be a problem, neither should a " as you are string
delimiting using ' .

To stop an ' embedded in the string being a problem simply double it up.

strComments = InputBox( _
"Add any comments about this addition below. (can be blank)", _
"Comment")

strComments = Replace(strComments , "'", "''",compare:=vbTextCompare)

' Rest of your code

Notes
"'" is a ' with a " either side
"''" is 2 ' with a " either side

--

Terry Kreft

"jonceramic" <[e-mail] in message

news:11**********************@a34g2000cwb.googlegr oups.com...

Hi,

Question... Is there any way to put a wrapper on a raw string going
through SQL, or should I bite the bullet and use a recordset method
instead?

Situation:
I'm trying to have the user add comment data to a table. I calculate
and/or determine all of the variables except for that comment.

So, currently, I'm using an inputbox to grab a string, and then using
that string as part of an INSERT INTO SQL command...

strComments = InputBox("Add any comments about this
addition below. (can be blank)", "Comment")
strSQL = "INSERT INTO tblInstallationOrInUse " & _
"(relEquipmentID, relInstrumentID, CurrentlyInstalled,
Comments, DateInstalled, Location)" & _
" VALUES " & _
"(" & Forms!frminstall.EquipmentID & ", " &
Me.InstrumentID & ", Yes, '" & strComments & _
"', #" & datDate & "#, '" & strLocation & _
"')"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Works fine... Until someone decides to use a ", ', comma, or other
nasty punctuation in my comment box. Then it hoses my SQL statement
because it's just a simple text concatenation.

So....
- Scrub/wrap my comment somehow?
OR
- Just get over myself and use a recordset like a grownup Access
user...

Thanks,

Jon- Hide quoted text -

- Show quoted text -