Inserting ' into table from vb to access. (Urgent)

Hi,
i am trying to insert a ' from a visual basci query into a table in access but getting systax error becase while building query v use ' as text container like in follwoing line.
plz help me out.


insert into tblStudent values( 1, 'Sachin's wife'); //error

insert into tblStudent values( 1, 'Sachin s wife'); //no error



is there any way to solve this problem easy & reliable.
plz help with some gud example of quotation
must be simple if u  have more than one solutions plz welcome
thankx
tomerps1Asked:
Who is Participating?
 
nilesh_parmarConnect With a Mentor Commented:
try this function and call it  in query

 Public Function fixit(ByVal somestr As String) As String

        fixit = Replace(somestr, "'", "''")

    End Function

use it for ex

insert into tblStudent values( 1, fixit('Sachin's wife'));

i've worked with this and i m sure it will work.
0
 
venkateshwarrCommented:
insert into tblStudent values( 1, 'Sachin''s wife');
0
 
nilesh_parmarCommented:
its doing same as venkateshwarr wrote, anyway ..
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
crazycomputersCommented:
Unless I'm mistaken, that should be:

insert into tblStudent values( 1, 'Sachin\'s wife');
0
 
nilesh_parmarCommented:
sorry ,
it should be
 insert into tblStudent values(1,'fixit(Sachin's wife)');
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> insert into tblStudent values(1,'fixit(Sachin's wife)');

Guess it'll definitely not work. You're not calling the fixit () function. You're putting it as a constant 'fixit(...). Might give an error after Sachin'....

Perhaps what you can try is either what crazycomputers sugessted or if that doesn't work, use another dummy character instead of ' and while reading from the database, check if its that character and display ' in its place.
0
 
nilesh_parmarCommented:
i meant

insert into tblStudent values(1,'" & fixit(Sachin's wife) & "');
0
 
crazycomputersCommented:
The \ is standard for escaping special characters in SQL expressions, but we all know how M$ violates standards.  '' might work, but I'd put my money on \'
0
 
tomerps1Author Commented:
Thankx Nitesh Parmar
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.