Bind a QComboBox to a Database

[onefootswill]

Is it possible to bind a QComboBox to a database relation? For example, I have an authors relation and would like to load all of the authors into a QComboBox. Here is the code for what I have tried:

addBookDialog::addBookDialog(QWidget *parent)
    : QDialog(parent)
{
	ui.setupUi(this);
	createConnection();
	QMessageBox oi;
 
	QSqlQueryModel sqlAuthors;
	sqlAuthors.setQuery("SELECT nr, lname FROM authors;");
	ui.authorsComboBox->setModel(&sqlAuthors);
	//ui.authorsComboBox->setModelColumn(0);
 
}
 
addBookDialog::~addBookDialog()
{
 
}
 
bool addBookDialog::createConnection()
{
	db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("stuff.s3db");
	if (!db.open()) {
//		QMessageBox::critical(0, tr("Cannot open database"), db.lastError().text());
		return false;
	}
	return true;
 
}

addBookDialog::addBookDialog(QWidget *parent)
    : QDialog(parent)
{
	ui.setupUi(this);
	createConnection();
	QMessageBox oi;

	QSqlQueryModel sqlAuthors;
	sqlAuthors.setQuery("SELECT nr, lname FROM authors;");
	ui.authorsComboBox->setModel(&sqlAuthors);
	//ui.authorsComboBox->setModelColumn(0);

}

addBookDialog::~addBookDialog()
{

}

bool addBookDialog::createConnection()
{
	db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("stuff.s3db");
	if (!db.open()) {
//		QMessageBox::critical(0, tr("Cannot open database"), db.lastError().text());
		return false;
	}
	return true;

}

To copy to clipboard, switch view to plain text mode 

Cheers

[spirit]

try this

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("test.db");
 
	if (!db.open()) {
		qDebug() << db.lastError().text();
		return;
	}
 
	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE test (id int PRIMARY KEY, test nvarchar)");
 
	qDebug() << query.exec("INSERT INTO test VALUES(0, 'a')");
	qDebug() << query.exec("INSERT INTO test VALUES(1, 'b')");
	qDebug() << query.exec("INSERT INTO test VALUES(2, 'c')");
	qDebug() << query.exec("INSERT INTO test VALUES(3, 'd')");
	qDebug() << query.exec("SELECT test FROM test WHERE id = 1");
 
	QSqlQueryModel *model = new QSqlQueryModel;
	model->setQuery("SELECT id, test FROM test");
	model->setHeaderData(0, Qt::Horizontal, tr("id"));
	model->setHeaderData(1, Qt::Horizontal, tr("test"));
 
	QTableView *view = new QTableView;
 
	QComboBox *cb = new QComboBox();
	cb->setModel(model);
	cb->setView(view);

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("test.db");

	if (!db.open()) {
		qDebug() << db.lastError().text();
		return;
	}

	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE test (id int PRIMARY KEY, test nvarchar)");
	 
	qDebug() << query.exec("INSERT INTO test VALUES(0, 'a')");
	qDebug() << query.exec("INSERT INTO test VALUES(1, 'b')");
	qDebug() << query.exec("INSERT INTO test VALUES(2, 'c')");
	qDebug() << query.exec("INSERT INTO test VALUES(3, 'd')");
	qDebug() << query.exec("SELECT test FROM test WHERE id = 1");

	QSqlQueryModel *model = new QSqlQueryModel;
	model->setQuery("SELECT id, test FROM test");
	model->setHeaderData(0, Qt::Horizontal, tr("id"));
	model->setHeaderData(1, Qt::Horizontal, tr("test"));

	QTableView *view = new QTableView;

	QComboBox *cb = new QComboBox();
	cb->setModel(model);
	cb->setView(view);

To copy to clipboard, switch view to plain text mode 

[onefootswill]

Thanks very much. There's just one last thing I cannot get working. I want to make the "nr" column invisible, so that only the author's names are visible. Here is what I have tried:

addBookDialog::addBookDialog(QWidget *parent)
    : QDialog(parent)
{
	ui.setupUi(this);
 
	QSqlQueryModel *sqlAuthors = new QSqlQueryModel;
	sqlAuthors->setQuery("SELECT nr, fname || ' ' || minitial || ' ' || lname FROM authors;");
	sqlAuthors->setHeaderData(0,Qt::Horizontal, tr("nr"));
	sqlAuthors->setHeaderData(1, Qt::Horizontal, tr("Name"));
 
	QTableView *view = new QTableView;
	view->verticalHeader()->hide();
	view->horizontalHeader()->hide();
	view->setColumnHidden(0, true);
//	view->setColumnWidth(0,0);
	view->setSelectionBehavior(QAbstractItemView::SelectRows);
	view->setSelectionMode(QAbstractItemView::SingleSelection);
	ui.authorsComboBox->setModel(sqlAuthors);
	ui.authorsComboBox->setView(view);
 
}

addBookDialog::addBookDialog(QWidget *parent)
    : QDialog(parent)
{
	ui.setupUi(this);

	QSqlQueryModel *sqlAuthors = new QSqlQueryModel;
	sqlAuthors->setQuery("SELECT nr, fname || ' ' || minitial || ' ' || lname FROM authors;");
	sqlAuthors->setHeaderData(0,Qt::Horizontal, tr("nr"));
	sqlAuthors->setHeaderData(1, Qt::Horizontal, tr("Name"));

	QTableView *view = new QTableView;
	view->verticalHeader()->hide();
	view->horizontalHeader()->hide();
	view->setColumnHidden(0, true);
//	view->setColumnWidth(0,0);
	view->setSelectionBehavior(QAbstractItemView::SelectRows);
	view->setSelectionMode(QAbstractItemView::SingleSelection);
	ui.authorsComboBox->setModel(sqlAuthors);
	ui.authorsComboBox->setView(view);

}

To copy to clipboard, switch view to plain text mode 

I have also tried view->hideColumn(0), but that does not hide it either. I feel I am once again missing something simple.

Cheers.

[spirit]

try this

...
    view->setSelectionBehavior(QAbstractItemView::SelectRows);
    view->setSelectionMode(QAbstractItemView::SingleSelection);
    ui.authorsComboBox->setModel(sqlAuthors);
    ui.authorsComboBox->setView(view);
    view->setColumnHidden(0, true);

...
    view->setSelectionBehavior(QAbstractItemView::SelectRows);
    view->setSelectionMode(QAbstractItemView::SingleSelection);
    ui.authorsComboBox->setModel(sqlAuthors);
    ui.authorsComboBox->setView(view);
    view->setColumnHidden(0, true);

To copy to clipboard, switch view to plain text mode 

[onefootswill]

Cheers for that. With a bit of experimentation, I found that I had to hide the second column. This was no big hassle as I just swapped the order of the return values in the SQL SELECT statement. For one reason or another, it did not like having the first column (index 0) hidden. The code is:

sqlAuthors = new QSqlQueryModel;
	sqlAuthors->setQuery("SELECT fname || ' ' || minitial || ' ' || lname, nr FROM authors;");
	sqlAuthors->setHeaderData(0,Qt::Horizontal, tr("nr"));
	sqlAuthors->setHeaderData(1, Qt::Horizontal, tr("Name"));
 
	view = new QTableView;
	view->verticalHeader()->hide();
	view->horizontalHeader()->hide();
	view->setSelectionBehavior(QAbstractItemView::SelectRows);
	view->setSelectionMode(QAbstractItemView::SingleSelection);
 
	ui.authorsComboBox->setModel(sqlAuthors);
	ui.authorsComboBox->setView(view);
	view->setColumnHidden(1, true);

sqlAuthors = new QSqlQueryModel;
	sqlAuthors->setQuery("SELECT fname || ' ' || minitial || ' ' || lname, nr FROM authors;");
	sqlAuthors->setHeaderData(0,Qt::Horizontal, tr("nr"));
	sqlAuthors->setHeaderData(1, Qt::Horizontal, tr("Name"));

	view = new QTableView;
	view->verticalHeader()->hide();
	view->horizontalHeader()->hide();
	view->setSelectionBehavior(QAbstractItemView::SelectRows);
	view->setSelectionMode(QAbstractItemView::SingleSelection);

	ui.authorsComboBox->setModel(sqlAuthors);
	ui.authorsComboBox->setView(view);
	view->setColumnHidden(1, true);

To copy to clipboard, switch view to plain text mode 

Thanks very much for your help.