insert data to database

[vanduongbk]

hi everyone,
i have init a database and i want to insert data to this database ,i write a code as below, when compiler without any error but can not insert any data
please help me

void MainForm::creat_db()
{
  db = QSqlDatabase::addDatabase("QSQLITE");
  #ifdef Q_OS_LINUX
  // store database file into user home folder in Linux
  QString path(QDir::home().path());
  path.append(QDir::separator()).append("my.db.sqlite");
  path = QDir::toNativeSeparators(path);
  db.setDatabaseName(path);
  #else
  // file exists in the application private folder
  db.setDatabaseName("my.db.sqlite");
  #endif
  // Open databasee
  if(!db.open())
  {
      QMessageBox::warning(0, QObject::tr("database error"),db.lastError().text());
  }
 
  QSqlQuery query;
  query.exec("create table db_table "
                  "(id integer primary key, "
                  "start_time varchar(20), "
                  "decription varchar(30), "
                 "finish_time varchar(20))");
 }
 
int MainForm::insert_to_db(QString start_time, QString description, QString finish_time)
 {
 int newId = -1;
 bool ret = false;
 if (db.isOpen())
 {
 // NULL = is the keyword for the autoincrement to generate next value
 QSqlQuery query;
 ret = query.exec(QString("insert into db_table(NULL,'%1','%2','%3')")
                  .arg(start_time).arg(description).arg(finish_time));
 // Get database given autoincrement value
 if (ret)
 {
 newId = query.lastInsertId().toInt();
 }
 }
 return newId;
 }

void MainForm::creat_db()
{
  db = QSqlDatabase::addDatabase("QSQLITE");
  #ifdef Q_OS_LINUX
  // store database file into user home folder in Linux
  QString path(QDir::home().path());
  path.append(QDir::separator()).append("my.db.sqlite");
  path = QDir::toNativeSeparators(path);
  db.setDatabaseName(path);
  #else
  // file exists in the application private folder
  db.setDatabaseName("my.db.sqlite");
  #endif
  // Open databasee
  if(!db.open())
  {
      QMessageBox::warning(0, QObject::tr("database error"),db.lastError().text());
  }

  QSqlQuery query;
  query.exec("create table db_table "
                  "(id integer primary key, "
                  "start_time varchar(20), "
                  "decription varchar(30), "
                 "finish_time varchar(20))");
 }

int MainForm::insert_to_db(QString start_time, QString description, QString finish_time)
 {
 int newId = -1;
 bool ret = false;
 if (db.isOpen())
 {
 // NULL = is the keyword for the autoincrement to generate next value
 QSqlQuery query;
 ret = query.exec(QString("insert into db_table(NULL,'%1','%2','%3')")
                  .arg(start_time).arg(description).arg(finish_time));
 // Get database given autoincrement value
 if (ret)
 {
 newId = query.lastInsertId().toInt();
 }
 }
 return newId;
 }

To copy to clipboard, switch view to plain text mode 

and I call them in mainform

MainForm::MainForm(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainForm)
{
    ui-> setupUi(this);
    creat_db();
    insert_to_db("10:10","too high","10:12"); //only test
}
...........

MainForm::MainForm(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainForm)
{
    ui-> setupUi(this);
    creat_db();
    insert_to_db("10:10","too high","10:12"); //only test
}
...........

To copy to clipboard, switch view to plain text mode 

[nix]

First you should check exec() return to check the status. Then this is sql command I guess you should have a ";" at the end of the string.

To be sure, check the return value of every calls to exec() and if it's not ok use lastError() method to get more information about the error you got.

[vanduongbk]

thank nix
can you edit above code and write a demo for it

[nix]

Your insert command was wrong. Try

// Add include QSqlQuery, QSqlError, QtDebug
void MainForm::creat_db()
{
  db = QSqlDatabase::addDatabase("QSQLITE");
  #ifdef Q_OS_LINUX
  // store database file into user home folder in Linux
  QString path(QDir::home().path());
  path.append(QDir::separator()).append("my.db.sqlite");
  path = QDir::toNativeSeparators(path);
  db.setDatabaseName(path);
  #else
  // file exists in the application private folder
  db.setDatabaseName("my.db.sqlite");
  #endif
  // Open databasee
  if(!db.open())
  {
      QMessageBox::warning(0, QObject::tr("database error"),db.lastError().text());
  }
 
  QSqlQuery query;
  if ( ! query.exec("create table db_table "
                  "(id integer primary key, "
                  "start_time varchar(20), "
                  "decription varchar(30), "
                 "finish_time varchar(20));"))
  {
    qDebug() << query.lastError().text();
  }
 }
 
int MainForm::insert_to_db(QString start_time, QString description, QString finish_time)
 {
 int newId = -1;
 bool ret = false;
 if (db.isOpen())
 {
 // NULL = is the keyword for the autoincrement to generate next value
 QSqlQuery query;
 ret = query.exec(QString("insert into db_table(start_time, decription, finish_time)  values('%1','%2','%3');")
                  .arg(start_time).arg(description).arg(finish_time));
 // Get database given autoincrement value
 if (ret)
 {
 newId = query.lastInsertId().toInt();
 }
 else
 {
   qDebug() << query.lastError().text();
 }
 }
 return newId;
 }

// Add include QSqlQuery, QSqlError, QtDebug
void MainForm::creat_db()
{
  db = QSqlDatabase::addDatabase("QSQLITE");
  #ifdef Q_OS_LINUX
  // store database file into user home folder in Linux
  QString path(QDir::home().path());
  path.append(QDir::separator()).append("my.db.sqlite");
  path = QDir::toNativeSeparators(path);
  db.setDatabaseName(path);
  #else
  // file exists in the application private folder
  db.setDatabaseName("my.db.sqlite");
  #endif
  // Open databasee
  if(!db.open())
  {
      QMessageBox::warning(0, QObject::tr("database error"),db.lastError().text());
  }

  QSqlQuery query;
  if ( ! query.exec("create table db_table "
                  "(id integer primary key, "
                  "start_time varchar(20), "
                  "decription varchar(30), "
                 "finish_time varchar(20));"))
  {
    qDebug() << query.lastError().text();
  }
 }

int MainForm::insert_to_db(QString start_time, QString description, QString finish_time)
 {
 int newId = -1;
 bool ret = false;
 if (db.isOpen())
 {
 // NULL = is the keyword for the autoincrement to generate next value
 QSqlQuery query;
 ret = query.exec(QString("insert into db_table(start_time, decription, finish_time)  values('%1','%2','%3');")
                  .arg(start_time).arg(description).arg(finish_time));
 // Get database given autoincrement value
 if (ret)
 {
 newId = query.lastInsertId().toInt();
 }
 else
 {
   qDebug() << query.lastError().text();
 }
 }
 return newId;
 }

To copy to clipboard, switch view to plain text mode 

You can replace the qDebug() by messagebox.

[vanduongbk]

thank you nix very much ,my problem was solve
if i create a result form , and result form is child form of mainform , on mainform have result button to open a result form
i have measurement and write data to a database in mainform ,
in result form ,i have creat a qtablewidget to display data, how i can display all data form database to this qtablewidget
thank for help

[ChrisW67]

QTableView and QSqlTableModel or QSqlQueryModel.

[vanduongbk]

hello
how i can create many table in database ,sample as db1_table ,db2_table ....
and how i can write a insert function to each dbn_table
i use qtablewidget ,how when a record was insert to db ,it is also simultaneously display on qtablewidget
plz me ,thank

[vanduongbk]

hi ChrisW67
i have write a code in result form as below to display the data from a database but when compile ,it appear error:

C2248: 'QTableWidget::setModel' : cannot access private member declared in class 'QTableWidget'
see declaration of 'QTableWidget::setModel'
see declaration of 'QTableWidget'

QSqlQueryModel *model=new QSqlQueryModel;
    model->setQuery("SELECT start_time,description,finish_time FROM db_table");
    model->setHeaderData(0,Qt::Horizontal,tr("start_time"));
    model->setHeaderData(1,Qt::Horizontal,tr("description"));
    model->setHeaderData(1,Qt::Horizontal,tr("finish_time"));
 
    ui->tableWidget_1->setModel(model);
    ui->tableWidget_1->show();

QSqlQueryModel *model=new QSqlQueryModel;
    model->setQuery("SELECT start_time,description,finish_time FROM db_table");
    model->setHeaderData(0,Qt::Horizontal,tr("start_time"));
    model->setHeaderData(1,Qt::Horizontal,tr("description"));
    model->setHeaderData(1,Qt::Horizontal,tr("finish_time"));

    ui->tableWidget_1->setModel(model);
    ui->tableWidget_1->show();

To copy to clipboard, switch view to plain text mode 

[nix]

Because you want use QTableView instead of QTableWidget.

[vanduongbk]

hi nix
this mean that i can not use QTableWidget for display data ,only can use QTableView ?

[nix]

If you want use a table with a model use QTableView if you want to insert your data by yourself use QTableWidget. Read the doc about those two classes and about Model/View programming

[vanduongbk]

hi nix
how i can call data from database in mainform and display this database on result form

[nix]

Your code using a model was not so far try this
http://doc.qt.io/qt-4.8/qsqltablemodel.html/#details

[vanduongbk]

my problem is solve
thank you

[vanduongbk]

hello
i have record to database and display well on qtable
but i have a question how the lastest record data to database is the first display on table

[BalaQT]

Use order by clause

model->setQuery("SELECT start_time,description,finish_time FROM db_table [B]order by id desc[/B]");

model->setQuery("SELECT start_time,description,finish_time FROM db_table [B]order by id desc[/B]");

To copy to clipboard, switch view to plain text mode 

hope it helps,
bala

[vanduongbk]

thank you for your reply
i have question ,can you help me
i want to write a below code, but I do not know how to coding them,plz help me

............
if(warning == 0)
            {
                warning=1;
                start_time=getCurrentDateTime(); //i have write a function to get time and i call it here
                insert_to_db(start_time, "high value", "...");
                current_id= ???????
 
            }
 
        }
        else
        {
            if(warning==1)
            {
                warning=0;
                end_time=getCurrentDateTime();
                 update_to_db(end_time,current_id);//i want to update only update end time at above record where ID is ID at  insert_to_db(start_time, "high value", "...");
            }

............
if(warning == 0)
            {
                warning=1;
                start_time=getCurrentDateTime(); //i have write a function to get time and i call it here
                insert_to_db(start_time, "high value", "...");
                current_id= ???????

            }

        }
        else
        {
            if(warning==1)
            {
                warning=0;
                end_time=getCurrentDateTime();
                 update_to_db(end_time,current_id);//i want to update only update end time at above record where ID is ID at  insert_to_db(start_time, "high value", "...");
            }

To copy to clipboard, switch view to plain text mode 

and this is a update function that i write

void class::update_to_db(QString finish_time,int id)
{
    QSqlQuery query;
    query.prepare("update db_table set finish_time= :finish_time" "when id= :id");
    query.bindValue(":id",id);
    query.bindValue(":finish_time",finish_time);
    if(!query.exec())
    {
        throw QSqlError();
    }
}

void class::update_to_db(QString finish_time,int id)
{
    QSqlQuery query;
    query.prepare("update db_table set finish_time= :finish_time" "when id= :id");
    query.bindValue(":id",id);
    query.bindValue(":finish_time",finish_time);
    if(!query.exec())
    {
        throw QSqlError();
    }
}

To copy to clipboard, switch view to plain text mode