Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlQuery for ms access database
Forum Update on Monday, May 27th 2025

QSqlQuery for ms access database

Scheduled Pinned Locked Moved Solved General and Desktop
24 Posts 4 Posters 3.4k Views
  • 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.
  • B Offline
    B Offline
    behruz montazeri
    wrote on 29 Aug 2019, 18:15 last edited by
    #1

    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
    0
    • S Offline
      S Offline
      SGaist
      Lifetime Qt Champion
      wrote on 29 Aug 2019, 18:18 last edited by
      #2

      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
      0
      • B Offline
        B Offline
        behruz montazeri
        wrote on 29 Aug 2019, 18:21 last edited by behruz montazeri
        #3

        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
        0
        • S Offline
          S Offline
          SGaist
          Lifetime Qt Champion
          wrote on 29 Aug 2019, 18:24 last edited by SGaist
          #4

          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

          B 1 Reply Last reply 29 Aug 2019, 18:37
          0
          • S SGaist
            29 Aug 2019, 18:24

            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 ?

            B Offline
            B Offline
            behruz montazeri
            wrote on 29 Aug 2019, 18:37 last edited by behruz montazeri
            #5

            @sgaist
            I tested several times doesn't work.

            1 Reply Last reply
            0
            • S Offline
              S Offline
              SGaist
              Lifetime Qt Champion
              wrote on 29 Aug 2019, 19:14 last edited by
              #6

              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

              B 1 Reply Last reply 30 Aug 2019, 10:27
              0
              • S SGaist
                29 Aug 2019, 19:14

                What exactly did you test ?

                B Offline
                B Offline
                behruz montazeri
                wrote on 30 Aug 2019, 10:27 last edited by behruz montazeri
                #7

                @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
                0
                • M Offline
                  M Offline
                  MrShawn
                  wrote on 30 Aug 2019, 12:04 last edited by
                  #8

                  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.

                  B 1 Reply Last reply 30 Aug 2019, 13:20
                  2
                  • M MrShawn
                    30 Aug 2019, 12:04

                    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.

                    B Offline
                    B Offline
                    behruz montazeri
                    wrote on 30 Aug 2019, 13:20 last edited by
                    #9

                    @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 ?

                    B 1 Reply Last reply 30 Aug 2019, 13:29
                    0
                    • B behruz montazeri
                      30 Aug 2019, 13:20

                      @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 ?

                      B Offline
                      B Offline
                      behruz montazeri
                      wrote on 30 Aug 2019, 13:29 last edited by behruz montazeri
                      #10

                      @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
                      0
                      • M Offline
                        M Offline
                        MrShawn
                        wrote on 30 Aug 2019, 13:34 last edited by
                        #11

                        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 + "*");
                        
                        B 1 Reply Last reply 30 Aug 2019, 13:42
                        0
                        • M MrShawn
                          30 Aug 2019, 13:34

                          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 + "*");
                          
                          B Offline
                          B Offline
                          behruz montazeri
                          wrote on 30 Aug 2019, 13:42 last edited by
                          #12

                          @mrshawn
                          There is no error but do not work.

                          1 Reply Last reply
                          0
                          • S Offline
                            S Offline
                            SGaist
                            Lifetime Qt Champion
                            wrote on 30 Aug 2019, 15:56 last edited by
                            #13

                            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

                            B 1 Reply Last reply 30 Aug 2019, 16:45
                            0
                            • S SGaist
                              30 Aug 2019, 15:56

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

                              B Offline
                              B Offline
                              behruz montazeri
                              wrote on 30 Aug 2019, 16:45 last edited by
                              #14

                              @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
                              0
                              • S Offline
                                S Offline
                                SGaist
                                Lifetime Qt Champion
                                wrote on 30 Aug 2019, 20:06 last edited by
                                #15

                                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

                                B 1 Reply Last reply 1 Sept 2019, 10:23
                                2
                                • S SGaist
                                  30 Aug 2019, 20:06

                                  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.

                                  B Offline
                                  B Offline
                                  behruz montazeri
                                  wrote on 1 Sept 2019, 10:23 last edited by
                                  #16

                                  @sgaist
                                  I have no idea.

                                  S 1 Reply Last reply 2 Sept 2019, 06:16
                                  0
                                  • RonaldViscarraLR Offline
                                    RonaldViscarraLR Offline
                                    RonaldViscarraL
                                    wrote on 1 Sept 2019, 19:23 last edited by
                                    #17

                                    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?

                                    B 2 Replies Last reply 2 Sept 2019, 04:10
                                    0
                                    • RonaldViscarraLR RonaldViscarraL
                                      1 Sept 2019, 19:23

                                      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?

                                      B Offline
                                      B Offline
                                      behruz montazeri
                                      wrote on 2 Sept 2019, 04:10 last edited by behruz montazeri 9 Feb 2019, 04:44
                                      #18

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

                                      1 Reply Last reply
                                      0
                                      • RonaldViscarraLR RonaldViscarraL
                                        1 Sept 2019, 19:23

                                        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?

                                        B Offline
                                        B Offline
                                        behruz montazeri
                                        wrote on 2 Sept 2019, 04:40 last edited by behruz montazeri 9 Feb 2019, 04:41
                                        #19
                                        This post is deleted!
                                        1 Reply Last reply
                                        0
                                        • B behruz montazeri
                                          1 Sept 2019, 10:23

                                          @sgaist
                                          I have no idea.

                                          S Offline
                                          S Offline
                                          SGaist
                                          Lifetime Qt Champion
                                          wrote on 2 Sept 2019, 06:16 last edited by
                                          #20

                                          @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

                                          B 1 Reply Last reply 2 Sept 2019, 07:03
                                          0

                                          1/24

                                          29 Aug 2019, 18:15

                                          • Login

                                          • Login or register to search.
                                          1 out of 24
                                          • First post
                                            1/24
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • Users
                                          • Groups
                                          • Search
                                          • Get Qt Extensions
                                          • Unsolved