QODBC3:Unable to connect

[MartinChan]

Used to ask the question of ODBC driver question but it came to a new question
I just want to connect a .xls file to the QT,then read the statics in it,but I found I can't connect the file and the SqlError always told me:QODBC3:Unable to connect like this.

Here is my code in main.cpp:

#include <QCoreApplication>
#include <QSql>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>
#include <QString>
#include <QStringList>
#include <QSqlRecord>
#include <QSqlError>
#include <QTextStream>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    qDebug() << "Available drivers:";
    QStringList drivers = QSqlDatabase::drivers();
    foreach(QString driver, drivers)
    qDebug() << "\t" << driver;
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    qDebug() << "ODBC driver valid?" << db.isValid();
    QString out_str;
    QTextStream out(stdout);
    QSqlError err;

    db.setDatabaseName("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\\grade.xls;DefaultDir=D:\\;");
    bool ok=db.open();
    if (ok){
        QStringList tables;
        QString tabName,sqlString;
        tables=db.tables(QSql::Tables);
        for (int i=0;i<tables.size();i++){
            tabName=tables.at(i);
            qDebug()<<tabName;
            sqlString="select * from"+tabName;
            QSqlQuery q(sqlString);
            QSqlRecord rec=q.record();
            int fieldCount=rec.count();
            qDebug()<<"Number of columns:"<<fieldCount;
            int ii;
            QString fieldName;
            for(ii=0;ii<fieldCount;ii++){
                fieldName=rec.fieldName(i);
                qDebug()<<fieldName<<"/t";
            }
            out<<endl;
            while(q.next()){
                for(int i=0;i<fieldCount;i++){
                    fieldName=rec.fieldName(i);
                    out<<fieldName<<"\t";
                }
                out<<endl;
            }
        }
    }
    else{
        out_str="open failure\n";
        out<<out_str<<endl;
        err=db.lastError();
        out_str=err.driverText();
        out<<out_str<<endl;
    }
    return a.exec();
}

As you can see ,I can really sure I have install the ODBC driver but I just can't connect the file.@mrjj for help

[mrjj]

Hi
Is the Excel ODBC driver listed in
http://windows.microsoft.com/en-us/windows/using-odbc-data-source-administrator#1TC=windows-7

ODBC Data Source Administrator ?

Can you use D:\grade.xls as data source in excel via odbc ?

Also
Here, it can open but
tables=db.tables(QSql::Tables); << this returns zero!
for (int i=0;i<tables.size();i++){ << so this never runs

[mrjj]

Hi
2 things
You must define a named ranged in the excel sheet
http://www.contextures.com/xlNames01.html

also, u need a space in your select.
sqlString = "select * from(SPACE)" + tabName;

[MartinChan]

@mrjj Thx for your advice and until now I can't link successfully,I will try and if it still can't work I have to use QAxobject to do the query.

I doubted the unsuccessful connection is due to the bit version of my system and the software(My windows10 is 64bit,the Microsoft Excel 2013 is 64 bit,but the Qt I installed is 32 bit)And I don't know the difference between ODBC3 and ODBC,is ODBC3 representing the 32 bit version of ODBC??(Because you told me that you have successfully use the db.open() but in my PC it still can't work)

Here is my ODBC setting,I have try to install any options about Excel...

[mrjj]

@MartinChan

It does look like mine.

You can test the drivers from excel. Try to use
the grade.xls as data source.

That must work before it can work in Qt.

[MartinChan]

This post is deleted!