Problem with creating records in a MYSQL database

[pcheng]

I am having a lot of trouble finding out a problem with MYSQL and QT. If anyone can help it will be much appreciated.

I have the following code to initialize a model with a MYSQL table


// Setup the model to use in the mapper
model = new QSqlRelationalTableModel(this);
model->setTable("Orders");
model->setRelation(5, QSqlRelation("customers", "CustomerNumber", "LastName"));
model->setRelation(6, QSqlRelation("employee", "EmployeeNumber", "LastName"));
model->select();
Qt::SortOrder order = Qt::AscendingOrder;
model->sort(0, order);

// Setup the mapper for the order widgets
mapper = new QDataWidgetMapper(this);
mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
mapper->setModel(model);
mapper->setItemDelegate(new QSqlRelationalDelegate(model));
mapper->addMapping(ui->idLineEdit, 0);
mapper->addMapping(ui->fullfilledCheckBox,1);
mapper->addMapping(ui->fullfilledDateEdit, 2);
mapper->addMapping(ui->orderDateEdit, 3);
mapper->addMapping(ui->dueDateEdit, 4);
mapper->addMapping(ui->customerLineEdit, 5);
mapper->addMapping(ui->employeeLineEdit, 6);

This code creates a model with an Orders table in MYSQL and then uses QDataWidgetMapper to map the fields to the respective widgets in the form. The CustomerLineEdit and the EmployeeLineEdit get their values from another form where you can select the name of the Customer and it inserts the value in the line edit.

I create a new order using the following:


void addOrder::on_newPushButton_clicked()
{
if (!model->submitAll())
qDebug() << model->lastError();
if (!itemModel->submitAll())
qDebug() << itemModel->lastError();
// Create a new item and clear all widgets
QSqlRecord sqlRecord1;
model->insertRecord(-1,sqlRecord1);
mapper->toLast();
clearFields();
updateButtons(mapper->currentIndex());
}

I save any work that I was doing before clicking New and then insert a new blank record in the model and move the mapper to the last item. I then clear the fields from any values they might have and change the Previous and Next buttons.

When I click on the Save button the following code is run:


void addOrder::on_savePushButton_clicked()
{
int currIndex = mapper->currentIndex();
mapper->submit();
// Submit all changes to the database
if (!model->submitAll())
qDebug() << model->lastError();
if (!itemModel->submitAll())
qDebug() << itemModel->lastError();
ui->savePushButton->setEnabled(false);
mapper->setCurrentIndex(currIndex);
}

When I run the program I get the following error. This is because the mapper has an empty value for the OrderID which should be AutoIncremented in MYSQL.

QSqlError(1366, "QMYSQL3: Unable to execute statement", "Incorrect integer value: '' for column 'OrderID' at row 1")

I have another form for another table and this works fine so MYSQL is working.

Can anyone spot any problems in this code?

Thanks,

Pericles

Added after 9 minutes:

If I remove lines 4,5,14 which are responsible for setting the relationships between the orders table and the customer and employee tables then the whole thing works fine.

Any ideas on how I can incorporate the relationships correctly or why it destroys my saving?

Added after 26 minutes:

Once again after a little further debugging I found another clue.

I put back lines 4,5,14 and run the code.

If I type the numbers of the customerid and the employeeid and save it works fine. It even shows the names of the customer and employee. If I use the Find form to put the number in the field it does not work.

The code I use in the Find is as follows:


void addOrder::on_findEmployeePushButton_clicked()
{
// Find an Employee
// This will be deprecated when the final program is done because it will get the value from the logged in user
FindEmployee *find = new FindEmployee();
if (find->exec())
ui->employeeLineEdit->setText(QString::number(find->getIndex()));
}


void addOrder::on_findCustomerPushButton_clicked()
{
// Find a Customer
findCustomer *find = new findCustomer();
if (find->exec())
ui->customerLineEdit->setText(QString::number(find->getIndex()));
}

Added after 43 minutes:

Found it.

I added the command ui->employeeLineEdit->setFocus(); to force the mapper to autosubmit when it loses focus.

Everything works fine now.

Pericles