I'm trying to insert data into a table. Here's the code:
void AddContacts::saveContact() {
QString first_name = ui->lineFirstName->text();
QString last_name = ui->lineLastName->text();
QString street_address = ui->lineStreetAddress->text();
QString city = ui->lineCity->text();
QString state = ui->comboBoxState->currentText();
QString zip_code = ui->lineZip->text();
QString personal_email = ui->linePersonalEmail->text();
QString work_email = ui->lineWorkEmail->text();
QString home_phone = ui->lineHomePhone->text();
QString cellular_phone = ui->lineCellularPhone->text();
QString work_phone = ui->lineWorkPhone->text();
QString pager = ui->linePager->text();
QString facebook = ui->lineFacebook->text();
QString twitter = ui->lineTwitter->text();
QString notes = ui->textEditNotes->toPlainText();
// Insert into the database
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("db.db3");
db.open();
QSqlQuery qry;
QString query = "INSERT INTO (id, first_name, last_name, street_address, city, state, zip_code, personal_email, work_email, home_phone, cellular_phone, work_phone, pager, facebook, twitter, notes) VALUES('null','" + first_name + "','" + last_name +"','" + street_address + "','" + city + "','" + state + "','" + zip_code + "','" + personal_email + "','" + work_email + "','" + home_phone + "','" + cellular_phone + "','" + work_phone + "','" + pager + "','" + facebook + "','" + twitter + "', '" + notes + "')";
qry.prepare(query);
if(!qry.exec()) {
qDebug() << qry.lastError();
}
else {
qDebug() << "Success!";
}
Here is what I used to create the table:
CREATE TABLE contacts (
id int primary key,
first_name varchar(20),
last_name varchar(20),
street_address text,
city varchar(30),
state varchar(20),
zip_code varchar(10),
personal_email varchar(40),
work_email varchar(40),
home_phone varchar(12),
cellular_phone varchar(12),
work_phone varchar(12),
pager varchar(12),
facebook text,
twitter varchar(20),
notes text);
I'm getting this error:
QSqlError(-1, "Unable to fetch row", "No query")
I'm not sure what I could be doing wrong. Can you find anything?
Don't insert a string 'null'
as a value for column id
. Use a valid integer instead. If you really want the first row to have a NULL
id, the SQL statement looks like this ... VALUES (NULL, ...
.
Your code is very prone to errors since you don't escape apostrophes in your input. If some of your text fields contain a '
, your SQL statement will fail. You should bind your columns to variables. This is from the Qt references and you should use this approach:
QSqlQuery query;
query.prepare("INSERT INTO contacts (id, first_name, last_name) "
"VALUES (:id, :first_name, :last_name)");
query.bindValue(":id", 1001);
query.bindValue(":first_name", first_name);
query.bindValue(":last_name", last_name);
query.exec();