Results 1 to 4 of 4

Thread: ORDER BY Sqlite in QT

  1. #1
    Join Date
    Dec 2014
    Posts
    11
    Thanks
    5
    Qt products
    Qt5
    Platforms
    Windows

    Default ORDER BY Sqlite in QT

    Hi again (unfortunately for me XD)
    First of all, thx for help with my proxyProblem

    Well, In this time, I created a sqlite table and insert things, and works, but, i have a problem with ORDER BY, this doesn't sort, and i don't know the reason ...

    here is the code

    mainwindow.h
    Qt Code:
    1. #ifndef MAINWINDOW_H
    2. #define MAINWINDOW_H
    3.  
    4. #include <QMainWindow>
    5. #include <QtSql/QSqlDatabase>
    6. #include <QtSql/QSqlQuery>
    7. #include <QtSql/QSqlError>
    8.  
    9. namespace Ui {
    10. class MainWindow;
    11. }
    12.  
    13. class MainWindow : public QMainWindow
    14. {
    15. Q_OBJECT
    16.  
    17. public:
    18. explicit MainWindow(QWidget *parent = 0);
    19. ~MainWindow();
    20.  
    21. void crearTablaUsuarios();
    22. void insertarUsuario();
    23. void mostrarDatos();
    24.  
    25. private slots:
    26. void on_pushButtonAgregar_clicked();
    27.  
    28. void on_pushButton_clicked();
    29.  
    30. private:
    31. Ui::MainWindow *ui;
    32. };
    33.  
    34. #endif // MAINWINDOW_H
    To copy to clipboard, switch view to plain text mode 

    and my mainwindow.cpp
    Qt Code:
    1. #include "mainwindow.h"
    2. #include "ui_mainwindow.h"
    3. #include <QDebug>
    4.  
    5. MainWindow::MainWindow(QWidget *parent) :
    6. QMainWindow(parent),
    7. ui(new Ui::MainWindow)
    8. {
    9. ui->setupUi(this);
    10. qDebug()<< "Aplicacion iniciada...";
    11. QString nombre;
    12. nombre.append("BaseDeDatos1.sqlite");
    13. db = QSqlDatabase::addDatabase("QSQLITE");
    14. db.setDatabaseName(nombre);
    15. if (db.open()){
    16. qDebug()<< "Se ha conectado con EXITO la Base de Datos.";
    17. }
    18. else{
    19. qDebug()<< "No se ha conectado con EXITO la Base de Datos.";
    20. }
    21. crearTablaUsuarios();
    22. mostrarDatos();
    23. }
    24.  
    25. MainWindow::~MainWindow()
    26. {
    27. delete ui;
    28. }
    29.  
    30. void MainWindow::crearTablaUsuarios()
    31. {
    32. QString consulta;
    33. consulta.append("CREATE TABLE IF NOT EXISTS usuarios("
    34. "id INTEGER PRIMARY KEY AUTOINCREMENT,"
    35. "nombre VARCHAR(100),"
    36. "apellido VARCHAR(100),"
    37. "edad INTEGER NOT NULL,"
    38. "clase INTEGER NOT NULL"
    39. ");");
    40. QSqlQuery crear;
    41. crear.prepare(consulta);
    42. if(crear.exec()){
    43. qDebug()<< "La tabla USUARIOS existe o se ha creado correctamente.";
    44. }
    45. else{
    46. qDebug()<< "La tabla USUARIOS NO se ha creado correctamente o no existe.";
    47. qDebug()<< "ERROR!"<< crear.lastError();
    48. }
    49. }
    50.  
    51. void MainWindow::insertarUsuario()
    52. {
    53. QString consulta;
    54. consulta.append("INSERT INTO usuarios("
    55. "nombre,"
    56. "apellido,"
    57. "edad,"
    58. "clase)"
    59. "VALUES("
    60. "'"+ui->lineEditNombre->text()+"',"
    61. "'"+ui->lineEditApellido->text()+"',"
    62. ""+ui->lineEditEdad->text()+","
    63. ""+ui->lineEditClase->text()+""
    64. ");");
    65. QSqlQuery insertar;
    66. insertar.prepare(consulta);
    67. if(insertar.exec()){
    68. qDebug()<< "El USUARIO se ha insertado correctamente.";
    69. }
    70. else{
    71. qDebug()<< "El USUARIO NO se ha insertado correctamente.";
    72. qDebug()<< "ERROR!"<< insertar.lastError();
    73. }
    74. }
    75.  
    76. void MainWindow::mostrarDatos()
    77. {
    78. QString consulta;
    79. consulta.append("SELECT * FROM usuarios");
    80. QSqlQuery consultar;
    81. consultar.prepare(consulta);
    82. if(consultar.exec()){
    83. qDebug()<< "Se ha consultado correctamente.";
    84. }
    85. else{
    86. qDebug()<< "NO se ha consultado correctamente.";
    87. qDebug()<< "ERROR!"<< consultar.lastError();
    88. }
    89.  
    90. int fila = 0;
    91.  
    92. ui->tableWidgetDatos->setRowCount(fila);
    93.  
    94. while (consultar.next()){
    95. ui->tableWidgetDatos->insertRow(fila);
    96. ui->tableWidgetDatos->setItem(fila, 0, new QTableWidgetItem(consultar.value(1).toByteArray().constData()));
    97. ui->tableWidgetDatos->setItem(fila, 1, new QTableWidgetItem(consultar.value(2).toByteArray().constData()));
    98. ui->tableWidgetDatos->setItem(fila, 2, new QTableWidgetItem(consultar.value(3).toByteArray().constData()));
    99. ui->tableWidgetDatos->setItem(fila, 3, new QTableWidgetItem(consultar.value(4).toByteArray().constData()));
    100. fila++;
    101. }
    102. }
    103.  
    104. void MainWindow::on_pushButtonAgregar_clicked()
    105. {
    106. insertarUsuario();
    107. mostrarDatos();
    108. }
    109.  
    110. void MainWindow::on_Sort_clicked()
    111. {
    112. QString consulta;
    113. consulta.append("SELECT * FROM usuarios ORDER BY clase ASC;");
    114. QSqlQuery ordenar;
    115. ordenar.prepare(consulta);
    116. ordenar.exec();
    117. if (ordenar.exec()){
    118. qDebug()<< "Se ha ordenado";
    119. }
    120. else{
    121. qDebug()<< "La Cagaste";
    122. qDebug()<< "ERROR!"<< ordenar.lastError();
    123. }
    124. mostrarDatos();
    125. }
    To copy to clipboard, switch view to plain text mode 

    My problem is here
    Qt Code:
    1. void MainWindow::on_Sort_clicked()
    2. {
    3. QString consulta;
    4. consulta.append("SELECT * FROM usuarios ORDER BY nombre ASC;");
    5. QSqlQuery ordenar;
    6. ordenar.prepare(consulta);
    7. ordenar.exec();
    8. if (ordenar.exec()){
    9. qDebug()<< "Se ha ordenado";
    10. }
    11. else{
    12. qDebug()<< "La Cagaste";
    13. qDebug()<< "ERROR!"<< ordenar.lastError();
    14. }
    15. mostrarDatos(); //this function show the table
    16. }
    To copy to clipboard, switch view to plain text mode 

    This doesn't throw error, work perfectly, but don't sort ...

    Thx for help me again

  2. #2
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: ORDER BY Sqlite in QT

    Quote Originally Posted by Koch View Post
    Well, In this time, I created a sqlite table and insert things, and works, but, i have a problem with ORDER BY, this doesn't sort, and i don't know the reason ...
    You are executing your SELECT query twice. Once in MainWindow::on_Sort_clicked with an ORDER BY clause, but in your function MainWindow::mostrarDatos(), you are re-executing the query without the ORDER by clause.

    On another note, don't use concatenation when building your INSERT SQL statement. This leaves you exposed to SQL injections and you should use either named or positional query parameters, prepare the SQL statement, then QSqlQuery::bindValue to dynamically associate values to the named or positional query parameters.

    Hope that helps,

    Jeff
    Last edited by jefftee; 24th December 2014 at 08:08.

  3. The following user says thank you to jefftee for this useful post:

    Koch (24th December 2014)

  4. #3
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,014 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: ORDER BY Sqlite in QT

    The query is executed three times. There are two exec statements in on_Sort_clicked.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  5. The following user says thank you to wysota for this useful post:

    Koch (24th December 2014)

  6. #4
    Join Date
    Dec 2014
    Posts
    11
    Thanks
    5
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: ORDER BY Sqlite in QT

    Ohh thank you both, i didn't know this. Now this work perfectly, I'm so grateful, thank you so much !

Similar Threads

  1. Replies: 3
    Last Post: 28th September 2012, 14:01
  2. [Qt][SQLite] Two problems with SQLite.
    By Xandareva in forum Newbie
    Replies: 6
    Last Post: 6th April 2010, 23:06
  3. style order
    By wirasto in forum Qt Programming
    Replies: 1
    Last Post: 23rd January 2010, 10:40
  4. about tab order
    By hesummar in forum Qt Programming
    Replies: 7
    Last Post: 16th November 2006, 05:45
  5. TAB order on form
    By igor_x in forum Newbie
    Replies: 3
    Last Post: 10th November 2006, 05:29

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.