A problem when use create excel file using Microsoft excel ODBC dr

[Guest]

Hi all,

I'm using Microsoft excel ODBC driver to create .xls file. The code looks
like this,

Foo()
{
CDatabase db;
CString openStr;


openStr.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s","MICROSOFT
EXCEL DRIVER (*.XLS)", "c:\\test.xls", "c:\\test.xls");

if (db.OpenEx(openStr,CDatabase::noOdbcDialog))
{
// Create table
openStr.Format ("CREATE TABLE %s (%s TEXT,%s TEXT,%s TEXT,%s TEXT)",
"testTable",
"Item1",
"Item2",
"Item3",
"Item4"
);
db.ExecuteSQL (openStr);

// Fill content
openStr.Format ("INSERT INTO testTable (Item1,Item2,Item3,Item4) VALUES
('%s','%s','%s',%s')",
"Value1",
"Value2",
"Value3",
"Value4",
"Value5"
);
db.ExecuteSQL (openStr);
}
return ;
}

After this function running, the "c:\test.xls" is created successfully and
everything looks OK except the values inputted ("ValueX") were all prefixed
by a '. It may because the "ValueX" was by a pair of ' in the SQL command.
But if I remove the ', the SQL then has syntax error.

Anyone knows how to remove the extra '? Thanks in advance.

 

[onedaywhen]

This behaviour is by design. It ensures Excel (and Jet) sees the value
as text. It should not cause you any problems so just try to forget
about it <g>.

Jamie.

--

 

[Guest]

It's a bad news.
Thanks anyway.

 

[onedaywhen]

It's a bad news.

Really? I'm genuinely interested: what problems is it causing you?
Thanks,
Jamie.

--

 

[onedaywhen]

As I thought. Silence speaks volumes. People think the ' will be a
problem but it never is.

Jamie.

--

 

[Guest]

Yes, customer hate this '.

 

[onedaywhen]

Yes, customer hate this '.

Users, eh? Say no more ;-)

Jamie.

--