How to check Primary key is Exists in Table ?

[hohoanganh205]

I have a Database with Table SinhVien ( MSSV, HoTen, Lop_MaLop ),

1.jpg

I created QT C++ project with Buttons: Add, Edit, Del and connect to my Database (QMySQL). When I enter a value to MSSV and press Edit Button, will be edit information of HoTen, Lop_MaLop look like code :

2.jpg

QSqlQuery *qry=new QSqlQuery();
qry->prepare(
"UPDATE SinhVien SET HoTen = '" + ui.txtTenSinhVien->text()
+ "', Lop_MaLop = " + ui.txtLop->text() + " where MSSV = "
+ ui.txtMaSV->text() + "");
qry->exec();

It's work but we can not know the value entered on MSSV is Exists or Not Exists in Database (Table MSSV ), How to check ? I try this code but not work :

QSqlQuery *qry=new QSqlQuery();

if("exists(select MSSV from SinhVien where MSSV = )" + ui.txtMaSV->text())
{
qry->prepare(
"UPDATE SinhVien SET HoTen = '" + ui.txtTenSinhVien->text()
+ "', Lop_MaLop = " + ui.txtLop->text() + " where MSSV = "
+ ui.txtMaSV->text() + "");
qry->exec();
QMessageBox::information(this, QString::fromUtf8("Yeah !."),
QString::fromUtf8("Edit successful !"));
ShowData();
}
else
{
//QMessageBox::information(this, QString::fromUtf8("Warning !."),
//QString::fromUtf8("MSSV Not Exists in Database!"));
return;
}


Thanks for Help !

[ChrisW67]

You need to build an SQL query with that exists clause in the where part of the query. execute it, and look at the result.

You should also avoid building queries by joining query fragments as strings. This is hard to read and maintain, is easy to break, and opens up security issues. Use named parameters and the bindValue() function. See Approaches to Binding Values

[hohoanganh205]

Thanks ChrisW67,
I read bindValue() function and will be apply to code.

see you again

[hohoanganh205]

I finished my code, share to another Beginners:

QSqlQuery *checkexists=new QSqlQuery();
	checkexists->prepare("select MaDaiLy from DaiLy where MaDaiLy = " + ui.txtMaDL->text());
	checkexists->exec();	
	if((checkexists->last())==true)
	{
		//exists
	}
	else
	{
		//not exists
                // do something.
    }

QSqlQuery *checkexists=new QSqlQuery();
	checkexists->prepare("select MaDaiLy from DaiLy where MaDaiLy = " + ui.txtMaDL->text());
	checkexists->exec();	
	if((checkexists->last())==true)
	{
		//exists
	}
	else
	{
		//not exists
                // do something.
    }

To copy to clipboard, switch view to plain text mode 

[Lesiok]

This is not working because You are executing query like this :

select MaDaiLy from DaiLy where MaDaiLy = blablabla

select MaDaiLy from DaiLy where MaDaiLy = blablabla

To copy to clipboard, switch view to plain text mode 

and of course You got an error from SQL engine because You don't have column 'blablabla' in table DaiLy. You should do this like :

checkexists->prepare("select MaDaiLy from DaiLy where MaDaiLy = ?");
checkexists->bindValue(0, ui.txtMaDL->text());

checkexists->prepare("select MaDaiLy from DaiLy where MaDaiLy = ?");
checkexists->bindValue(0, ui.txtMaDL->text());

To copy to clipboard, switch view to plain text mode