ASP / ADODB / VBSscript / Access / INSERT INTO

[synergyzw]

Hi

I'm developing an ASP / VBscript web app that uses ADODB for db connectivity saving customer comments in to an access table using the .execute method and a prepared sql style command.

I've discovered the single quote problem where any instance of a single quote within a text / memo field needs to be duplicated.

However I'm also finding that various other customer typed characters are a problem. For example the exclamation mark, brackets and perhaps others.

I've gone over msdn and technet and I can't see any article or material about this as a standard phenomenon but it must surely be a challenge experienced by numerous people saving memo/text type form data in to access/sql tables. I thought I'd easily find a list of "problem" characters and how to deal with them or even some kind persons code excerpt for "standardizing" text/memo content so that it works in this scenario.

Have I totally missed the plot and unearthed a problem that only exisits because I'm taking the wrong approach or is there some simple/standard approach to tackling this issue.

Your suggestions would be welcomed; I'm at my wits end
Thanks

 

[HowardMarks]

This is a common problem, and is usually solved by parsing the data before it is inserted into the DB and replacing the 'troublesome' characters with their ascii counterparts. Take a look at

http://www.bbsinc.com/symbol.html

For a full list of ascii codes.

You will need to use the Replace() function for example

yourstring = Replace(yourstring, "!", "!&quot

Hope this helps! Nick (Software Developer)

http://www.retrographics.co.uk

http://www.retrotest.co.uk

nick@retrographics.fsnet.co.uk
nick.price@myenable.com

 

[synergyzw]

Thanks. I can handle the replace's without a problem. Mostly I'm pleased to know that this is a common problem and that I'm obviously going down an avenue familiar to many others

Whew!

 

[dilettante]

Normally you simply quote the values in your SQL statement using IBM's idea of quotes, the ' character. Remember, SQL was their idea.

And of course you also need to double up any ' chars in the values you are putting into your SQL string.

As in:

strFld1 = "Hi!"
strFld2 = "[bra'ckets(parens)]"
strSQL = "INSERT INTO mytable (myfld1, myfld2) VALUES (

'

" & _

Replace(strFld1, "'", "''")

 & "

'

,

'

" & _

Replace(strFld2, "'", "''")

 & "

'

)"

Which gives:

INSERT INTO mytable (myfld1, myfld2) VALUES (

'

Hi!

'

,

'

[bra

''

ckets(parens)]

'

)

Have I missed something obvious here?

If you have CrLf pairs and such in your values you have another problem. Perhaps you might consider populating an ADO record and Updating it instead of using SQL for this. Something like:

objRecordset.AddNew
objRecordset("myfld1") = strFld1
objRecordset("myfld2") = strFld2
objRecordset.Update