Record update windowd entered data saving

[GreyGeek]

I do something like what is shown below, but if you have an updatable view (using multiple tables) on your database then something similar to this snippet should work.

void homestead::UpdateProperty() {
	QDateTime dtNow = QDateTime::currentDateTime();
	QString propQryPrep = "UPDATE property_";
	propQryPrep.append(this->dbYear);
	propQryPrep.append(" SET \
					   county     = :county, \
					   cntyname   = :cntyname, \
					   txdistrict = :txdistrict, \
					   legal      = :legal, \
					   parcel_id  = :parcel_id, \
					   pvalue     = :pvalue, \
					   entry_id   = :entry_id, \
					   entry_date = :entry_date, \
					   notes      = :notes \
					   WHERE proprty_id = :proprty_id");
 
	propQry.prepare(propQryPrep);
	propQry.bindValue(":county",ui.leCountyNumber->text().toInt());
	propQry.bindValue(":cntyname",ui.cboCountyName->currentText());
	propQry.bindValue(":txdistrict",ui.leTaxDistrict->text());
	propQry.bindValue(":legal",ui.txtLegal->toPlainText());
	propQry.bindValue(":parcel_id",ui.leParcelID->text());
	propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt());
	propQry.bindValue(":entry_id",homestead::RevID);
	propQry.bindValue(":entry_date",dtNow);
	propQry.bindValue(":notes",ui.teNotes->toPlainText());
	propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt());
	if (propQry.exec()) {
		ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!");
	} else {
		ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text());
	}
}

void homestead::UpdateProperty() {
	QDateTime dtNow = QDateTime::currentDateTime();
	QString propQryPrep = "UPDATE property_";
	propQryPrep.append(this->dbYear);
	propQryPrep.append(" SET \
					   county     = :county, \
					   cntyname   = :cntyname, \
					   txdistrict = :txdistrict, \
					   legal      = :legal, \
					   parcel_id  = :parcel_id, \
					   pvalue     = :pvalue, \
					   entry_id   = :entry_id, \
					   entry_date = :entry_date, \
					   notes      = :notes \
					   WHERE proprty_id = :proprty_id");

	propQry.prepare(propQryPrep);
	propQry.bindValue(":county",ui.leCountyNumber->text().toInt());
	propQry.bindValue(":cntyname",ui.cboCountyName->currentText());
	propQry.bindValue(":txdistrict",ui.leTaxDistrict->text());
	propQry.bindValue(":legal",ui.txtLegal->toPlainText());
	propQry.bindValue(":parcel_id",ui.leParcelID->text());
	propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt());
	propQry.bindValue(":entry_id",homestead::RevID);
	propQry.bindValue(":entry_date",dtNow);
	propQry.bindValue(":notes",ui.teNotes->toPlainText());
	propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt());
	if (propQry.exec()) {
		ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!");
	} else {
		ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text());
	}
}

To copy to clipboard, switch view to plain text mode 

BTW, this code works on Oracle and on PostgreSQL before 8.1.0. On PostgreSQL after 8.0 they've added 3 millisecond positions to the QDateTime return.

[fane]

I have found a solution to my problem. I have appended a QSqlField to the record retrieved with model->record()

QSqlRecord rec = model->record();
QSqlField cityField("city", QVariant::Int);
rec.append(cityField);
rec.setValue("name", "Test Customer");
rec.setValue("city", 1); // or whatever value exists in the related table 'cities'
 
model->insertRecord(-1, rec);
model->submit();

QSqlRecord rec = model->record();
QSqlField cityField("city", QVariant::Int);
rec.append(cityField);
rec.setValue("name", "Test Customer");
rec.setValue("city", 1); // or whatever value exists in the related table 'cities'

model->insertRecord(-1, rec);
model->submit();

To copy to clipboard, switch view to plain text mode 

'city' is the name of a field in the 'customers' database table but which is not part of the query that filled the model.
'city_name' is the name of a field in the related table 'cities' and is contained by rec.
I run this code and I got the same "error" - record inserted in the table 'customers' but the value of 'city' field is set to the default == 0. Then I have installed Qt 4.1.0 and magically it worked - record inserted with proper value for field 'city' == 1 (in this case).
So, as wysota said, it is probably a bug in Qt 4.0.0.
Thanks for all suggestions.

[MarkoSan]

Whatever I do, I cannot get data into database!!!! I am going mad!!!

[wysota]

Whatever I do, I cannot get data into database!!!! I am going mad!!!

Please provide a minimal compilable example which reproduces the problem. Your code is too complex (and incomplete) for us to test on our own machines.

[MarkoSan]

Please provide a minimal compilable example which reproduces the problem. Your code is too complex (and incomplete) for us to test on our own machines.

I tried, but zip file is too big and i do not know how to distribure sql databse.

[MarkoSan]

Is the situation maybe connected with http://www.trolltech.com/developer/t...entry&id=86645, what do you think?

[wysota]

I tried, but zip file is too big and i do not know how to distribure sql databse.

So make it smaller. The example is to be minimal. We don't need your database, just a table schema and some sample data.

[MarkoSan]

Here is minimal project file and sources (VC++ 6.0 Service Pack 6, Qt 4.0.1). After unziping in .sql file there are table declarations and sample data.

[fane]

I have managed to make it work but I use Qt 4.1.0 on WinXp and MySQL 4.1.11. Similar code didn't work on Qt 4.0.0, but I don't know about 4.0.1. My modifications are commented with 'modified by fane' (hope I didn't miss anyone), and of course your MySQL root password has to be changed.
I have appended two QSqlFields to the record retrieved with model->record() named "ULICA" and "POSTA_STEVILKA", set their value with QSqlRecord::setValue(). I have modified the edit strategy to be OnRowChange and removed the setValue() call for the "SIFRA" field which is autoincremented by MySQL.
I guess you have to move to Qt 4.1.0 according to this