PDA

View Full Version : Create sqlite table is not work



galaxy66
1st March 2016, 09:03
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

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;
}

}
}

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.

c++ sqlite

monillo
1st March 2016, 11:41
Hi:

Try this


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

Best regards.

galaxy66
1st March 2016, 12:14
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() .