2. ASPRunnerPro General questions
  3. Problem entering specific characters
This topic is locked

Problem entering specific characters

 		1/5/2008 8:33:06 AM
ASPRunnerPro General questions
P
 Philip author

Hello
ASPRunner 5.1 / 327
I have problems to add records when a simple TEXT field contains characters as follows:
works)
[color="#FF0000"]D'Jeen can not be added (but DJeen works)
Any ideas what's the reaseon for the problem?
Regards

Philip
P
 		Philip author 1/5/2008

Hello

ASPRunner 5.1 / 327
I have problems to add records when a simple TEXT field contains characters as follows:
works)
[color="#FF0000"]D'Jeen can not be added (but DJeen works)


I did some research and found this: the reason for the problem is caused by an strSQLUpdate command.

I use this code in the after Edit event:
[indent]'** UPDATE a record in another table ****
strSQLUpdate = "UPDATE stamm SET "

strSQLUpdate = strSQLUpdate & "H_Kategorie_AOA=" & "'" & dict("H_Kategorie_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Klasse_AOA=" & "'" & dict("H_Klasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Name_AOA=" & "'" & dict("H_Name_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Rasse_AOA=" & "'" & dict("H_Rasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_SHSB_AOA=" & "'" & dict("H_SHSB_AOA") & "'"

strSQLUpdate = strSQLUpdate & " where H_Lizenz_AOA=" & Session("UserID")

dbConnection.Execute strSQLUpdate
BeforeEdit = True[/indent]
This code works fine, as long the above mentioned characters are not used. Any idea how to change the SQL statement ?
Thanks

Philip
P
 		Philip author 1/7/2008

Some other research leads me close to the solution but ...
The problem is the single quote ( ' ) which is as well the delimiter used in SQL Update command.

As a solution I found this: use 2 single quotes instead of 1. The approach I'm looking for is to have a kind of REPLACE command like this:
Replace(Field_Name,"'","''")
How can I implement this (or somethung similar) it this code?

** UPDATE a record in another table ****

strSQLUpdate = "UPDATE stamm SET "

strSQLUpdate = strSQLUpdate & "H_Kategorie_AOA=" & "'" & dict("H_Kategorie_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Klasse_AOA=" & "'" & dict("H_Klasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Name_AOA=" & "'" & dict("H_Name_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Rasse_AOA=" & "'" & dict("H_Rasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_SHSB_AOA=" & "'" & dict("H_SHSB_AOA") & "'"

strSQLUpdate = strSQLUpdate & " where H_Lizenz_AOA=" & Session("UserID")

dbConnection.Execute strSQLUpdate
BeforeEdit = True


Philip
P
 		Philip author 1/9/2008

The problem is the single quote ( ' ) which is as well the delimiter used in SQL Update command.

As a solution I found this: use 2 single quotes instead of 1. I use this code in after record added event (example with 2 critical fields and 1 that can't contain single quote by definition):
'** REPLACE 1 single quote with 2 single quotes ****
H_Name_clean = dict("H_Name_AOA")

H_Name_clean = Replace(H_Name_clean,"'","''")

H_Rasse_clean = dict("H_Rasse_AOA")

H_Rasse_clean = Replace(H_Rasse_clean,"'","''")
'** UPDATE ****
strSQLUpdate = "UPDATE stamm SET "

strSQLUpdate = strSQLUpdate & "H_Name_AOA=" & "'" & (H_Name_clean) & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Rasse_AOA=" & "'" & (H_Rasse_clean) & "'" &", "

strSQLUpdate = strSQLUpdate & "H_SHSB_AOA=" & "'" & dict("H_SHSB_AOA") & "'"

strSQLUpdate = strSQLUpdate & " where H_Lizenz_AOA=" & session("H_Lizenz_AOA")

dbConnection.Execute strSQLUpdate
Philip