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 Updated to NodeBB v4.3 + New Features

QSqlQuery for ms access database

Scheduled Pinned Locked Moved Solved General and Desktop
24 Posts 4 Posters 3.5k Views 1 Watching
  • 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