Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Qt prevent to insert duplicate data SQLITE Problem!
Forum Updated to NodeBB v4.3 + New Features

Qt prevent to insert duplicate data SQLITE Problem!

Scheduled Pinned Locked Moved Solved General and Desktop
6 Posts 4 Posters 1.1k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • S Offline
    S Offline
    Siamak Royal
    wrote on last edited by Siamak Royal
    #1

    Hi there . i have a problem in login and register form that i built in qt ( I am beginner ) . so i wrote a code to prevent inserting duplicate data in sqlite and i write this part of code in buttonClicked of registering.

    if(data.open()){
            QString username,password,email,phone;
            username = ui->line_Edit_username_Registery->text();
            password = ui->line_Edit_password_Registery->text();
            email = ui->line_edit_email->text();
            phone = ui->line_edit_phone->text();
    
            //Query Check
            QSqlQuery queryCheck;
            queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
            queryCheck.bindValue(":username", username);
            queryCheck.bindValue(":password", password);
            queryCheck.bindValue(":email",email);
            queryCheck.bindValue(":phone",phone);
    
    
            bool check = true;
            if ( queryCheck.exec())
            {
    
                while (queryCheck.next()) {
                    QString usernameDB,passwordDB,emailDB,phoneDB;
                    username_database = queryCheck.value(1).toString();
                    password_database = queryCheck.value(2).toString();
                    email_database = queryCheck.value(3).toString();
                    phone_database = queryCheck.value(4).toString();
                    if(username == username_database || phone == phone_database || email == email_database){
                        QMessageBox::information(this,"Error!","Duplicated!");
                        check = false;
    
                    }
    
                }
            }// End Query Check
    

    so as you can see i wrote a condition
    if(username == username_database || phone == phone_database || email == email_database).
    to prevent duplicate data and when i insert all four data ( username , password , email , phone ) data won't insert to SQLITE (Duplicate error QMessageBox::information(this,"Error!","Duplicated!"); ).
    but when i for example changing password and then insert it . data is going to insert in database while there is duplicate user name and phone and password.

    how can i solve this problem.

    Whole Code of Button_Clicked Register Form.

    void mainwindow::on_pushButton_register_clicked()
    {
    
       if(data.open()){
           QString username,password,email,phone;
           username = ui->line_Edit_username_Registery->text();
           password = ui->line_Edit_password_Registery->text();
           email = ui->line_edit_email->text();
           phone = ui->line_edit_phone->text();
    
           //Query Check
           QSqlQuery queryCheck;
           queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
           queryCheck.bindValue(":username", username);
           queryCheck.bindValue(":password", password);
           queryCheck.bindValue(":email",email);
           queryCheck.bindValue(":phone",phone);
    
    
           bool check = true;
           if ( queryCheck.exec())
           {
    
               while (queryCheck.next()) {
                   QString usernameDB,passwordDB,emailDB,phoneDB;
                   username_database = queryCheck.value(1).toString();
                   password_database = queryCheck.value(2).toString();
                   email_database = queryCheck.value(3).toString();
                   phone_database = queryCheck.value(4).toString();
                   if(username == username_database || phone == phone_database || email == email_database){
                       QMessageBox::information(this,"Error!","Duplicated!");
                       check = false;
    
                   }
    
               }
           }// End Query Check
    
    
           // query insert
           if(check){
               QSqlQuery query;
               query.prepare("INSERT INTO register (username, password , email , phone) "
                             "VALUES (:username, :password, :email , :phone)");
               query.bindValue(":username", username);
               query.bindValue(":password", password);
               query.bindValue(":email", email);
               query.bindValue(":phone", phone);
    
    
                   bool chkbox = ui->checkBox->isChecked();
                   //Checking checkbox condition
                   if (chkbox){
                       if(query.exec())
                           QMessageBox::information(this,"this","yes");
                       else
                           QMessageBox::information(this,"this","no");
                   }
                   else {
                       QMessageBox::information(this,"title","Please accept the rules");
                   }//Checking checkbox condition
           }
       }
       else{
           QMessageBox::information(this,"this","database didn't connect");
       }
    }
    
    
    Gojir4G 1 Reply Last reply
    0
    • S Offline
      S Offline
      Siamak Royal
      wrote on last edited by
      #6

      i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

      thank you .

      1 Reply Last reply
      1
      • S Siamak Royal

        Hi there . i have a problem in login and register form that i built in qt ( I am beginner ) . so i wrote a code to prevent inserting duplicate data in sqlite and i write this part of code in buttonClicked of registering.

        if(data.open()){
                QString username,password,email,phone;
                username = ui->line_Edit_username_Registery->text();
                password = ui->line_Edit_password_Registery->text();
                email = ui->line_edit_email->text();
                phone = ui->line_edit_phone->text();
        
                //Query Check
                QSqlQuery queryCheck;
                queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
                queryCheck.bindValue(":username", username);
                queryCheck.bindValue(":password", password);
                queryCheck.bindValue(":email",email);
                queryCheck.bindValue(":phone",phone);
        
        
                bool check = true;
                if ( queryCheck.exec())
                {
        
                    while (queryCheck.next()) {
                        QString usernameDB,passwordDB,emailDB,phoneDB;
                        username_database = queryCheck.value(1).toString();
                        password_database = queryCheck.value(2).toString();
                        email_database = queryCheck.value(3).toString();
                        phone_database = queryCheck.value(4).toString();
                        if(username == username_database || phone == phone_database || email == email_database){
                            QMessageBox::information(this,"Error!","Duplicated!");
                            check = false;
        
                        }
        
                    }
                }// End Query Check
        

        so as you can see i wrote a condition
        if(username == username_database || phone == phone_database || email == email_database).
        to prevent duplicate data and when i insert all four data ( username , password , email , phone ) data won't insert to SQLITE (Duplicate error QMessageBox::information(this,"Error!","Duplicated!"); ).
        but when i for example changing password and then insert it . data is going to insert in database while there is duplicate user name and phone and password.

        how can i solve this problem.

        Whole Code of Button_Clicked Register Form.

        void mainwindow::on_pushButton_register_clicked()
        {
        
           if(data.open()){
               QString username,password,email,phone;
               username = ui->line_Edit_username_Registery->text();
               password = ui->line_Edit_password_Registery->text();
               email = ui->line_edit_email->text();
               phone = ui->line_edit_phone->text();
        
               //Query Check
               QSqlQuery queryCheck;
               queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
               queryCheck.bindValue(":username", username);
               queryCheck.bindValue(":password", password);
               queryCheck.bindValue(":email",email);
               queryCheck.bindValue(":phone",phone);
        
        
               bool check = true;
               if ( queryCheck.exec())
               {
        
                   while (queryCheck.next()) {
                       QString usernameDB,passwordDB,emailDB,phoneDB;
                       username_database = queryCheck.value(1).toString();
                       password_database = queryCheck.value(2).toString();
                       email_database = queryCheck.value(3).toString();
                       phone_database = queryCheck.value(4).toString();
                       if(username == username_database || phone == phone_database || email == email_database){
                           QMessageBox::information(this,"Error!","Duplicated!");
                           check = false;
        
                       }
        
                   }
               }// End Query Check
        
        
               // query insert
               if(check){
                   QSqlQuery query;
                   query.prepare("INSERT INTO register (username, password , email , phone) "
                                 "VALUES (:username, :password, :email , :phone)");
                   query.bindValue(":username", username);
                   query.bindValue(":password", password);
                   query.bindValue(":email", email);
                   query.bindValue(":phone", phone);
        
        
                       bool chkbox = ui->checkBox->isChecked();
                       //Checking checkbox condition
                       if (chkbox){
                           if(query.exec())
                               QMessageBox::information(this,"this","yes");
                           else
                               QMessageBox::information(this,"this","no");
                       }
                       else {
                           QMessageBox::information(this,"title","Please accept the rules");
                       }//Checking checkbox condition
               }
           }
           else{
               QMessageBox::information(this,"this","database didn't connect");
           }
        }
        
        
        Gojir4G Offline
        Gojir4G Offline
        Gojir4
        wrote on last edited by
        #2

        @Siamak-Royal said in Qt prevent to insert duplicate data SQLITE Problem!:

        queryCheck.prepare(QString("SELECT * FROM register where username = :username AND password = :password AND email = :email AND phone = :phone"));

        Hi, I don't know SQL, but here password = :password you are selecting only records with specific password, so I guess you retrieve only records from the database with this specific password and not the others, which have different password but same name, email or phone. That's probably why you don't detect the duplicated data in your loop, because you never iterate on them.

        1 Reply Last reply
        1
        • artwawA Offline
          artwawA Offline
          artwaw
          wrote on last edited by
          #3

          Hi,
          have you considered marking a column(s) in the database unique? Having that set any insert will fail - you can examine error type afterwards to eventually display notification to the user. Seems like less work?

          For more information please re-read.

          Kind Regards,
          Artur

          JonBJ 1 Reply Last reply
          3
          • artwawA artwaw

            Hi,
            have you considered marking a column(s) in the database unique? Having that set any insert will fail - you can examine error type afterwards to eventually display notification to the user. Seems like less work?

            JonBJ Online
            JonBJ Online
            JonB
            wrote on last edited by JonB
            #4

            @artwaw
            I guess OP's username is already primary key(?) One could add email as unique too. I'm thinking that password certainly must not be unique(!), and users may be allowed to share phone numbers....

            artwawA 1 Reply Last reply
            2
            • JonBJ JonB

              @artwaw
              I guess OP's username is already primary key(?) One could add email as unique too. I'm thinking that password certainly must not be unique(!), and users may be allowed to share phone numbers....

              artwawA Offline
              artwawA Offline
              artwaw
              wrote on last edited by
              #5

              @JonB Hard to say, OP didn't share the table definition. However, sqlite is very fast backend so if something can be done there I prefer to do it there.

              For more information please re-read.

              Kind Regards,
              Artur

              1 Reply Last reply
              1
              • S Offline
                S Offline
                Siamak Royal
                wrote on last edited by
                #6

                i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

                thank you .

                1 Reply Last reply
                1

                • Login

                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • Users
                • Groups
                • Search
                • Get Qt Extensions
                • Unsolved