How to insert data to table from another table - SQL in QT

[Voobys]

I have task to make two tables, insert data with QUI and after this insert column Number from house and Consumption from water to list table
I created a three tables:

query.exec("create table water(id int primary key, Hydrometer varchar(20), Date varchar(20), Consumption varchar(20))");
query.exec("create table house(id int primary key, Owner varchar(20), Number varchar(20), Hydrometer varchar(20))");
query.exec("create table list(id int primary key, House varchar(20), Consumption varchar(20))");
query.exec(

I insert data to table this way:

query.exec("insert into vodomer (id, Hydrometer, Date, Consumption) values ('"+QString::number(idhydrometer)+"', '"+number_hydrometer+"', '"+date+"', '"+consumption+"')");

I cant figure out how to insert data from one table to another table .

Could you help me please?

[arsinte_andrei]

@Voobys said in How to insert data to table from another table - SQL in QT:

I cant figure out how to insert data from one table to another table .

what do you mean by that? do you want to read first that table and the process the second table or you have a relational table and do you want the data to be linked together??

[arsinte_andrei]

here you have a function that I've made to insert data into a table

QSqlQuery ATP_db::atpInsert(QString tableName, QMap<QString, QVariant> *data ) {
	QString fieldNames, fieldValues, temp;
	QMap<QString, QVariant>::const_iterator i1;
	for (i1 = data->constBegin(); i1 != data->constEnd(); ++i1) {
		temp = i1.key();
		fieldValues += temp + ", ";
		fieldNames += temp.remove(0,1) + ", ";
	}
	fieldNames.remove(fieldNames.lastIndexOf(","),1);
	fieldValues.remove(fieldValues.lastIndexOf(","),1);
//	qDebug() << fieldNames << "** - **" << fieldValues;

	QSqlQuery myQuerry;
	myQuerry.prepare("INSERT INTO " + tableName + " ( " + fieldNames + ") VALUES ( " + fieldValues + ")");
//	qDebug() << data;
	QMap<QString, QVariant>::const_iterator i;
	for (i = data->constBegin(); i != data->constEnd(); ++i) {
		myQuerry.bindValue(i.key(), i.value());
		//myQuerry.bindValue(i.key().toUtf8().data(), i.value().toString().toUtf8().data());
	}
//	qDebug() << myQuerry.boundValues();
	myQuerry.exec();
//	qDebug() <<  myQuerry.lastQuery() <<myQuerry.lastError();
	return myQuerry;
}

I pass the data as a QMap in form of QStrings - field Name - colomn name and then QVarian - is the actual data that has to be written into the column
Hope that helps..

[Voobys]

Sorry... I can insert data to the table.

I dont know how take data from table water and from table house and insert to the table list. Because when I tried it this way, it didnt work:

        query.prepare("insert into list(Consumption) values ('"+consumption+"') from water(Consumption))");

I just dont know how to take data from table and insert them to the another table...

[arsinte_andrei]

hm... first you have to read the data from water consumption after you read the data and store it in some variables then you can write it where you want...
first read it.. and then write it back... and pay attention to the column names and table names... they might differ and if not then edit them to differ in order to avoid confusion

my standard in this is to table name become tbl_name
eg:

tbl_user

tc_user_id
tc_user_username
........

at least you know what you are referring to and where you read the data from and where you write the data to...

[arsinte_andrei]

@Voobys said in How to insert data to table from another table - SQL in QT:

Sorry... I can insert data to the table.

post a piece of code bigger in order to understand better what are you trying to do - eventually some data structure of your tables and how do they relate to one another....

[Voobys]

@arsinte_andrei

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
{
ui->setupUi(this);
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(QFileDialog::getOpenFileName(this, "open"));
db.open();

ui->actionLogin->triggered();

}

MainWindow::~MainWindow()
{
delete ui;

}

void MainWindow::on_actionVytvo_it_datab_zi_triggered()
{

db= QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(QFileDialog::getSaveFileName(this,"save"));
db.open();
QSqlQuery query;
query.exec("create table water(id int primary key, Hydrometer varchar(20), Date varchar(20), Consumption varchar(20))");
query.exec("create table house(id int primary key, Owner varchar(20), House varchar(20), Hydrometer varchar(20) )");
query.exec("create table owner(id int primary key, FName varchar(20), LName varchar(20), username varchar(20), password  varchar(20))");
query.exec("create table list(id int primary key, House varchar(20), Consumption varchar(20))");

}

void MainWindow::on_actionaddhouse_triggered()
{
idhouse++;
DialogChata o;
o.exec();

owner= o.Owner();
number_house = QString::number(o.Number_house());
number_hydrometer= QString::number(o.Cislo_vodomeru2());


QSqlQuery query;
query.exec("insert into house(id, Owner, House, Hydrometer) values ('"+QString::number(idhouse)+"', '"+owner+"', '"+number_house+"', '"+number_hydrometer+"')");
query.exec("insert into list(id,House values ('"+QString::number(idhouse)+"')");
query.prepare("select * from house");
query.exec();
model->setQuery(query);
ui->tableView->setModel(model);

}

void MainWindow::on_actionaddowner_triggered()
{
idowner++;
DialogMajitel m;
m.exec();

fname= m.Fname();
lname= m.Lname();
username = m.Username();
password = m.Password();

QSqlQuery qeury;
qeury.exec("insert into owner(id,  FName, LName, username, password) values ('"+QString::number(idowner)+"', '"+fname+"', '"+lname+"', '"+username+"', '"+password+"')");
qeury.prepare("select * from owner");
qeury.exec();
model->setQuery(qeury);
ui->tableView->setModel(model);

}

void MainWindow::on_actionView_Owner_triggered()
{
QSqlQuery query;
query.exec("select * from owner");
query.exec();
model->setQuery(query);
ui->tableView->setModel(model);
}

void MainWindow::on_actionView_House_triggered()
{
QSqlQuery query;
query.exec("select * from house");
query.exec();
model->setQuery(query);
ui->tableView->setModel(model);
}

void MainWindow::on_actionOpenDB_triggered()
{
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(QFileDialog::getOpenFileName(this, "open"));
db.open();
}

void MainWindow::on_actionaddhydrometer_triggered()
{
idhydrometer++;
DialogVodomer v;
v.exec();

number_hydrometer= QString::number(v.Number_hydrometer());
date= v.date();
consumption= QString::number(v.Consuption());

QSqlQuery query;
query.exec("insert into hydrometer(id, Hydrometer, Date, Consuption) values ('"+QString::number(idhydrometer)+"', '"+number_hydrometer+"', '"+date+"', '"+consuption+"')");
query.exec("insert into prumer(Consuption) values ('"+consuption+"')");
query.prepare("select * from hydrometer");
query.exec();
model->setQuery(query);
ui->tableView->setModel(model);

}

void MainWindow::on_actionViewHydrometer_triggered()
{
QSqlQuery query;
query.prepare("select * from hydrometer");
query.exec();
model->setQuery(query);
ui->tableView->setModel(model);
}

void MainWindow::on_actionLogin_triggered()
{
DialogLogin l;
l.exec();

username = l.Username();
password = l.Password();

QSqlQuery query;
if(query.exec("select * from owner where username ='"+username+"' and password = '"+password+"'"))
{
    int count=0;
    while(query.next())
    {
        count++;
    }
    if(count == 1)
    {
        
        
        ui->statusBar->showMessage("Succesful");
        QSqlQuery query;
       // here I want to call that code - If it is succesfully login so open a new dialog with a list with house and consuption
        //query.prepare("select * from list");
        query.exec();
        model->setQuery(query);
        ui->tableView->setModel(model);
    }
        if(count > 1)
        {
            ui->statusBar->showMessage("Duplicate");
        }
        if(count <1)
        {
            ui->statusBar->showMessage("Wrong");
        }

}

}

[arsinte_andrei]

@Voobys said in How to insert data to table from another table - SQL in QT:

"create table water(id int primary key, Hydrometer varchar(20), Date varchar(20), Consumption varchar(20))"

I'm thinking that this suppose to be like

"CREATE TABLE tbl_water(tc_water_id integer	PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, tc_water_hydrometer VARCHAR(20), tc_water_date VARCHAR(20), tc_water_consumption VARCHAR(20));"

also, I see that you store the password unencrypted which is not good at all - you should think about it
does you database exist?? is it created?? can you access it with a SQLite database editor??

also, the list is not better if it's a view??
something like

QString mtView = " "
		"CREATE VIEW IF NOT EXISTS tv_myView "
		"AS "
		"SELECT tc_house_id.tbl_house, tc_house_house.tbl_house"
		"FROM tbl_house "
			 "LEFT OUTER JOIN tbl_water; ";

but your tables need to be edited as there are no links between them.. you have to set some keys for this to work,, basicly this is SQL and not C++ or Qt