Parameter count mismatch SQLTE-CodePudding

Fairly newbie here to this and need some help, trying to save new employee information into my sqlite database from a user management screen in my application. I am throwing a Parameter count mismatch when I try to save new employee data into my sqlite employeeinfo database.

columns in my sqlite database table are as follows; Registration,Name,Surname,Access,Phone,Email,Username,Password

I am using the following code on pushbutton clicked;

void oviewsettings::on_pushButton_clicked()
{
QString Registration, Name, Surname, Access, Phone, Email, Username, Password;
Registration=ui->lineEdit_usermanagement_Registration->text();
Name=ui->lineEdit_usermanagement_Name->text();
Surname=ui->lineEdit_usermanagement_Surname->text();
Access=ui->lineEdit_usermanagement_Access->text();
Phone=ui->lineEdit_usermanagement_Phone->text();
Email=ui->lineEdit_usermanagement_Email->text();
Username=ui->lineEdit_usermanagement_Username->text();
Password=ui->lineEdit_usermanagement_Password->text();


OViewMain conn;
if(!conn.connOpen()){
    qDebug()<<"Failed to open the database";
    return;
}
conn.connOpen();
QSqlQuery qry;
qry.prepare("INSERT INTO employeeinfo(Registration,Name,Surname,Access,Phone,Email,Username,Password) VALUES ('" Registration "','" Name "','" Surname "','" Access "','" Phone "','" Email "','" Username "','" Password "')");
qry.addBindValue(":Registration");
qry.addBindValue(":Name");
qry.addBindValue(":Surname");
qry.addBindValue(":Access");
qry.addBindValue(":Phone");
qry.addBindValue(":Email");
qry.addBindValue(":Username");
qry.addBindValue(":Password");
qry.exec();

if(qry.exec())
{
    QMessageBox::critical(this,tr("Save"),tr("Database Updated, Saved"));
    conn.connClose();
}
else
{
    QMessageBox::critical(this,tr("Error"),qry.lastError().text());
}

}

CodePudding user response：

Not familiar with qt but from using other prepared statements and looking at the manual it seems like it should be the following code.

qry.prepare("INSERT INTO employeeinfo(Registration, Name, Surname, Access, Phone, Email, Username, Password) VALUES (:Registration, :Name, :Surname, :Access, :Phone, :Email, :Username, :Password)");
qry.BindValue(":Registration", Registration);
qry.BindValue(":Name", Name);
qry.BindValue(":Surname", Surname);
qry.BindValue(":Access", Access);
qry.BindValue(":Phone", Phone);
qry.BindValue(":Email", Email);
qry.BindValue(":Username", Username);
qry.BindValue(":Password", Password);
qry.exec();

The placeholders need to be in the query so the driver knowns where to write the values. The binding then needs a mapping to the placeholder and the value it should have. Additionally BindValue() uses named placeholders, addBindValue() uses anonymous/unnamed placeholders, here's an example using the latter.

qry.prepare("INSERT INTO employeeinfo(Registration, Name, Surname, Access, Phone, Email, Username, Password) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
qry.addBindValue(Registration);
qry.addBindValue(Name);
qry.addBindValue(Surname);
qry.addBindValue(Access);
qry.addBindValue(Phone);
qry.addBindValue(Email);
qry.addBindValue(Username);
qry.addBindValue(Password);
qry.exec();

CodePudding user response：

Thanks for the above, that makes total sense now you have broken it down.

Your updated code is on the money just with a small typo the bindValue has a capital B in your code and QT is case sensitive. so have retyped it below for anyone else having this issue to see it.

One further note: Although with the updated code it saves the user into the sql database, it throws a new error: UNIQUE constraint failed: employeeinfo.Registration Unable to fetch row.

But it does save the user into the DB. I would suppose I have to create a new question to why it throws this error, but would love it if someone could answer that to complete this thread.

See updated code here:

    QSqlQuery qry;
qry.prepare("INSERT INTO employeeinfo(Registration, Name, Surname, Access, Phone, Email, Username, Password) VALUES (:Registration, :Name, :Surname, :Access, :Phone, :Email, :Username, :Password)");
    qry.bindValue(":Registration", Registration);
    qry.bindValue(":Name", Name);
    qry.bindValue(":Surname", Surname);
    qry.bindValue(":Access", Access);
    qry.bindValue(":Phone", Phone);
    qry.bindValue(":Email", Email);
    qry.bindValue(":Username", Username);
    qry.bindValue(":Password", Password);
    qry.exec();