pkimk
asked on
VB6 & Ms. Access
For VB6 enterprise & Ms. Access 97
I use SQL to do whatever transaction (add, edit, delete, etc) intercourse with Ms. Access as database.
The transaction failed whenever SQL statement content single quote ( ' ) from user input variables.
e.g.
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing
operator) in query expression 'User_ID = 'KEN' AND Passwd = 'ke'nc".
It should allow ke'nc as my input passwd veriable.
Please advise.
I use SQL to do whatever transaction (add, edit, delete, etc) intercourse with Ms. Access as database.
The transaction failed whenever SQL statement content single quote ( ' ) from user input variables.
e.g.
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing
operator) in query expression 'User_ID = 'KEN' AND Passwd = 'ke'nc".
It should allow ke'nc as my input passwd veriable.
Please advise.
Refer to MS Access help the topic is "Quotation Marks in Strings" the solution of your problem at the paragraph named "Include Double Quotation Marks "
if you succeeded in replacing all single quotes with double single quotes in the user input so no problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have about 25 text fields to update per transaction. User input can be combination of single & double quotes data.
If single quote replace by double quote and store in the database, what happen when I want to retrive my original data.
From your suggestion, if
user input : ke"n'c
after replace : ke"n"c store in DB
retrive & replace : ke'n'c ???
If single quote replace by double quote and store in the database, what happen when I want to retrive my original data.
From your suggestion, if
user input : ke"n'c
after replace : ke"n"c store in DB
retrive & replace : ke'n'c ???
The replace function can be used on all input fields that are strings, regardless of how many single quotes they contain. the call to:
MyString = replace(MyString, "'", "''") should be read as "replace every single occurence of the single quote character with two instead". Following this rule, your example above would be:
input: ke''n'c
after replace: ke''''n''c
Howerver, it is very important to note that it will not be stored in the actual database this way. The replace syntax is only necessary for transact SQL to successfully parse the statement prior to inserting it. Thus, when you read the value from the database, it would again (automatically) be:
ke''n'c (the same as the original, non-replaced input).
In other words, you can safely do the replace on every single string before inserting it into the db, regardless of whether they contain single quotes or not. You should not perform a replace when reading data back from the db, however, since what's actually stored in the db will be the original, non-replaced strings.
Best regards,
Mans Johnsson.
MyString = replace(MyString, "'", "''") should be read as "replace every single occurence of the single quote character with two instead". Following this rule, your example above would be:
input: ke''n'c
after replace: ke''''n''c
Howerver, it is very important to note that it will not be stored in the actual database this way. The replace syntax is only necessary for transact SQL to successfully parse the statement prior to inserting it. Thus, when you read the value from the database, it would again (automatically) be:
ke''n'c (the same as the original, non-replaced input).
In other words, you can safely do the replace on every single string before inserting it into the db, regardless of whether they contain single quotes or not. You should not perform a replace when reading data back from the db, however, since what's actually stored in the db will be the original, non-replaced strings.
Best regards,
Mans Johnsson.
If you will use single quote as string delimiters in the SQL statment no danger of using double quote at all in the string the problem that time will be with single quote only and vise-versa. so replace only the single quote with double single quote and leave any other charcter as it is.
ASKER
Thanks all.