Basic QSqlite

Julian

I'm trying to do a database with SQLite, but I get the next debug messagge: QSqlError("", "Unable to fetch row", "No query")

this it's the code:

*******************************************************
 void MainWindow::crearTablaUsuario()
{
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS semaforos("
                    "id INTERGER PRIMARY KEY AUTOINCREMENT"
                    "interseccion VARCHAR(100),"
                    "marca VARCHAR(50),"
                    "modelo VARCHAR(100)"




                    ");");
QSqlQuery crear(db);
crear.prepare(consulta);

if(crear.exec())
{

}
else
{
    qDebug()<<"ERROR!"<<crear.lastError();
}

}

*******************************************************

and

void MainWindow::insertarInterseccion()
{
    QString agregar;
    agregar.append(  "INSERT INTO semaforos("
                    "interseccion,"
                    "marca,"
                    "descripcion,"
                    "modelo"")"
                    "VALUES("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_Marca->text()+"',"
                    "'"+ui->lineEdit_modelo->text()+"'"


                    ");"      );


    QSqlQuery insertar(db);
    insertar.prepare(agregar);

[edit: fixed coding tags SGaist]

SGaist

Hi,

Your create query looks wrong, there's at least a missing comma after AUTOINCREMENT and INTEGER is misspelled.

Julian

thank. I fixed it but there is still the error. It's in the seccion code of insert into semaforo. I can't put data to the table.

Julian

@Julian here it's the problem

  agregar.append(  "INSERT INTO semaforos("
                    "interseccion,"
                    "marca,"
                    "descripcion,"
                    "modelo"")"
                    "VALUES("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_Marca->text()+"',"
                    "'"+ui->lineEdit_modelo->text()+"'"
                    ");"      );

    QSqlQuery insertar(db);
    insertar.prepare(agregar);

[edit: added coding tags SGaist]

SGaist

Please use coding tags. The </> button on far right inserts them for you.

There's likely a space missing between ) and VALUES and you are giving 4 fields to your insert statement but provide only 3 values.

Julian

There's likely a space missing between ) and VALUES

Where?

 QString agregar;
    agregar.append(  "INSERT INTO semaforos("
                    "interseccion,"
                    "marca,"
                    "descripcion,"
                    "modelo"") "
                    "VALUES ("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_Marca->text()+"',"
                    "'"+ui->lineEdit_modelo->text()+"'"
                    ");"      );


    QSqlQuery insertar(db);
    insertar.prepare(agregar);

its' still do not work
as there is id whit autoincrement, I thought it was not necesary.

SGaist

From a quick view the spaces now looks correct however you have descricion in your field list that doesn't match anything in you table at least based on your create statement.

Julian

 QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS semaforos("
                    "a VARCHAR(100),"
                    "b VARCHAR(50),"
                    "c VARCHAR(100)"




                    ");");
QSqlQuery crear(db);
crear.prepare(consulta);

if(crear.exec())
{
    
}
else
{
    qDebug()<<"ERROR!"<<crear.lastError();
}

}

QString agregar;
    agregar.append(  "INSERT INTO semaforos("
                    "a,"
                    "b,"
                    "c"") "
                    "VALUES("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_Marca->text()+"',"
                    "'"+ui->lineEdit_modelo->text()+"'"
                    ");"      );


    QSqlQuery insertar(db);
    insertar.prepare(agregar);

  if(insertar.exec())
    {
      
    }
                  else
        {
                  qDebug()<<"wrong..."<<insertar.lastError();
    }

and this it's what I get:

wrong... QSqlError("", "Unable to fetch row", "No query")

SGaist

You should test the value returned by prepare. And also the value returned by QSqlQuery::executedQuery.

One silly question though, did you successfully open the database before calling request on it ?

the_

@Julian
to add @SGaist's explanation:

never trust user input and always use prepared statements when directly writing user input into a database.
Have a quick look at the QSqlQuery documentation for how to prepare SQL statements

Julian

Well, Thanks both.

I try whit QSqlQuerry::executedQuery() and what Debug return it's : "INSERT INTO semaforos(a,b,c) VALUES('asdf','asdf','asdf');"

and "asdf" are the String which I put on the lineEdits so ¿What happening?

This it's the code:

 QString agregar;
    agregar.append(  "INSERT INTO semaforos("
                    "a,"
                    "b,"
                    "c"") "
                    "VALUES("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_Marca->text()+"',"
                    "'"+ui->lineEdit_modelo->text()+"'"
                    ");"      );


    QSqlQuery insertar(db);  //where db it's the database
    insertar.prepare(agregar);

    if(insertar.exec())
    {
  
    }
                  else
        {
                  qDebug()<<insertar.executedQuery();
    }
}

Now it's working (I do not know why). Let me see what happend.

the_

@Julian

What did you change in your code (maybe above the part you posted here)?

Julian

What did you change in your code (maybe above the part you posted here)?

Thank! it' work now. I did not know what a did, I just fit the code and take care about you said with executedQuery() .

But instead of using names to variables I used letters, so I could corroborate better

herer it's the initialization table.

 QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS semaforos("
                    "a VARCHAR(100),"
                    "b VARCHAR(50),"
                    "c VARCHAR(50),"
                    "d VARCHAR(50),"
                    "e VARCHAR(50),"
                    "f VARCHAR(50),"
                    "g VARCHAR(50),"
                    "h VARCHAR(50),"
                    "i VARCHAR(50),"
                    "j VARCHAR(100)"




                    ");");
QSqlQuery crear(db);
crear.prepare(consulta);

if(crear.exec())
{

}
else
{
    qDebug()<<"ERROR!"<<crear.lastError();
}

}



   QString agregar;
    agregar.append(  "INSERT INTO semaforos("
                    "a,"
                    "b,"
                    "c,"
                    "d,"
                    "e,"
                    "f,"
                    "g,"
                    "h,"
                    "i,"
                    "j"") "
                    "VALUES("
                    "'"+ui->lineEdit_interseccion->text()+"',"
                    "'"+ui->lineEdit_marca->text()+"',"
                     "'"+ui->lineEdit_caracteristica->text()+"',"
                     "'"+ui->lineEdit_modelo->text()+"',"
                     "'"+ui->lineEdit_tiempo->text()+"',"
                     "'"+ui->lineEdit_peatonal->text()+"',"
                     "'"+ui->lineEdit_tecnologia->text()+"',"
                     "'"+ui->lineEdit_cantidad->text()+"',"
                     "'"+ui->lineEdit_descripcion->text()+"',"
                    "'"+ui->textEdit->toPlainText()+"'"
                    ");"      );

  QSqlQuery insertar(db);
  insertar.prepare(agregar);


    if(insertar.exec())
    {
        
    }
                  else
        {
                  qDebug()<<"Error..."<<insertar.executedQuery();
    }
}

SGaist

So you have everything working now with your original database ?