SetQuery INSERT

[gabor53]

I tried to fix the problem like this:

#include <QCoreApplication>
#include <QDebug>
#include <QtGui>
#include <QtCore>
#include <QtSql>
#include <QtDebug>
#include <QSqlQueryModel>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QSqlDatabase db;
    QSqlQuery query;

    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName ("C:/Programming/Qtsamples/Variabletodb/database.db");
    db.open ();

    if(!db.open ())
    {
        qDebug() << "The database is not open!";
    }
	else
    {
        qDebug() << "The database is open!";
    }

    QString ID = "213";
    QString Fname = "John";
    QString Lname = "Smith";

    query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");
    query.bindValue (0,ID);
    query.bindValue (1,Fname);
    query.bindValue (2,Lname);
    query.exec ();
    db.close ();


    return a.exec();
}

I got the following messages:
The database is open!
QSqlQuery::prepare: database not open

Of course nothing was written into the database. Please tell me what I did wrong at this time. Thank you for your help.

[kshegunov]

@gabor53
Hello,
You should use named bindings, like this:

query.bindValue(":ID", ID);
query.bindValue(":FirstName", Fname);
// ... and so on

Additionally, how does

query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");

even compile? you have two strings on two lines, and the function expects a single string ...

[jsulm]

@kshegunov This is valid syntax: compiler combines both string literals into one string.

[jsulm]

@gabor53 I think you should pass the database to the query:

QSqlQuery query(db);

[kshegunov]

@jsulm

This is valid syntax: compiler combines both string literals into one string.

It is possible, I have never seen it before though.

[mjsurette]

@gabor53
@jsulm is right, you need to pass the database to the query. I always do this after the database is open. This assumes of course that the database is already created and the table defined.

@kshegunov C++ and I believe C as well concatenates two or more string literals separated only by whitespace. This is quite useful for maintaining readability in long SQL queries.

[SGaist]

Hi,

@jsulm and @mjsurette Passing the database to QSqlQuery's constructor is only needed if you don't use the default connection which is not the case.

However, query is created before the database is added thus using an invalid connection . That's way there's a complaint about a closed database.

@kshegunov Indeed, the two strings are concatenated but here there's a space that will be missing in the current case.

What should also be done is check the return value of query.exec() and print the related error if it fails. Otherwise there's no way to have a real clue of what is going on.

[kshegunov]

@mjsurette @SGaist
Thanks for the clearing that up. I never knew this to be possible and I always have used the \ + newline approach (the way macros are broken down to multiple lines).

[gabor53]

Thanks for all your help.
I added

 qDebug()<< query.lastError ();

and I got the following message:
QSqlError("","Driver not loaded","Driver not loaded").

 qDebug()<< query.lastError ();

returns false.
Any idea what's wrong?
Thank you.

[gabor53]

The following code worked:

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QSqlDatabase db;


    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName ("C:/Programming/Qtsamples/Variabletodb/database.db");
    db.open ();
	QSqlQuery query(db);

    if(!db.open ())
    {
        qDebug() << "The database is not open!";
    }
	else
    {
        qDebug() << "The database is open!";
    }

    QString ID = "213";
    QString Fname = "John";
    QString Lname = "Smith";

    query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");
    query.bindValue (":ID",ID);
    query.bindValue (":FirstName",Fname);
    query.bindValue (":LastName",Lname);
    query.exec ();



//    qDebug() <<  query.exec();

//    qDebug()<< query.lastError ();

    db.close ();


    return a.exec();
}

Thank you for your help!