471,355 Members | 1,569 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

"wrapper" for raw INSERT INTO text?

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

Jan 30 '07 #1
2 2694
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

Jan 31 '07 #2
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" <terry.kr...@mps.co.ukwrote:
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" <joncera...@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- Hide quoted text -

- Show quoted text -

Jan 31 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Roy Smith | last post: by
19 posts views Thread by les_ander | last post: by
1 post views Thread by David A Barajas | last post: by
reply views Thread by mailforpr | last post: by
2 posts views Thread by Peter Laman | last post: by
reply views Thread by Peter Frost | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.