How to insert Qxlsx data into sqlite database table?

jsulm

@Joshika_Namani Please format your code properly.
What exactly does not work?
You do not have any error handling (hint: QSqlQuery::exec returns a bool and there is https://doc.qt.io/qt-5.15/qsqlquery.html#lastError).

Joshika_Namani

Thanks for your reply, It's executing with out any errors but table is empty.

jsulm

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

It's executing with out any errors

So, you see "inserted" printed (from qInfo()<<"inserted")?

Joshika_Namani

@jsulm
yes

jsulm

@Joshika_Namani How did you check that the table is empty?
I also don't see where you're closing your database.

Joshika_Namani

I have used sqlite browser its gui database.
Yes, i didn't close the database.

JonB

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

    if(!query.exec())
    {
        qInfo()<<"inserted";
    }

Somebody here is crazy. You said

Thanks for your reply, It's executing with out any errors but table is empty.

So, you see "inserted" printed (from qInfo()<<"inserted")?

yes

You are printing inserted if query.exec() fails. Why??

insert into Dataset(AT) values(?)

I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it? In any case you have QSqlQuery::lastError(). Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.

Separate issue when you have that working:

query.addBindValue(vec[1]);

You always want to re-insert vec[1], not vec[i], right?

jsulm

@JonB said in How to insert Qxlsx data into sqlite database table?:

Somebody here is crazy

Oh, I guess I'm blind or crazy or both :-D

JonB

@jsulm I didn't mean you... :)

jsulm

@JonB I know :-) But I really should have seen the wrong if condition.

Joshika_Namani

@JonB
Sorry for that i modified everything here I'm getting an error like QSqlError("", "", "") by using lastError() method.

JonB

@Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

i modified everything here

In that case show new code! :)

Joshika_Namani

@JonB
QXlsx::Document xlsx("Test.xlsx");
QSqlDatabase sqlite;
sqlite = QSqlDatabase::addDatabase("QSQLITE");

sqlite.setDatabaseName("/home/Qt Program/DB/Database");
Cell* cell;
int col=1;

std::vector<QVariant> vec;

if(xlsx.load())
{
for(int row=1;row<=20;row++)
{

    cell=xlsx.cellAt(row,col);
    QVariant var=cell->readValue();
    vec.push_back(var);
   }
for(int i=0;i<=20;i++)
  {
        qInfo()<<vec[i];
   }
if(sqlite.open())
    {
 
    QSqlQuery query;
    for(int i=0;i<vec.size();i++)
    {
        
        query.prepare("insert into Data(AT) values(?)");
        query.addBindValue(vec[i]);
        if(query.exec())
        {
            qInfo()<<"inserted";
        }
        else
        {
            qInfo()<<sqlite.lastError();
        }
    }


    }
else
{
    qInfo()<<"not opened";
}

}
}

JonB

@Joshika_Namani
You have not answered or acted on what I already wrote. Please don't ignore suggestions and make responders type the same thing again. I already wrote:

insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?
Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.
Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says

Failures that occur in conjunction with an individual query are reported by QSqlQuery::lastError().

which you should be checking instead/as well.

Please read the documentation for the functions you call, and check all errors before asking us, it saves you & us time.

Joshika_Namani

@JonB
sorry for that,

insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?
Yes, it is valid statement in sqlite.
Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.
Returns true if the query is prepared successfully; otherwise returns false.
Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says
yes, I got error. like QSqlError("","","");

I checked again there was a problem in database but it solved thanks alot.
Now, I'm able to insert data into database from QXlsx file.