Create sqlite table is not work

[galaxy66]

I have a method that make a connection to SQLITE and work with database by conditions (insert data or create table in my database.)

When I call it in my code, at first open database correctly but can not create tables.

the SQLite said No such table

the SQLite said No such table

To copy to clipboard, switch view to plain text mode 

this is my method :

void SQL(string cmnd)
{
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//not dbConnection
    QString path = "DBAbsent";
    db.setDatabaseName(path);
    QSqlQuery q(db);
 
    if(!db.open())
    {
 
        printf("Error to open database");
    }
    else
    {
 
        if (cmnd=="createTable")
        {
            q.prepare("create table IF NOT EXISTS Personel (P_id int primary key, "
                                 "CardID varchar(50), "
                               "name varchar(50));");
 
            if(!q.exec())
        {
            qDebug()<<strerror(errno);
           qDebug()<<q.lastError(); // show this error : QSqlError("", "Unable to fetch row", "No query")
 
        }
 
            q.prepare("create table IF NOT EXISTS Absent "
                                 "(P_id int , "
                                 "DateTime varchar(50), "
                                 "key1 int,key2 int,key3 int,key4 int);");
            if(!q.exec())
                qDebug()<<strerror(errno);
 
             db.close();
             return;
 
        }
        else if (cmnd=="Register")
        {
            string card=ReadFromCard();
            printf("\nCard Accepted : %s\nEnter Your name: ",card.c_str());
            string name;
            int id;
            //string dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
            cin>>name;
            printf("\n Name Accepted.\nEnter Your Personel ID: ");
            cin>>id;
            q.prepare( "SELECT count(P_id) FROM Personel WHERE P_id =?;" );
            q.bindValue(0,id);
 
            if( !q.exec() )
            {
              printf("\nThe error occured from P_ID database");
              qDebug()<<strerror(errno);
              //ShowMainMenu();
              //return;
            }
 
            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                if (q.value(0).toInt()>0)
                {
                    printf("\nThis personel ID was repeated\n");
                    break;
                }
                else
                {
                    q.prepare("SELECT CardID FROM Personel WHERE CardID =?;");
                    q.bindValue(0,card.c_str());
                    q.exec();
 
                    while(q.next())
                    {
                        printf("\nThis card is not valid : it was repeated\n");
                        break;
                    }
                }
            }
 
            q.prepare("insert into Personel value(?,?,?);");
            q.bindValue(0,id);
            q.bindValue(1,card.c_str());
            q.bindValue(2,name.c_str());
            q.exec();
            printf("\nInsert Personel complete\n");
            db.close();
            return;
 
        }
        else
        {
            string card;
            card=cmnd;
            QString dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
 
            q.prepare("SELECT P_id FROM Personel WHERE CardID =?;");
            q.bindValue(0,card.c_str());
 
            if( !q.exec() )
            {
              printf("\nThe error occured from database418\n");
              ShowMainMenu();
 
            }
            string result=("\n    CARD NOT VALID =>repeated     %s\n");
            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                int P_id = q.value(0).toInt();
                q.prepare("insert into Absent(P_id,DateTime,key1,key2,key3,key4) value(?,?,1,0,0,0);");
                q.bindValue(0,P_id);
                q.bindValue(1,dt);
                q.exec();
                result=("\n********WELL COME **********%s\n");
            }
            printf("%s",result);
            db.close();
            return;
        }
 
    }
}

void SQL(string cmnd)
{

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//not dbConnection
    QString path = "DBAbsent";
    db.setDatabaseName(path);
    QSqlQuery q(db);

    if(!db.open())
    {

        printf("Error to open database");
    }
    else
    {

        if (cmnd=="createTable")
        {
            q.prepare("create table IF NOT EXISTS Personel (P_id int primary key, "
                                 "CardID varchar(50), "
                               "name varchar(50));");

            if(!q.exec())
        {
            qDebug()<<strerror(errno);
           qDebug()<<q.lastError(); // show this error : QSqlError("", "Unable to fetch row", "No query")

        }

            q.prepare("create table IF NOT EXISTS Absent "
                                 "(P_id int , "
                                 "DateTime varchar(50), "
                                 "key1 int,key2 int,key3 int,key4 int);");
            if(!q.exec())
                qDebug()<<strerror(errno);

             db.close();
             return;

        }
        else if (cmnd=="Register")
        {
            string card=ReadFromCard();
            printf("\nCard Accepted : %s\nEnter Your name: ",card.c_str());
            string name;
            int id;
            //string dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
            cin>>name;
            printf("\n Name Accepted.\nEnter Your Personel ID: ");
            cin>>id;
            q.prepare( "SELECT count(P_id) FROM Personel WHERE P_id =?;" );
            q.bindValue(0,id);

            if( !q.exec() )
            {
              printf("\nThe error occured from P_ID database");
              qDebug()<<strerror(errno);
              //ShowMainMenu();
              //return;
            }

            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                if (q.value(0).toInt()>0)
                {
                    printf("\nThis personel ID was repeated\n");
                    break;
                }
                else
                {
                    q.prepare("SELECT CardID FROM Personel WHERE CardID =?;");
                    q.bindValue(0,card.c_str());
                    q.exec();

                    while(q.next())
                    {
                        printf("\nThis card is not valid : it was repeated\n");
                        break;
                    }
                }
            }

            q.prepare("insert into Personel value(?,?,?);");
            q.bindValue(0,id);
            q.bindValue(1,card.c_str());
            q.bindValue(2,name.c_str());
            q.exec();
            printf("\nInsert Personel complete\n");
            db.close();
            return;

        }
        else
        {
            string card;
            card=cmnd;
            QString dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");

            q.prepare("SELECT P_id FROM Personel WHERE CardID =?;");
            q.bindValue(0,card.c_str());

            if( !q.exec() )
            {
              printf("\nThe error occured from database418\n");
              ShowMainMenu();

            }
            string result=("\n    CARD NOT VALID =>repeated     %s\n");
            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                int P_id = q.value(0).toInt();
                q.prepare("insert into Absent(P_id,DateTime,key1,key2,key3,key4) value(?,?,1,0,0,0);");
                q.bindValue(0,P_id);
                q.bindValue(1,dt);
                q.exec();
                result=("\n********WELL COME **********%s\n");
            }
            printf("%s",result);
            db.close();
            return;
        }

    }
}

To copy to clipboard, switch view to plain text mode 

And this is my output :

0 - Press 0 to Back
    1 - Press 1 to Register the card
    1
    //the below lines when I want to select something from both tables , occurred
    No such file or directory
    QSqlError("", "Unable to fetch row", "No query")
 
    No such file or directory  
    //the below line is when the if(cmnd="Register") {..} block is running , occurred.
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

0 - Press 0 to Back
    1 - Press 1 to Register the card
    1
    //the below lines when I want to select something from both tables , occurred
    No such file or directory
    QSqlError("", "Unable to fetch row", "No query")

    No such file or directory  
    //the below line is when the if(cmnd="Register") {..} block is running , occurred.
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

To copy to clipboard, switch view to plain text mode 

c++ sqlite

[monillo]

Hi:

Try this

q.prepare("create table IF NOT EXISTS 'Personel' (P_id int primary key, "
               "'CardID' varchar(50), "
               "'name' varchar(50))");

q.prepare("create table IF NOT EXISTS 'Personel' (P_id int primary key, "
               "'CardID' varchar(50), "
               "'name' varchar(50))");

To copy to clipboard, switch view to plain text mode 

Best regards.

[galaxy66]

It solved. Just do these :
1 - As @Tony said: QSqlQuery should defined after opening database.
2 - q.prepare just uses for SQL query contains bindvalue(), like select() or insert() .