Table not showing changes while entering them, but does with submit button.

rnfmcb

I am using QT 4.8 with PSQL v9, so I can not use a Qsqltablemodel.
I wrote functions to make the Qquerymodel write to the database.

I need my table to submit changes I make on the table to the database with the submit button. The changes I make disapear as I make them but they are updated when I select the submit button. I want the changes to stay on the model until I select the submit button.

MainWindow::MainWindow(const QString &tableName, QWidget *parent): 
	 QWidget(parent)

{
	model = new EditableSqlModel(this);

    view = new QTableView;
    view->setModel(model);
    view->resizeColumnsToContents();

	
    submitButton = new QPushButton(tr("Submit"));
    submitButton->setDefault(true);
    newrowButton = new QPushButton(tr("&New"));
    quitButton = new QPushButton(tr("Quit"));

    buttonBox = new QDialogButtonBox(Qt::Vertical);
    buttonBox->addButton(submitButton, QDialogButtonBox::ActionRole);
    buttonBox->addButton(newrowButton, QDialogButtonBox::ActionRole);
    buttonBox->addButton(quitButton, QDialogButtonBox::RejectRole);
	
	createConnections();   
	
    QHBoxLayout *mainLayout = new QHBoxLayout;
    mainLayout->addWidget(view);
    mainLayout->addWidget(buttonBox); 
    setLayout(mainLayout);
    setWindowTitle(tr("Qt Table"));
	

}


MainWindow::~MainWindow() {
}



void MainWindow:: submit(){ 
	qDebug("Writing to database"); 
	model->writeToDatabase(); 	
	
}

void MainWindow::createConnections(){
	  
   	connect(submitButton, SIGNAL(clicked()), this, SLOT(submit()));
    connect(quitButton, SIGNAL(clicked()), this, SLOT(close()));
	connect(newrowButton, SIGNAL(clicked()), model, SLOT(addRow()));
	
}

//The model code 
EditableSqlModel::EditableSqlModel(QObject *parent)
    : QSqlQueryModel(parent)
{
	queryDatabase(); 
}

Qt::ItemFlags EditableSqlModel::flags(
        const QModelIndex &index) const
{
    Qt::ItemFlags flags = QSqlQueryModel::flags(index);
    if (index.column() == 1 || index.column() == 2)
        flags |= Qt::ItemIsEditable;
    return flags;
}

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
{
    if (index.column() < 1 || index.column() > 2)
        return false;

    QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
    int id = data(primaryKeyIndex).toInt();
	
    bool ok;
	cout << index.column() << endl; 
	
    switch(index.column()) {
		case 1: 
			ok = setServiceType(id, value.toString());
			break; 
		case 2: 
			ok = setServicedBy(id, value.toString());
			break; 
		default: 
			ok = false; 
    }
    return ok;
}

void EditableSqlModel::queryDatabase()
{
	
    setQuery("select id,type,by,date,dur,state from mytable  order by   date ASC limit 1000");
    setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    setHeaderData(1, Qt::Horizontal, QObject::tr(" Type"));
    setHeaderData(2, Qt::Horizontal, QObject::tr("By"));
	setHeaderData(3, Qt::Horizontal, QObject::tr("Date"));
    setHeaderData(4, Qt::Horizontal, QObject::tr("Duration"));
    setHeaderData(5, Qt::Horizontal, QObject::tr("State"));
}


bool EditableSqlModel::setType(int vId, const QString &type)
{
	QByteArray array = type.toLocal8Bit(); 
	char* buffer = array.data(); 
	char sql[4092]; 
	
    QSqlQuery query;
    sprintf(sql,"update mytable  set type = '%s' where id = %d",buffer,vId); 
	string val(sql); 
	queries.push_back(val); 
  	return true; 
   
}

bool EditableSqlModel::setBy(int vId, const QString &serviced)
{
   	QByteArray array = serviced.toLocal8Bit(); 
	char* buffer = array.data(); 
	char sql[4092]; 
    QSqlQuery query;
	
    sprintf(sql,"update mytable set by = '%s' where id = %d",buffer,vId); 
	string val(sql); 
	queries.push_back(val); 
	return true; 

}

//Will be tied to a signal with the save button 
void EditableSqlModel::writeToDatabase(){ 
	
	QSqlQuery query;
		for (it = queries.begin(); it != queries.end(); ++it){	
			string val = *it; 
			char sql[val.size() + 1]; 
			strcpy(sql,val.c_str()); 
			query.prepare(sql); 		
			query.exec();
			if (query.lastError().isValid())
				qDebug() << "query exec" << query.lastError(); 
				qDebug("%s" , query.lastQuery().toStdString().c_str());
		}	
	
	cout << "Database updated" << endl; 
	queryDatabase(); 	
}

rnfmcb

I solved this. The key was setData and Data functions. I created a list of objects from my query in my mainWindow function and sent it to my model. In Data, I returned the values for each column for the list of objects. Then in the SetData function, I set the values for that list in my switch statement. This way, my model was showing the editable list values instead of the query. When I selected submit, it updated the database with the saved queries from the values that were edited.

SGaist

Hi,

Why can't you use QSqlTableModel ?

Christian Ehrlicher

And apart from what @SGaist said - you're ignoring the role in setData() and don't emit dataChanged() signal as explained in the documentation.

And please use the code tags - otherwise your code is not really readable.

rnfmcb

@SGaist Qsqltablemodel doesn't work with PSQL 9 database. The model creates a P22 error cause it can't handle the postgress arrays created by the model when the table is edited.

@Christian-Ehrlicher Can you explain more what you mean by I am ignoring the roles? What roles? Also, isn't the return ok value the datachanged signal? Thanks so much!

jsulm

@rnfmcb said in Table not showing changes while entering them, but does with submit button.:

What roles?

bool QAbstractItemModel::setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)

rnfmcb

I changed the role like you said, I still have the same problem. The table still changes the values back to the original.

Christian Ehrlicher

@rnfmcb said in Table not showing changes while entering them, but does with submit button.:

I changed the role like you said,

We did not say how you should change it so please show use your code. And did you add the dataChanged() signal as explained in the documentation?

rnfmcb

@Christian-Ehrlicher

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)
{
    if (index.column() < 1 || index.column() > 2)
        return false;

    QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
    int id = data(primaryKeyIndex).toInt();			

	bool ok;
	switch(index.column()) {
		case 1: 
			ok = setServiceType(id, value.toString());
			emit dataChanged(index, index);
			break; 
		case 2: 
			ok = setServicedBy(id, value.toString()); 
			emit dataChanged(index, index); 
			break;
		default: 
			ok = false; 
	}
	return ok; 
}

Christian Ehrlicher

The role stuff is still wrong (and I wonder that it even compiles) - you should check the role, not simply ignore it.
Then QSqlQueryModel caches the data - since you don't overwrite data() you have to inform the QSqlQueryModel to refetch the data

rnfmcb

Ah, so then I think I need to override the data function then? I want the table to show the edits until the submit button is pressed, then that submit button submits the database changes. I don't want to update the table with a new query or the old query. Right now, the table changes back to the old query when I select enter or tab, not the new edits.

It seems I need to understand how the qsqlquerymodel caches the data.. How do I inform the model to refetch the data without changing the query?

rnfmcb

@Christian-Ehrlicher I

I have been experimenting with the data and the setdata function. The roles do not seem to matter, neither does datachanged or the value that is returned from setData. I think because I have to requery the database in order to reset the model. I don't want to do that until I select the submit button.

Is there a way to keep the edited changes on the table without updating the query on the qsqlquerymodel? I think the only way is through updating the query? Is there a way to catch the enter or the tab signal and do something with that, since that is what sets the model back to the original query ? Or maybe I have to have a proxymodel to display the data until the submit button is pressed? I attached some code I was using for the setData and data. I don't know if something is still wrong with this. I can't find a solution that works though.

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int role)
{
if (index.column() < 1 || index.column() > 2)
return false;

qDebug() << value << endl; 
bool ok;

if(role == Qt::EditRole || role == Qt::DisplayRole){ 
	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
	int id = data(primaryKeyIndex,role).toInt();			

	
	cout << index.column() << endl; 

	switch(index.column()) {
		case 1: 
			ok = setServiceType(id, value.toString());
			//emit dataChanged(index,index); 
			break; 
		case 2: 
			ok = setServicedBy(id, value.toString()); 
			//emit dataChanged(index, index); 
			break;
		default: 
			ok = false; 
	}		 
} 
return ok;

}

QVariant EditableSqlModel::data(const QModelIndex &index, int role) const
{

if(role != Qt::UserRole || Qt::DisplayRole)
	return QSqlQueryModel::data(index, role);

else {
	qDebug() << "In else" << endl; 
    int columnIdx = role - Qt::UserRole - 1;
    QModelIndex modelIndex = this->index(index.row(), columnIdx);
    return QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
}

}

JonB

@rnfmcb
I don't claim to understand your situation or solution, but do you really have in your data() method:

if(role != Qt::UserRole || Qt::DisplayRole)
	return QSqlQueryModel::data(index, role);

?

rnfmcb

This post is deleted!

rnfmcb

I solved this. The key was setData and Data functions. I created a list of objects from my query in my mainWindow function and sent it to my model. In Data, I returned the values for each column for the list of objects. Then in the SetData function, I set the values for that list in my switch statement. This way, my model was showing the editable list values instead of the query. When I selected submit, it updated the database with the saved queries from the values that were edited.