Access Insert statement fails due to apostrophe...

[martindavey]

Hi,
I'm using ADO to insert into an Access database.

The following works in Access itself:
insert into [myTable] ([First Name],[Surname])
values ("Fred","O'Brien"

In my program I use:
wsprintf( SQL, "insert into [myTable] "
"([First Name],[Surname]) values ('%s','%s');",
strFirstName, strSurname );
...

That works fine, but if the surname contains an apostrophe ( e.g. O'Brien ) then I get syntax error (missing operator).

If I use quotes instead of apostrophes:
wsprintf( SQL, "insert into [myTable] "
"([First Name],[Surname]) values (\"%s\",\"%s\"",
strFirstName, strSurname );

I get Too few parameters (expected 1).

Any idea (I don't want to remove the apostrophe).

Cheers :O)

 

[Cagliostro]

wsprintf( SQL, "insert into [myTable](paramname1, paramname2) "
"([First Name],[Surname]) values (\"%s\",\"%s\"",
strFirstName, strSurname );
Ion Filipski

filipski@excite.com

 

[martindavey]

Are you sure? That looks completely wrong to me.

I've already specified the column names within parentheses, what are paramname1, etc.?

 

[IonelBurtan]

There is an ESCAPE part in the INSERT Sql sintax if I am not wrong. Try to use that one.

Anyway, the problem is not one that comes from the interface(front-end). You will have this problem with another characters (like !@#%*) too.

Hope this helps,

s-)

Blessed is he who in the name of justice and goodwill, sheperds the weak through the valley of darkness...

http://www20.brinkster.com/ionelb

 

[Cagliostro]

By the way, there is a place where you have two double quotas.
wsprintf( SQL,
"insert into [myTable]""([First Name],[Surname]) values (\"%s\",\"%s\"", strFirstName, strSurname );
what are they for? Ion Filipski

filipski@excite.com

 

[martindavey]

I had no problem inserting a row with:
Surname == O!@#%*Brien

I don't know how to escape the apostrophe for Access, it's no good using \', Access doesn't recognise that.

Basically, I have a field contained within apostrophes that contains an apostrophe - I need to know how to get that apostrophe accepted by Access.

Getting desperate ;O)

 

[martindavey]

Ion,
The double quotes appear on separate lines - they allow you to span strings over multiple lines - they are removed by the compiler.

 

[Cagliostro]

Ok, I understood the problem.
You have names like Oneil's...
My idea is to use prepared SQL's.
So, for example in ODCB API is SQLPrepare(..yoursqlstring..);
your SQLString should be:
"insert into [myTable]([First Name],[Surname]) values (?,?)"
the next step is to bind parameters, see the two ? will be interpreted as parameters bound by you. In ODBC API is SQLBindParam. So bind strFirstName as the first argument for SQL and strSurname as second.
The next step (in ODBC API) SQLExec. So till you close statement you can execute this prepared statement many times with changing strings (with not reallocating them). In other APIs the methods are quiet the same, focus attention on binding parameters. Ion Filipski

filipski@excite.com