More database problems

[poporacer]

Things are getting closer, but for some reason my SQL statments are not working properly. I am using SQLite. Not all of the data is getting put into the table.

code to create table:

void createDb()
{
    QSqlQuery query;
 
    query.exec("CREATE TABLE rider (id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "LName TEXT, FName TEXT, weight REAL, notes TEXT)"); //Table creates properly with all the fields
    query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Ryan', 'Villapoto',185)"); // Villapoto gets put into table as FName    
    query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Jeremy', 'McGrath',155)"); // Nothing gets added
}

void createDb()
{
    QSqlQuery query;

    query.exec("CREATE TABLE rider (id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "LName TEXT, FName TEXT, weight REAL, notes TEXT)"); //Table creates properly with all the fields
    query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Ryan', 'Villapoto',185)"); // Villapoto gets put into table as FName    
    query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Jeremy', 'McGrath',155)"); // Nothing gets added
}

To copy to clipboard, switch view to plain text mode 

I am sure it is something simple....but I can't find it

[ChrisW67]

The code posted above functions and produces a two row table for me.

Line 7: If Villapoto ends up in the FName column with this query then either something is very broken with your Sqlite engine or plugin, you are not looking at the same database as the program, or you mistyped your comment.
Line 9: Does the query return true or false (You really should get into the habit of looking at the return value). If false, then what does QSqlQuery::lastError() give you?

[poporacer]

here is the connection...I am sure the connection is working because I have the database created from the program and if the SQLite engine is broken (I am sure it came with Creator) how do I fix it?? I cut and pasted the code so no mistyping.
I did use qDebug() << query.lastError(); and it returned QSqlError(-1, "", "") .

bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("rider.dat");
    if (!db.open()) {
        QMessageBox::warning(0, QObject::tr("Database Error"),
                             db.lastError().text());
        return false;
    }
    return true;
}

bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("rider.dat");
    if (!db.open()) {
        QMessageBox::warning(0, QObject::tr("Database Error"),
                             db.lastError().text());
        return false;
    }
    return true;
}

To copy to clipboard, switch view to plain text mode 

I am not sure how to use QSqlQuery::lastError() I used qDebug() <<QSqlQuery::lastError(); and got an error...I used qDebug() <<QSqlQuery::lastError(query); and it still didn't work. I did include <QSqlError>.
Thanks for your help!

[ChrisW67]

It is infinitely more likely that the database is not what you think it is. If "rider.dat" already exists then it will be opened, otherwise it is created. If it already existed and the table rider already exists then your CREATE TABLE will fail leaving whatever structure was previously there. Depending on the structure this could cause your subsequent INSERTs to also fail, leaving whatever data was in the table still in the table. Delete rider.dat before you try again.

query.lastError() will return a QSqlError object that has some useful members. There's no point looking at it if the exec() indicated it succeeded though.

void createDb()
{
    QSqlQuery query;
    bool ok;
 
    ok = query.exec("CREATE TABLE rider (id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "LName TEXT, FName TEXT, weight REAL, notes TEXT)"); //Table creates properly with all the fields
    if (!ok) qWarn() << query.lastError().text();
    ok = query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Ryan', 'Villapoto',185)"); // Villapoto gets put into table as FName    
    if (!ok) qWarn() << query.lastError().text();
    ok = query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Jeremy', 'McGrath',155)"); // Nothing gets added
    if (!ok) qWarn() << query.lastError().text();
}

void createDb()
{
    QSqlQuery query;
    bool ok;

    ok = query.exec("CREATE TABLE rider (id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "LName TEXT, FName TEXT, weight REAL, notes TEXT)"); //Table creates properly with all the fields
    if (!ok) qWarn() << query.lastError().text();
    ok = query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Ryan', 'Villapoto',185)"); // Villapoto gets put into table as FName    
    if (!ok) qWarn() << query.lastError().text();
    ok = query.exec("INSERT INTO rider (FName, LName, weight)"
               "VALUES ('Jeremy', 'McGrath',155)"); // Nothing gets added
    if (!ok) qWarn() << query.lastError().text();
}

To copy to clipboard, switch view to plain text mode 

[poporacer]

I have been out of town for the last week. I tried your code and got a "qWarn was not declared in this scope. I included <QSqlError>. Where do I need to declare qWarn. I did get my SQL to work finally! I thank you for your help!

[ChrisW67]

#include <QDebug>

#include <QDebug>

To copy to clipboard, switch view to plain text mode