DELETE FROM DATABASE



  • Hi, I want to delete a row from database in a qt project and I don't know how I can do it.

    I want to delete the row when I mark whatever items of the row, I mean we can supose that we have in my data base name surname and birthday so, when I mark with the cursor the birthday and I press the boton "delete" this row have to be delete from the database.

    Could someone help me, please?

    Thank you so much!

    i dont put any code because I don't know how I have to do it...



  • Hi,

    @patcs

    QSqlQuery query;
    query.prepare("DELETE FROM tablename WHERE birthday = ?");
    query.addBindValue(birthdayvalue);
    query.exec();

    So as u mentioned u need to select birthday value, get the birthday value and then when click on button call the above code to delete the row of the value specified.

    Thanks,



  • Hi, @Pradeep-Kumar

    the problem here is that birthday isn't a ID, I mean, it could be more than one birthday with the same date. Then, I need delete the row where I clicked in datatable.



  • In that case u need to have a unique id so based on that id and birthday date, u can delete the row.

    Thanks,



  • @Pradeep-Kumar I had a id but it doesn't appear in the table because it isn't necessary for the user.

    void MainWindow::createUserTable()
    {
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS usuarios("
    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "dni VARCHAR(100),"
    "nombre VARCHAR(100),"
    "apellido VARCHAR(100),"
    "apellido2 VARCHAR(100),"
    "edad INTEGER NOT NULL,"
    "notas VARCHAR(300),"
    "estres INTEGER NOT NULL,"
    "valoracion VARCHAR(20),"
    "duracion VARCHAR(20),"
    "elementos VARCHAR(30),"
    "fecha VARCHAR(20),"
    "hora VARCHAR(20)"
    ");");

    QSqlQuery crear;
    crear.prepare(consulta);
    
    if(crear.exec())
    {
        qDebug()<<"La tabla USUARIO existe o se ha creado correctamente.";
    
    }else{
        qDebug()<<"La tabla USUARIO NO se ha creado correctamente.";
        qDebug()<<"ERROR!"<<crear.lastError();
    
    }
    

    could use this id to do the delete?
    how would it be?
    thanks,

    }



  • Then u can use both the id and birthday date in query, so only the specific row, matching the id and birthday will get deleted.

    can use multiple conditions in the query to delete the record.

    QSqlQuery query;
    query.prepare("DELETE FROM tablename WHERE birthday = ? and id=?");
    query.addBindValue(birthdayvalue);
    query.addBindValue(id);
    query.exec();

    Thanks,



  • @Pradeep-Kumar
    I don't think it is necessary to add 'birthday' column in 'where' clause - just 'id', because @patcs wants to delete current row from the table no matter which column is selected. The question is how to get 'id' value for current row if it is not displayed. This depends on what type of widget he use for visualization - QTableWidget or QTableView.



  • @Stoyan it is QTableWidget
    then how I have to do it?

    thanksss!



  • @patcs
    QTableWidget is not very convenient for connections to database, because you have to manage everything yourself - read data from database to a structure (list, array) and then populate every column of QTableWidget.
    If you are using QTableWidget then you already have some structure with data from the database. You have to add to QTableWidget also column 'id' as first for example. Then you can use

    table.setColumnHidden(0, true);
    

    to hide it, so it is not visible to users.
    To get value for selected row (which can be different from current row) you can use something like this:

    id = table.item(row(table.selectedItems().at(0)), 0);
    

    And beside delete of the row in database you have to delete same row from QTableWidget.



  • @Stoyan i'm trying but it say me that id was not declared in this scope. This is because I have two classes in one of them I can insert the datas and the other classes I can see the database, so I don't know how use this variable in other class.



  • @patcs
    The name of variable 'id' I used was for sample. In your code it may have another name.
    Can you show some code example?
    Probably you have to add a new variable in one or more classes for the column 'id' from the database.



  • @Stoyan Ok, this is the class where we can see the database:

    void BaseDatosScreen::dataShow()
    {
    QString consultation;
    consultation.append("SELECT *FROM usuarios");
    QSqlQuery consultar;
    consultar.prepare(consultation);

    if(consultar.exec())
    {
        qDebug()<<"Se ha consultado correctamente.";
    
    }else{
        qDebug()<<"NO se ha consultado correctamente.";
        qDebug()<<"ERROR!"<<consultar.lastError();
    
    }
    
    
    int fila = 0;
    ui->dataTable->setRowCount(0);
    
    while(consultar.next()){
    
        ui->dataTable->insertRow(fila);
        ui->dataTable->setColumnHidden(0,true);
        ui->dataTable->setItem(fila, 1, new QTableWidgetItem(consultar.value(1).toByteArray().constData()));
        ui->dataTable->setItem(fila, 2, new QTableWidgetItem(consultar.value(2).toByteArray().constData()));
        ui->dataTable->setItem(fila, 3, new QTableWidgetItem(consultar.value(3).toByteArray().constData()));
        ui->dataTable->setItem(fila, 4, new QTableWidgetItem(consultar.value(4).toByteArray().constData()));
        ui->dataTable->setItem(fila, 5, new QTableWidgetItem(consultar.value(5).toByteArray().constData()));
        ui->dataTable->setItem(fila, 6, new QTableWidgetItem(consultar.value(6).toByteArray().constData()));
        ui->dataTable->setItem(fila, 7, new QTableWidgetItem(consultar.value(7).toByteArray().constData()));
        ui->dataTable->setItem(fila, 8, new QTableWidgetItem(consultar.value(8).toByteArray().constData()));
        ui->dataTable->setItem(fila, 9, new QTableWidgetItem(consultar.value(9).toByteArray().constData()));
        ui->dataTable->setItem(fila, 10, new QTableWidgetItem(consultar.value(10).toByteArray().constData()));
        ui->dataTable->setItem(fila, 11, new QTableWidgetItem(consultar.value(11).toByteArray().constData()));
        ui->dataTable->setItem(fila, 12, new QTableWidgetItem(consultar.value(12).toByteArray().constData()));
    
        fila++;
    
    }
    

    }

    void BaseDatosScreen::on_pushButton_BSeleccion_clicked()
    {

    QMessageBox::StandardButton reply;
    reply= QMessageBox::question(this,tr("Borrar"),tr("¿Está seguro de que quiere eliminar la sesión?"),QMessageBox::Yes | QMessageBox::No);
    
    
    
    if(reply == QMessageBox::Yes){
        ui->dataTable->removeRow(f);
        QSqlQuery query;
        query.prepare("DELETE FROM usuarios WHERE id = f");
    
    
        if(query.exec())
        {
            qDebug()<<"Se ha borrado correctamente.";
    
        }else{
            qDebug()<<"NO se ha borrado correctamente.";
            qDebug()<<"ERROR!"<<query.lastError();
    
        }
    
    }
    

    And the code of the other class where I declare every variables is:

    oid MainWindow::createUserTable()
    {
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS usuarios("
    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "dni VARCHAR(100),"
    "nombre VARCHAR(100),"
    "apellido VARCHAR(100),"
    "apellido2 VARCHAR(100),"
    "edad INTEGER NOT NULL,"
    "notas VARCHAR(300),"
    "estres INTEGER NOT NULL,"
    "valoracion VARCHAR(20),"
    "duracion VARCHAR(20),"
    "elementos VARCHAR(30),"
    "fecha VARCHAR(20),"
    "hora VARCHAR(20)"
    ");");

    QSqlQuery crear;
    crear.prepare(consulta);
    
    if(crear.exec())
    {
        qDebug()<<"La tabla USUARIO existe o se ha creado correctamente.";
    
    }else{
        qDebug()<<"La tabla USUARIO NO se ha creado correctamente.";
        qDebug()<<"ERROR!"<<crear.lastError();
    
    }
    

    }
    QString valoracion_sesion = "" ;

    void MainWindow::insertUser()
    {
    QString dni_usuario = ui->lineEditDNI->text();
    QString nombre_usuario = ui->lineEditNombre->text();
    QString apellido_usuario = ui->lineEditPrimerApellido->text();
    QString apellido2_usuario = ui->lineEditSegundoApellido->text();
    QString edad_usuario = ui->lineEditEdad->text();
    QString notas_usuario = ui->textEdit->toPlainText();
    int estres_usuario = ui->horizontalSlider->value();
    QString fecha_sesion = ui->dateEdit->text();
    QString hora_sesion = ui->timeEdit->text();

    QString duracion_sesion = "";
    if(ui->radioButton->isChecked()){
        duracion_sesion = ui->radioButton->text();
    }else if(ui->radioButton_2->isChecked()){
        duracion_sesion = ui->radioButton_2->text();
    }else if(ui->radioButton_3->isChecked()){
        duracion_sesion = ui->radioButton_3->text();
    }
    
    QString elementos_sesion = "";
    if(ui->checkBox->isChecked() && ui->checkBox_2->isChecked() && ui->checkBox_3->isChecked()){
        elementos_sesion = "Luces, Colores, Sonidos";
    
    }else if(ui->checkBox->isChecked() && ui->checkBox_2->isChecked()){
        elementos_sesion = "Luces, Colores";
    
    }else if(ui->checkBox->isChecked() && ui->checkBox_3->isChecked()){
        elementos_sesion = "Luces, Sonidos";
    
    }else if(ui->checkBox_2->isChecked() && ui->checkBox_3->isChecked()){
        elementos_sesion = "Colores, Sonidos";
    
    }else if(ui->checkBox->isChecked()){
        elementos_sesion = "Luces";
    
    }else if(ui->checkBox_2->isChecked()){
        elementos_sesion = "Colores";
    
    }else if(ui->checkBox_3->isChecked()){
        elementos_sesion = "Sonidos";
    
    }
    
    
    
    
    
    QString consulta;
    
    consulta.append("INSERT INTO usuarios(id, dni, nombre, apellido, apellido2,edad, notas, estres, valoracion, duracion, elementos, fecha, hora)"
                        "values(:id, :dni, :nombre, :apellido, :apellido2, :edad , :notas, :estres , :valoracion, :duracion, :elementos, :fecha, :hora);");
    
    QSqlQuery insertar;
    insertar.prepare(consulta);
    
    insertar.bindValue(":dni", dni_usuario);
    insertar.bindValue(":nombre", nombre_usuario);
    insertar.bindValue(":apellido", apellido_usuario);
    insertar.bindValue(":apellido2", apellido2_usuario);
    insertar.bindValue(":edad", edad_usuario);
    insertar.bindValue(":notas", notas_usuario);
    insertar.bindValue(":estres", estres_usuario);
    insertar.bindValue(":valoracion", valoracion_sesion);
    insertar.bindValue(":duracion", duracion_sesion);
    insertar.bindValue(":elementos", elementos_sesion);
    insertar.bindValue(":fecha", fecha_sesion);
    insertar.bindValue(":hora", hora_sesion);
    
    
    if(insertar.exec())
    {
        qDebug()<<"El USUARIO se ha insertado correctamente.";
    
    }else{
        qDebug()<<"El USUARIO NO se ha insertado correctamente.";
        qDebug()<<"ERROR!"<<insertar.lastError();
    
    }
    

    }



  • @patcs
    Instead this statement:

    consultation.append("SELECT *FROM usuarios");
    

    it is better to explicitly use column names to be sure you get them in right order:

    consultation.append("SELECT id, dni, nombre, apellido, apellido2,edad, notas, estres, valoracion, duracion, elementos, fecha, hora FROM usuarios");
    

    I don't see how you get value for f in this statement:

    ui->dataTable->removeRow(f);
    

    but definitely it is not "row number in dataTable" and "id" at the same time.
    Before this row you have to use something like:

    int rownum = row(ui->dataTable->selectedItems().at(0));
    int id = ui->dataTable->item(rownum, 0).data().toInt();
    QSqlQuery query;
    query.prepare("DELETE FROM usuarios WHERE id = ?");
    query.addBindValue(id);
    query.exec();
    ...
    ui->dataTable->removeRow(rownum);
    

    Note, that I didn't tested this code.



  • @Stoyan I get two errors:

    -row was not declared in this scope.
    -request for member 'data' in '((BaseDatosScreen*)this)->BaseDatosScreen::ui->Ui::BaseDatosScreen::<anonymous>.Ui_BaseDatosScreen::dataTable->QTableWidget::item(rownum, 0)', which is of pointer type 'QTableWidgetItem*' (maybe you meant to use '->' ?)
    int id = ui->dataTable->item(rownum, 0).data().toInt();

    the arrow points out the ".data()"

    thankyou a lot, and sorry for everything. I'm learning...



  • And one more thing. You have to add column id in dataTable before hide it.

    ui->dataTable->setItem(fila, 0, new QTableWidgetItem(QString::number(consultar.value(0).toInt())));
    ui->dataTable->setColumnHidden(0,true);
    


  • @Stoyan I fixed the errors!!!

    int rownum = ui->dataTable->row(ui->dataTable->selectedItems().at(0));
    int id = ui->dataTable->item(rownum, 0)->data(0).toInt();
    QSqlQuery query;
    query.prepare("DELETE FROM usuarios WHERE id = ?");
    query.addBindValue(id);
    ui->dataTable->removeRow(rownum);

    This is the code that I put finally.

    Thanks you a lot!!!!!!!


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.