asked on

Insert Into MSACCESS

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,

Jim Horn

>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#

Jim Horn

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

ramrocket

ASKER

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ramrocket

ASKER

I tried your suggestion, I still get thesame error

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

mahesh1402

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

mahesh1402

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

ramrocket

ASKER

I solved my problem. replace ALL single qoutes with double qoutes.

mahesh1402

method I suggested using CString works.

-MAHESH