How to insert Funny Characters Into the Database?

[victoria_1018]

Hi All,
I am designing a form using ASP and using Microsoft Access 2000 as the database.

I have set the program to accept funny charatcters such as (~`@#$%^&*|\"':;?/), which mean passing information from one page to another is very smoothly done. (This only happen when the data have not been added into the database)

The problem I am facing now is that the Access database does not allow these funny characters to be inserted into the database. Does anyone know what must I do to the program or the database to allow all these characters to be inserted into the database?
Thanks
Regards
Victoria

[whammy]

I don't have any problem inserting those characters into an Access 2000 database - the only one that should give you a problem is a single quote... and when you insert a single quote replace it with TWO single quotes... i.e.:

INSERT INTO table name (test) VALUES ('" & Replace(test,"'","''") & "')

If you don't believe me look at all the "funny characters" that were inserted into my Access DB just fine:

http://www.solidscripts.com/solidscripts_new/displayscript.asp?sid=4

Including single quotes (look at the comments):

http://www.solidscripts.com/solidscripts_new/displayscript.asp?sid=2

:D

P.S. I'm not sure what you mean by "set up the program to accept funny characters" :confused: - you shouldn't have to do anything to the ASP page except use Server.HTMLEncode() when displaying values in HTML so it doesn't mess up your HTML... all of those characters otherwise will display just fine... can you elaborate further?

:)

[aCcodeMonkey]

victoria_1018,

Me again.
I have a quesion. Is the data stored in the Access DB only to be displayed back on the web?
If so you can work around the problem by using ASCII Dec values
such as:
' ='
/ = /


Sample Text:

This Ain't workin!

Replace Example:
Replace(Request.Form("txtEmailMessage"),"'","'")

Output:

This Ain't workin!

You can build a loop to replace the problematic charactors

The Browser will display the appropriate character.

ASCII Character Codes Chart 1-127 (http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&siteid=us/dev&target=http://msdn.microsoft.com/library/en-us/vsintro7/html/_pluslang_ASCII_Character_Codes_Chart_1.asp)

ASCII Character Codes Chart 128-255 (http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&siteid=us/dev&target=http://msdn.microsoft.com/library/en-us/vsintro7/html/_pluslang_ASCII_Character_Codes_Chart_2.asp)

You might also take a peek at the server.URLEncode and Server.HTMLEncode functions too

Hope this helps... :cool:

[victoria_1018]

Thanks guys, I will try out and see if it work.:o

[whammy]

aCcodeMonkey, that's exactly what Server.HTMLEncode() does FOR you. You're making it more complicated.

:)

[victoria_1018]

Hi,
I had encounter a small problem here.

Part Of Mr SQL is:
SQL3= "INSERT INTO TrackCustomer (Company, Date_Time, StaffID, [SQL]) SELECT '" & session("Company") & "' AS Expr1, #"& NOW() &"# AS Expr2, '" & session("staffID")&"' AS Expr3, '" & SQL1 & "' AS Expr4;"

And I had change it to:
'" & Replace(session("Company"),"'","''") & "'
'" & Replace(SQL1,"'","''") & "'
and error occur.

I had attached that section of codes together with this problem.
Thanks

Regards
Victoria

[whammy]

'" & Replace(SQL1,"'","''") & "'


You don't want to do that to your other Mr. SQL (heh)! Only to the strings you are inserting to the database that might have a ' in them...

[victoria_1018]

Hi, I had tried that but it don't work and receive an error message writen like this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Tina's Car' AS Expr1, #10/9/02 9:26:04 AM# AS Expr2, 'S00000005' AS Expr3, 'UPDATE Customer SET Company = Tina''s Car'.

/SalesReport/SalesTeam1/UpdateConfirmation2.asp, line 96

I had tried changing that SQL statement but it don't work.

Thanks
Regards
Victoria

[whammy]

That's still the problem - you have single quotes in all the wrong places... it should look like:

'Tina''s Car' AS Expr1, #10/9/02 9:26:04 AM# AS Expr2, 'S00000005' AS Expr3

UPDATE Customer SET Company = 'Tina''s Car' WHERE something = 'something'.

(never forget the WHERE statement in updates!)...

Also I've never combined queries like that, one executing another...

If I was making the form, I'd execute all the SQL statements separately to make things clear. It looks very very confusing to me, at any rate. ;)

[victoria_1018]

Thanks, I will try it out.:o