Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Qt Academy Launch in California!

    Solved QSqlQuery for ms access database

    General and Desktop
    4
    24
    1153
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • behruz montazeri
      behruz montazeri last edited by

      I wrote this code for sqlite database i need same query for ms access database

          QSqlQuery query("SELECT * FROM names where name like '%' || ? || '%'");
          query.addBindValue(QString("%1%").arg(st));
      

      How could i change it to work for ms access ?

      1 Reply Last reply Reply Quote 0
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        Hi,

        Before changing the query, are you already connected to the Access database ?

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 0
        • behruz montazeri
          behruz montazeri last edited by behruz montazeri

          Yes it works on other queries.
          I get this error :

          QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC Driver Manager] Function sequence error"
          
          1 Reply Last reply Reply Quote 0
          • SGaist
            SGaist Lifetime Qt Champion last edited by SGaist

            Did you check the error returned by the query ?

            I must say that it doesn't look correct but my SQL is a bit rusty.

            Shouldn't the percent signes be directly doubled ?

            Interested in AI ? www.idiap.ch
            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

            behruz montazeri 1 Reply Last reply Reply Quote 0
            • behruz montazeri
              behruz montazeri @SGaist last edited by behruz montazeri

              @sgaist
              I tested several times doesn't work.

              1 Reply Last reply Reply Quote 0
              • SGaist
                SGaist Lifetime Qt Champion last edited by

                What exactly did you test ?

                Interested in AI ? www.idiap.ch
                Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                behruz montazeri 1 Reply Last reply Reply Quote 0
                • behruz montazeri
                  behruz montazeri @SGaist last edited by behruz montazeri

                  @sgaist
                  I changed the code to at least get it work :

                      QSqlQuery  query ;
                      query.prepare("SELECT * FROM names where name like ? ");
                      query.addBindValue(st);
                  

                  my intention is searching everything from begin of the word or everywhere.
                  The query should be like this :

                  *?*
                  

                  But it doesn't work
                  Error :

                  Syntax error (missing operator) in query expression 'name like '*' Pa_RaM000 '*''."
                  
                  1 Reply Last reply Reply Quote 0
                  • M
                    MrShawn last edited by

                    MS Access query syntax is different from t-sql. I would suggest using MS access itself and build your queries there, then take that as the baseline for your program's query strings.

                    behruz montazeri 1 Reply Last reply Reply Quote 2
                    • behruz montazeri
                      behruz montazeri @MrShawn last edited by

                      @mrshawn
                      I want something like this :

                      SELECT * FROM names where name Like "*"&[, the text that you want to use as a prompt, and then ]&"*"
                      

                      How should i write in query.prepare ?

                      behruz montazeri 1 Reply Last reply Reply Quote 0
                      • behruz montazeri
                        behruz montazeri @behruz montazeri last edited by behruz montazeri

                        @behruz-montazeri

                        query.prepare("SELECT * FROM names where name Like '*'&[ st ]&'*' "); 
                        query.addBindValue(st);
                        
                        [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
                        

                        What should i write to pass a parameter ?

                        1 Reply Last reply Reply Quote 0
                        • M
                          MrShawn last edited by

                          Try changing

                          query.prepare("SELECT * FROM names where name Like '*'&[ st ]&'*' ");
                          

                          to

                          QString st = "something";
                          query.prepare("SELECT * FROM names where name Like :st"); 
                          query.bindValue(":st", "*" + st + "*");
                          
                          behruz montazeri 1 Reply Last reply Reply Quote 0
                          • behruz montazeri
                            behruz montazeri @MrShawn last edited by

                            @mrshawn
                            There is no error but do not work.

                            1 Reply Last reply Reply Quote 0
                            • SGaist
                              SGaist Lifetime Qt Champion last edited by

                              Can you write an example query that currently works for that use case ?

                              Interested in AI ? www.idiap.ch
                              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                              behruz montazeri 1 Reply Last reply Reply Quote 0
                              • behruz montazeri
                                behruz montazeri @SGaist last edited by

                                @sgaist
                                As i mentioned this one :

                                    QSqlQuery  query ;
                                    query.prepare("SELECT * FROM names where name like ? ");
                                    query.addBindValue(st);
                                

                                It works but i should write entire word i want to write a part of record in my case name record and it update QTableView

                                1 Reply Last reply Reply Quote 0
                                • SGaist
                                  SGaist Lifetime Qt Champion last edited by

                                  What I am asking for is an example without binding that is working. Once we have that it should be easier to come with a solution.

                                  Interested in AI ? www.idiap.ch
                                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                  behruz montazeri 1 Reply Last reply Reply Quote 2
                                  • behruz montazeri
                                    behruz montazeri @SGaist last edited by

                                    @sgaist
                                    I have no idea.

                                    SGaist 1 Reply Last reply Reply Quote 0
                                    • RonaldViscarraL
                                      RonaldViscarraL last edited by

                                      Hi, the use of % instead of * is the right way. The * is only for using it directly on ms access query.

                                      WHat version of ms access are you using? could you post a screenshot of your database/table?

                                      behruz montazeri 2 Replies Last reply Reply Quote 0
                                      • behruz montazeri
                                        behruz montazeri @RonaldViscarraL last edited by behruz montazeri

                                        @ronaldviscarral
                                        Thanks for your response here is my screenshot :
                                        image Info
                                        link imaget

                                        1 Reply Last reply Reply Quote 0
                                        • behruz montazeri
                                          behruz montazeri @RonaldViscarraL last edited by behruz montazeri

                                          This post is deleted!
                                          1 Reply Last reply Reply Quote 0
                                          • SGaist
                                            SGaist Lifetime Qt Champion @behruz montazeri last edited by

                                            @behruz-montazeri said in QSqlQuery for ms access database:

                                            @sgaist
                                            I have no idea.

                                            Something like "SELECT * FROM names where name like '%foo%'" to confirm that one is working properly.

                                            Interested in AI ? www.idiap.ch
                                            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                            behruz montazeri 1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post