QODBC3:Unable to connect



  • 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


  • Qt Champions 2016

    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


  • Qt Champions 2016

    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;



  • @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...


  • Qt Champions 2016

    @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.



  • This post is deleted!

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.