?
Solved

Insert Into MSACCESS

Posted on 2006-05-02
12
Medium Priority
?
229 Views
Last Modified: 2013-11-20
I have a table with only 2 fields (id=autonumber, sql=255 text)

I want to do an insert but access gives me a syntax error:

CString statement = "INSERT INTO MYTABLE(SQL) VALUES('sql statement with 'single qoutes'...')";

I think it is complaining with what's in the values where it is containing single qoutes.  What is the correct syntax when a field value contains single qoutes?

thanks,
0
Comment
Question by:ramrocket
  • 3
  • 3
  • 3
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16586945
>What is the correct syntax when a field value contains single qoutes?
INSERT INTO MYTABLE(Field1, Field2) VALUES 'Some text value', 'Another text value')

If numeric, do not use single quotes '.  If dates, use pound signs #1/1/2005#
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16586973
If you need to execute an entire SQL Statement, in Access VBA (read:  I'm not familiar with MFC) that would go like this:

'Uses ADO

Dim cn as Adodb.Connection
Set cn = New ADODB.Connection

cn.Open 'See http://www.connectionstrings.com to build a connection string to another Access Db.

Dim sSQL as String
sSQL = "Your SQL String"

cn.Execute sSQL

Hope this helps.
-Jim
0
 

Author Comment

by:ramrocket
ID: 16587135
I am saving an sql statement into this table.  I just don't know what the insert syntax when a text field value contains single qoutes.
0
Industry Leaders: 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!

 
LVL 66

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 16587230
>insert syntax when a text field value contains single qoutes.
Try it with double-single-quotes for every single quote.

sSQL = Replace(sSQL, "'", "''")  
Last one is double-quote, single-quote, single-quote, double-quote
0
 

Author Comment

by:ramrocket
ID: 16587498
I tried your suggestion, I still get thesame error

CString str1 = L"'";
CString str2 = L"''";
statement .Replace(str1, str2);  
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16588192
Lets say you have
long id=12345;
CString sql="ABCDEFGH";

CString statement = "INSERT INTO MYTABLE(SQL) VALUES(id,'"+sql+"')";

See carefully '"+sql+"' <=== is combination of  single and double quotes.

-MAHESH
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16588282
Otherwise why not to use just CString::Format

e.g.

long id=12345;
CString sql="ABCDEFGH";

CString statement;
statment.Format( "INSERT INTO MYTABLE(SQL) VALUES(%d,'%s')",id,sql);

-MAHESH
0
 

Author Comment

by:ramrocket
ID: 16590670
I solved my problem.  replace ALL single qoutes with double qoutes.
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16883275
method I suggested using CString works.

-MAHESH
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

850 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