How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........
-
how to pass the parameters for updating the record using stored procedure call in qt application....please find the code below
// QString strSql1 = "{CALL myupdate1(:id,:name,:lastname,:contact,:emailid)}";
QString strSql1 = "{CALL myupdate1(?,?,?,?,?)}";
query.prepare(strSql1);
if(query.exec())
{
//ui->pushButton->setText("Update");QMessageBox::information(this,tr("Update"),tr("Record has been updated")); on_clear_pushButton_clicked(); } -
Hi ,
I am writing the stored procedure like this ,ALTER PROC myupdate1
(
@id INT,
@name VARCHAR(100),
@contact INT,
@lastname VARCHAR(100),
@emailId VARCHAR(100)
)
AS
BEGIN
----INSERT INTO Info (id ,name,lastname,contact,emailid)VALUES(@id,@Name,@lastname,@contact,@emailid);
--UPDATE Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"'
UPDATE Info SET name = @name,contact = @contact,lastname = @lastname,emailId = @emailId WHERE id = @id
ENDif anything wrong ......i want to write for generic way.....
-
Hi,
AFAIK, it's described in the Approaches to binding values in the QSqlQuery documentation.
Hope it helps
@SGaist said in How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........:
AFAIK, it's described in the Approaches to binding values in the QSqlQuery documentation.
@SGaist said in How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........:
Did you see the 'Binding values to a stored procedure:' part of the documentation I linked to ?
-
okay i have gone through the documents ...I did not understand the updating the record of the stored procedure ...i am trying to call the stored procedure name in the qt application it is build successfully but not updating record please help me what are the parameters need to pass?
-
Please show the exact code you are using.
-
Hi SGaist,
this is my stored procedureCREATE PROCEDURE myupdate1 ( @id INT, @name NVARCHAR(100), @contact INT, @lastname NVARCHAR(100), @emailId NVARCHAR(100) ) AS --DECLARE @name NVARCHAR(100),@contact INT,@lastname NVARCHAR(100),@emailId NVARCHAR(100) BEGIN SET NOCOUNT ON; --INSERT INTO Info (id ,name,lastname,contact,emailid)VALUES(@id,@Name,@lastname,@contact,@emailid); --UPDATE Info SET name=??,contact=?,lastname=?,emailId=? where id=? --update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' -- UPDATE Info SET name=ISNULL(@name,name) ,contact=ISNULL (@contact,contact),lastname =ISNULL (@lastname,lastname),emailId =ISNULL(@emailId,emailId) WHERE id = @id UPDATE Info SET name=ISNULL(@name,'"+name+"') ,contact=ISNULL (@contact,'"+contact+"'),lastname =ISNULL (@lastname,'"+lastname+"'),emailId =ISNULL(@emailId,'"+emailId+"') WHERE id = @id END -
I am calling this stored procedure in my qt application ,the code is
// myupdate......... else { //ui->on_->setText("Update"); setStyleSheet("QPushButton:focus { outline: 0 }"); ui->add_pushbutton->setText("Update"); QString id ,name,contact,lastname,emailid; name = ui->lineEdit_2->text(); lastname = ui->lineEdit_3->text(); contact = ui->lineEdit_4->text(); emailid = ui->lineEdit_5->text(); ui->lineEdit->setFocus(); QSqlQueryModel *model = new QSqlQueryModel; QSqlQuery query; // MainWindow ::findChildren(); // Setting the read only ............ QList <QLineEdit*> l_lineEdits = ui->lineEdit->findChildren<QLineEdit*>(); foreach(QLineEdit* id,l_lineEdits){ id->setReadOnly(true); } ui->lineEdit->setReadOnly(true); /* QPalette *palette = new QPalette(); palette->setColor(QPalette::Base,Qt::white); palette->setColor(QPalette::Text,Qt::white); ui->lineEdit_5->setPalette(*palette);*/ //ID while updating id = ui->lineEdit->text(); if ((name == NULL && contact == NULL && lastname == NULL && emailid == NULL) && (name == "" && contact == "" && lastname == "" && emailid == "") ) { QMessageBox::critical(this,tr("Error"),tr("You Cant't able to Update the Table")); ui->lineEdit->setFocus(); ui->lineEdit_2->setFocus(); ui->lineEdit_3->setFocus(); ui->lineEdit_4->setFocus(); ui->lineEdit_5->setFocus(); } else { // QLineEdit *accessLineEdit; // accessLineEdit->setReadOnly(true); QRegExp re8( "^[A-Za-z]*$" ); //Name while updating name = ui->lineEdit_2->text(); if(name == "" || lastname == "" || contact == "" || emailid == "") { if (name == "") { QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Enter the Valid Name")); ui->lineEdit_2->setFocus(); } if(lastname == "" ) { QMessageBox::critical(this,tr("Validation of Lastname:"),tr("Please Enter the Valid Lastname")); ui->lineEdit_3->setFocus(); } if(contact == "") { QMessageBox::critical(this,tr("Validation of Contact:"),tr("Please Enter the Valid Mobile Number:")); ui->lineEdit_4->setFocus(); } if(emailid == "") { QMessageBox::critical(this,tr("Validation of EmailId:"),tr("Please Enter the Valid EmailId:")); ui->lineEdit_5->setFocus(); } } else { QString text88 = name; bool ok1; int value = text88.toInt(&ok1); if(!ok1) { name = ui->lineEdit_2->text(); } else { ui->lineEdit_2->setFocus(); QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Update with Valid Name")); if (text88 == "0123456789" || text88 == NULL || text88 == 0 ) { QMessageBox msgBox; msgBox.setIcon(QMessageBox::Warning); msgBox.setWindowTitle("Wrong Name" ); msgBox.setText("Please Enter the Valid Name"); ui->lineEdit_2->setFocus(); QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Enter the Valid Name")); } } //Lastname while updating QRegExp re8( "^[A-Za-z]*$" ); lastname = ui->lineEdit_3->text(); //QRegExpValidator *validator8 = new QRegExpValidator( re8, 0 ); //ui->lineEdit_4->setValidator(validator8); // lastname = ui->lineEdit_4->text(); QString text77 = lastname; bool ok77 = true; int value7 = text77.toInt(&ok77); if(!ok77) { //lastname = text7; lastname = ui->lineEdit_3->text(); // query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' "); //query.exec(); } else { ui->lineEdit_3->setFocus(); QMessageBox::critical(this,tr("Validation of LastName:"),tr("Please Update with the Valid LastName")); if (text77 == "0123456789" || text77 == NULL || text77 == 0 ) { QMessageBox msgBox; msgBox.setIcon(QMessageBox::Warning); msgBox.setWindowTitle("Wrong Name" ); msgBox.setText("Please Enter the Valid Name"); ui->lineEdit_3->setFocus(); QMessageBox::critical(this,tr("Validation of LastName:"),tr("Please Update with the Valid LastName")); } } //Contact while updating contact = ui->lineEdit_4->text(); // QRegExpValidator *validator9 = new QRegExpValidator( re8, 0 ); // ui->lineEdit_3->setValidator(validator9); // contact = ui->lineEdit_4->text(); qint64 mobile=contact.toInt(); if(((const char*)mobile) == "" || ((const char*)mobile) == NULL || ((const char*)mobile) == 0 ) { QMessageBox msgBox; msgBox.setIcon(QMessageBox::Warning); msgBox.setWindowTitle("Wrong ID" ); msgBox.setText("Please Update with the Valid Mobile Number: "); ui->lineEdit_4->setFocus(); QMessageBox::critical(this,tr("Validation of Mobile Number:"),tr("Please Update with the Valid Mobile Number:")); } else { //query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' "); contact = ui->lineEdit_4->text(); } //Email ID validating and updating emailid = ui->lineEdit_5->text(); bool retorno = true; qDebug() << emailid; QRegularExpression regex("^[0-9a-zA-Z]+([0-9a-zA-Z]*[-._+])*[0-9a-zA-Z]+@[0-9a-zA-Z]+([-.][0-9a-zA-Z]+)*([0-9a-zA-Z]*[.])[a-zA-Z]{2,6}$"); if(!regex.match(emailid).hasMatch()) { retorno = false; ui->lineEdit_5->setFocus(); QMessageBox::critical(this,tr("Validation of EmailID:"),tr("Please Update with the Valid EmailID:")); } if(regex.match(emailid).hasMatch()) { retorno = true; } if((!ok1) && (!ok77) && (regex.match(emailid).hasMatch()) && (((const char*)mobile) != "" || ((const char*)mobile) != NULL || ((const char*)mobile) != 0)) { QSqlQuery query; // query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' "); ui->lineEdit->setReadOnly(true); id = ui->lineEdit->text().toInt(); //id = ui->lineEdit->text(); //name = ui->lineEdit_2->text(); //lastname = ui->lineEdit_3->text(); //contact = ui->lineEdit_4->text().toInt(); //emailid = ui->lineEdit_5->text(); // Calling stored procedure name ...... // QString strSql1 = "{CALL myupdate1(17,'veera',9999999,'raj','raja@gmail.com')}"; QString strSql1 = "{CALL myupdate1(:id,:name,:contact,:lastname,:emailId)}"; // QString strSql1 = "{CALL myupdate1(?,?,?,?,?)}"; query.prepare(strSql1); if(query.exec()) { //ui->pushButton->setText("Update"); QMessageBox::information(this,tr("Update"),tr("Record has been updated")); on_clear_pushButton_clicked(); } int up = query.boundValue(1).toInt(); qDebug() << up ; } else QMessageBox::critical(this,tr("Error:"),tr("Record updation Error:")); model->setQuery("SELECT *FROM Info"); ui->tableView->setModel(model); // changing the text box // ui->add_pushbutton->setText("Add"); /* // clearing the Textbox foreach (QLineEdit* clr, this->findChildren<QLineEdit*>()) { clr->clear(); emit clearFocus(); }*/ // ui->lineEdit->setFocus(); // MainWindow ::findChildren(); // Setting the read only ............ QList <QLineEdit*> ll_lineEdits = ui->lineEdit->findChildren<QLineEdit*>(); foreach(QLineEdit* id,ll_lineEdits){ id->setReadOnly(false); } ui->lineEdit->setReadOnly(false); } } } } -
Please read again the example in the documentation of QSqlQuery::prepare. More specifically the calls to bindValue and addBindValue.
You're just preparing the query but you don't assign anything for its parameters. How do you expect the query to run successfully when non of its parameters have values ?
Also you should print the error you get when the query is failing rather than just a message when it succeeds.
-
Hi SGaist,
I am writing stored procedure in DBeaver-General GUI using MS SQL for inserting records into the tableview in qt application,i am sharing the my stored procedure asALTER PROCEDURE dbo.insert_data2 ( @ID INT, @Name VARCHAR(100), @Lastname VARCHAR(100), @contact INT, @EmailId VARCHAR(100), @ret INTEGER=NULL OUTPUT ) AS BEGIN If Exists (select ID from Info where ID = @ID) BEGIN SELECT @ret = -2 RETURN @ret; -- SELECT -2 as StatusID --ID already exists. So returning -2. Means, not successful END ELSE BEGIN INSERT INTO Info (ID,Name,Lastname,contact,EmailId) VALUES(@ID,@Name,@Lastname,@contact,@EmailId); --SELECT *FROM Info --SELECT 2 as StatusID SELECT @ret = 115; RETURN @ret; END ENDafter successful of insertion it needs to return value is "115" but returning 1 what I am doing wrong ,I did not understand please guide me right direction...
