PDA

View Full Version : Generate Id's for databases



LaTj
21st September 2013, 02:38
//I have made a database with two tables.

CREATE TABLE member (\
id INTEGER PRIMARY KEY,\
name VARCHAR(40) NOT NULL,\
gender VARCHAR(40) NOT NULL,\
dob VARCHAR(40) NOT NULL,\
personalprivacy VARCHAR(40) NOT NULL)

CREATE TABLE persinfo (\
id INTEGER PRIMARY KEY,\
status VARCHAR(40) NULL,\
spouse VARCHAR(40) NULL,\
children VARCHAR(40) NULL,\
siblings VARCHAR(40) NULL,\
parents VARCHAR(40) NULL,\
nameid INTEGER NOT NULL,\
FOREIGN KEY(nameid) REFERENCES member(id)

It works fine. I just want to know if there is a way to generate id's with an auto-increment keyword or by any other means. I have made the first table generate id numbers by using this function.

int generateId(const QString &table)
{
QSqlQuery query;
query.exec("SELECT MAX(id) FROM " + table);
int id = 0;

if (query.next())
id = query.value(0).toInt() + 1;

return id;
}

Like I said it works fine, but it doesn't generate any id's for the persinfo table.

If anyone can help me please?

Lesiok
21st September 2013, 08:29
Depends on the database. What database are you using ?

LaTj
22nd September 2013, 04:17
I'm using SQLite

ChrisW67
22nd September 2013, 05:04
Sqlite FAQ #1: How do I create an AUTOINCREMENT field. (http://www.sqlite.org/faq.html#q1)

Your table declaration already has a unique identifier that will be automatically populated.
To retrieve the id that was assigned during an INSERT use QSqlQuery::lastInsertId() function.

LaTj
24th September 2013, 00:29
Thanks for the advice, and weblink. I found out the real problem and I was wondering if you can help me with this. The problem is that in the function query.next() or QSqlQuery::next() is returning false. It only does this for the member table and not for the persinfo table. Can you help figure out why this happens, i've tried but I cannot figure it out.

Here is more code on how I set the values for each table.

int row = nameModel->rowCount();
nameModel->insertRow(row);

nameModel->setData(nameModel->index(row, Member_Id), generateId("member"));
nameModel->setData(nameModel->index(row, Member_Name), nameform->getNameText());
nameModel->setData(nameModel->index(row, Member_Gender), nameform->getGenderText());
nameModel->setData(nameModel->index(row, Member_DOB), nameform->getDOBDate());
nameModel->setData(nameModel->index(row, Member_PersonalInfo), nameform->getPrivacyText());
nameModel->submitAll();

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

int row = infoModel->rowCount();
infoModel->insertRow(row);

infoModel->setData(infoModel->index(row, Info_Id), generateId("persinfo"));
infoModel->setData(infoModel->index(row, Info_Status), nameform->getStatus());
infoModel->setData(infoModel->index(row, Info_Spouse), nameform->getSpouseName());
infoModel->setData(infoModel->index(row, Info_Children), tempChildQString);
infoModel->setData(infoModel->index(row, Info_Siblings), tempSiblingQString);
infoModel->setData(infoModel->index(row, Info_Parents), nameform->getParentName1());
infoModel->submitAll();

ChrisW67
24th September 2013, 02:17
As the docs say QSqlQuery::next() returns false if there is no next record. I cannot see what the code you posted has to do with that though.

LaTj
26th September 2013, 00:13
I figured out the problem already thanks. As far as the extra code, I just wanted to see if I inserted any data incorrectly, because I was getting false on QSqlQuery::next().
Don't bother replying it has been resolved. Thanks again.