Solved

VB6 & Ms. Access

Posted on 2000-03-06
7
402 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:pkimk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 

Expert Comment

by:WaelEg
ID: 2590707
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 "
0
 

Expert Comment

by:WaelEg
ID: 2590788
if you succeeded in replacing all single quotes with double single quotes in the user input so no problem
 
0
 

Accepted Solution

by:
johnssonm earned 10 total points
ID: 2590908
You need to escape the single quotes in order for Access to understand that the quote is part of the string, and not a start / end delimiter. In VB, use the replace function to replace all occurences of a single quote with double quotes (thus escaping it). See the example below:

...
dim MyString
MyString = "Single'quotes'here"
MyString = replace(MyString, "'", "''")
...

You can now use MyString as an argument to a Transact SQL statement without worrying about the included quotes.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pkimk
ID: 2594362
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  ???
0
 

Expert Comment

by:johnssonm
ID: 2595205
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.
0
 

Expert Comment

by:WaelEg
ID: 2611283
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.
0
 

Author Comment

by:pkimk
ID: 2611605
Thanks all.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question