Hi all,

i still have a problem with access97 & Memo fields.

When people want to enter messages they sometimes use a:  ",  or a : '.

The first thing was easy to handle, i set my query like

strSQL= "INSERT INTO TbleName(field1, field2, field3) VALUES ("char(34) &

field1.value & char(34) & "," & char(34) & field2.value & char(34) & "," &

char(34) & field3.value & char(34) & ")"



Oke now it was no problem if the user entered a " ' ", but still if they

entered a " then i got a error (Syntax error (missing operator) in query

expression ),

So does anyone have idea of how to solve this one ?

any ideas are most welkome :-)

thnx



werner

Write your query like this (and avoid the Chr(34)!)



strSQL = "INSERT INTO TbleName(field1, field2, field3) "

strSQL = strSQL & "VALUES (" & field1.value & ", "

strSQL = strSQL & field2.value & ", "



etc you can write the " and ' as necessary.



However, for input submitted by a user, or extracted from a database you

need to escape the ' and the " by doubling them (eg replacing each ' with

two '' and each " with two "")



You can use the Replace() function to do this.

However I suggest putting this replace in a function - it'll make it easier

to reuse on the each page that you need it



Cheers

Ken





----- Original Message -----

From: "werner teunissen" 

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, June 21, 2000 5:54 PM

Subject: [asp_databases] insert " into a database Howto??





> Hi all,

> i still have a problem with access97 & Memo fields.

> When people want to enter messages they sometimes use a:  ",  or a : '.

> The first thing was easy to handle, i set my query like

> strSQL= "INSERT INTO TbleName(field1, field2, field3) VALUES ("char(34) &

> field1.value & char(34) & "," & char(34) & field2.value & char(34) & "," &

> char(34) & field3.value & char(34) & ")"

>

> Oke now it was no problem if the user entered a " ' ", but still if they

> entered a " then i got a error (Syntax error (missing operator) in query

> expression ),

> So does anyone have idea of how to solve this one ?

> any ideas are most welkome :-)

> thnx

>

> werner

>

>

there are a couple more articles linked to this one.   hope this helps...



http://support.microsoft.com/support/kb/articles/Q190/7/42.ASP?LN=EN-US&SD=tech&FR=0



------Original Message------

From: "werner teunissen" 

To: "ASP Databases" <asp_databases@p...>

Sent: June 21, 2000 7:54:05 AM GMT

Subject: [asp_databases] insert " into a database Howto??





Hi all,

i still have a problem with access97 & Memo fields.

When people want to enter messages they sometimes use a:  ",  or a : '.

The first thing was easy to handle, i set my query like

strSQL= "INSERT INTO TbleName(field1, field2, field3) VALUES ("char(34) &

field1.value & char(34) & "," & char(34) & field2.value & char(34) & "," &

char(34) & field3.value & char(34) & ")"



Oke now it was no problem if the user entered a " ' ", but still if they

entered a " then i got a error (Syntax error (missing operator) in query

expression ),

So does anyone have idea of how to solve this one ?

any ideas are most welkome :-)

thnx



werner

Ken,

Thanx for your advice, i also visited

http://support.microsoft.com/support/kb/articles/Q190/7/42.ASP?LN=EN-US&SD=t

ech&FR=0

and indeed i found the answer, but can you explain why to avoid the Chr(34)

?

and thanx to osamu who point me at the URL.



werner



Write your query like this (and avoid the Chr(34)!)

strSQL = "INSERT INTO TbleName(field1, field2, field3) "

strSQL = strSQL & "VALUES (" & field1.value & ", "

strSQL = strSQL & field2.value & ", "

etc you can write the " and ' as necessary.

However, for input submitted by a user, or extracted from a database you

need to escape the ' and the " by doubling them (eg replacing each ' with

two '' and each " with two "")



You can use the Replace() function to do this.

However I suggest putting this replace in a function - it'll make it

easier

to reuse on the each page that you need it

Cheers

Ken

Why use Chr(34) when you can just use " - if you keep using Chr(34) your

code will become unreadable!



strSQL = SELECT field1, field2, field3 "

strSQL = strSQL & "FROM table1 "

strSQL = strSQL & "WHERE field1 = " & somevalue & " "

strSQL = strSQL & "AND field2 = '" & someothervalue & "' "

strSQL = strSQL & "ORDER BY field1 DESC "



see, no Chr(34) required!



Cheers

Ken





----- Original Message -----

From: "werner teunissen" 

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, June 22, 2000 8:12 PM

Subject: [asp_databases] Re: insert " into a database Howto??





> Ken,

> Thanx for your advice, i also visited

>

http://support.microsoft.com/support/kb/articles/Q190/7/42.ASP?LN=EN-US&SD=t

> ech&FR=0

> and indeed i found the answer, but can you explain why to avoid the

Chr(34)

> ?

> and thanx to osamu who point me at the URL.

>

> werner

>