I used AppWizard to generate code to read an MS Excel spreadsheet. When trying to select data from the spreadsheet, the following error occurs: (this is output from SQL trace):
[tt]CompanyInfo ff0-ff4 ENTER SQLExecDirectW
HSTMT 00FE18A8
WCHAR * 0x00DE68B8 [ 24] "SELECT * FROM companies$"
SDWORD 24
CompanyInfo ff0-ff4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 00FE18A8
WCHAR * 0x00DE68B8 [ 24] "SELECT * FROM companies$"
SDWORD 24
DIAG [37000] [Microsoft][ODBC Excel Driver] Syntax error in FROM clause. (-3506)
CompanyInfo ff0-ff4 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 00FE18A8
SQLSMALLINT 1
SQLWCHAR * 0x0012F448 (NYI)
SQLINTEGER * 0x0012F02C
SQLWCHAR * 0x0012F048 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F030
CompanyInfo ff0-ff4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 00FE18A8
SQLSMALLINT 1
SQLWCHAR * 0x0012F448 (NYI)
SQLINTEGER * 0x0012F02C (-3506)
SQLWCHAR * 0x0012F048 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F030 (59) [/tt]
Here is the wizard generated code:
Can someone help me determine the cause of the syntax error? Thank you.
[tt]CompanyInfo ff0-ff4 ENTER SQLExecDirectW
HSTMT 00FE18A8
WCHAR * 0x00DE68B8 [ 24] "SELECT * FROM companies$"
SDWORD 24
CompanyInfo ff0-ff4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 00FE18A8
WCHAR * 0x00DE68B8 [ 24] "SELECT * FROM companies$"
SDWORD 24
DIAG [37000] [Microsoft][ODBC Excel Driver] Syntax error in FROM clause. (-3506)
CompanyInfo ff0-ff4 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 00FE18A8
SQLSMALLINT 1
SQLWCHAR * 0x0012F448 (NYI)
SQLINTEGER * 0x0012F02C
SQLWCHAR * 0x0012F048 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F030
CompanyInfo ff0-ff4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 00FE18A8
SQLSMALLINT 1
SQLWCHAR * 0x0012F448 (NYI)
SQLINTEGER * 0x0012F02C (-3506)
SQLWCHAR * 0x0012F048 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F030 (59) [/tt]
Here is the wizard generated code:
Code:
HRESULT Open()
{
CDataSource db;
CSession session;
HRESULT hr;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, "companies");
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
hr = db.OpenWithServiceComponents("MSDASQL.1", &dbinit);
if (FAILED(hr))
return hr;
hr = session.Open(db);
if (FAILED(hr))
return hr;
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propset.AddProperty(DBPROP_IRowsetScroll, true);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE );
hr = CCommand<CAccessor<CCompanies> >::Open(session, "SELECT * FROM companies$", &propset);
if (FAILED(hr))
return hr;
return MoveNext();
}
};