Results 1 to 30 of 30

Thread: vb6 database query sql contains like operator fail to work in Window 11?

  1. #1

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    vb6 database query sql contains like operator fail to work in Window 11?

    A sql of my vb6 application that fetch Microsoft access database (.mdb) data is a filter using the like operators using DAO/ADO.
    As a simple example:
    select * from Product where Description like '%shipped%' order by Seq Desc
    That runs correctly in Window 8 but fail to work in Window 11 with error similar to following:
    -2147217900 (80040e14) out of memory
    Anyone can help testing it and see how that can be fixed? In the worst case see if there can be any walk around solving the problem?
    The Window 11 computer has 16GB RAM.

  2. #2

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    The sql query problem mentioned above is solved after sp6 is installed.

  3. #3
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,344

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Then install SP6, that should really not be a problem on W10 and W11

  4. #4

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    As I finally checked, after getting sp6 installed now, the query contain like operator runs successfully in the IDE but fails if it is compiled (i.e. the exe file ) . Any suggestion to solve this?
    I am trying to use the package and deployment to solve this. But the wizard does not work too.
    ( the exe file fails in Window 11 only, not Window 10 )

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,344

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Do you use ADO or DAO in your application?

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,349

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    And since it's MS Access the wildcard should be "*" and not "%"
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Zvoni View Post
    And since it's MS Access the wildcard should be "*" and not "%"
    It is using ADO.
    As coded in the source, the sql statement is the same in both Window 10(work) and Window 11(not work).

  8. #8

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Zvoni View Post
    And since it's MS Access the wildcard should be "*" and not "%"
    To correct it, the sql query statement with ADO should be using % as wild card.
    You need to change it to * if you test run it in MS Access.

    Someone may help do a simple test with both IDE and compiled exe, for example,
    select * from Product where Description like '%shipped%' order by Seq Desc

  9. #9
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,344

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    What does your connection string look like?

  10. #10

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Arnoutdv View Post
    What does your connection string look like?
    Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:\myAcessMdb.mdb;"

    ' this test if the like operator in sql cannot be used in Window 11
    sql = "select * from _Product where description like '%city%' Order by Seq"

    If the sql does not have the like operator, it runs fine.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,349

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Just saw it: Since when do you use single quotes in Jet-SQL?
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    See if it is possible to file a bug fix if it is Microsoft fault as they need to support the compiled exe.

  13. #13
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,344

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    If it's really bug then it should be seen everywhere.

    Can post a sample project which shows this problem?
    Including a small MDB file.

  14. #14
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,431

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Zvoni View Post
    Just saw it: Since when do you use single quotes in Jet-SQL?
    AFAIK it always used single quotes.

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,924

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    How do you know it's the LIKE operator that's failing? What's the error message you get?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,344

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    That runs correctly in Window 8 but fail to work in Window 11 with error similar to following:
    -2147217900 (80040e14) out of memory
    As I finally checked, after getting sp6 installed now, the query contain like operator runs successfully in the IDE but fails if it is compiled (i.e. the exe file ) . Any suggestion to solve this?
    I am trying to use the package and deployment to solve this. But the wizard does not work too.
    ( the exe file fails in Window 11 only, not Window 10 )
    Quite strange that it does work from the IDE, but not from the compiled project.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,349

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by techgnome View Post
    How do you know it's the LIKE operator that's failing? What's the error message you get?

    -tg
    From Post 10:
    If the sql does not have the like operator, it runs fine.
    Well then, as a last guess:: SELECT * FROM blablabla
    Do we know what he tries to pull in using "SELECT *"?
    Maybe some BLOB-Columns choking everything.
    It still wouldn't explain his claim, that it works with Win8/10, but not Win11

    Ah, well.... i'm really glad i ditched Windows 5 years ago....
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,924

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Zvoni View Post
    From Post 10:


    Well then, as a last guess:: SELECT * FROM blablabla
    Do we know what he tries to pull in using "SELECT *"?
    Maybe some BLOB-Columns choking everything.
    It still wouldn't explain his claim, that it works with Win8/10, but not Win11

    Ah, well.... i'm really glad i ditched Windows 5 years ago....
    That's why I want to know what the error is ... something doesn't smell right.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  19. #19

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Arnoutdv View Post
    If it's really bug then it should be seen everywhere.

    Can post a sample project which shows this problem?
    Including a small MDB file.

    I am going to make a short version for you all to test later.

    The error code is
    -2147217900 (80040e14) that may means 'out of memory'

    > How do you know it's the LIKE operator that's failing? What's the error message you get?

    When the sql does not have a like operator in it, it works fine. For example, when there is not a where clause or the where clause contains = .

    > Quite strange that it does work from the IDE, but not from the compiled project.

    That is why I immediately assume everything works fine after installed sp6.

    > Maybe some BLOB-Columns choking everything.

    These are completely simple fields, for example dates and text strings

  20. #20
    PowerPoster
    Join Date
    Jun 2013
    Posts
    6,192

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Arnoutdv View Post
    Quite strange that it does work from the IDE, but not from the compiled project.
    The IDE is run (usually) "as Administrator" - whereas the compiled project is not.
    Maybe the "normal UserAccount" has different locale- or registry-settings or something.

    And/Or ... it could be "Folder-Virtualization" which plays a role here
    (e.g. when Exe and DB are located behind C:\Programs x86\...)

    Olaf

  21. #21

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    I have made a short version for testing.
    Running the compiled exe does not cause error of any sql using the like operator in this small sample.
    https://drive.google.com/file/d/1zez...ew?usp=sharing

    However, the table of my application has more than 20000 rows. After deleting many rows for testing, the sql that have the Like operator does successfully run in the compiled exe

    In summary,
    1. the sql with a like operator can run in IDE even if it has many rows
    2. the sql with a like operator fail in the compiled exe if there are many rows, as a result, causing out of memory problem
    3. the sql with a like operator can run if there are NOT many rows.
    Obviously, there does exist some kind of bug.
    ( That can also be tested using my short version, providing that you have a table that has many many rows. )

  22. #22
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,431

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by LeonChan View Post
    providing that you have a table that has many many rows.
    Are you joking?

  23. #23

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Eduardo- View Post
    Are you joking?
    Perhaps this time you can create test data of more than 20000 rows and test it yourself.

  24. #24
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,349

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    20,000 rows?
    that‘s ……. cute…..
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  25. #25
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,431

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by LeonChan View Post
    Perhaps this time you can create test data of more than 20000 rows and test it yourself.
    Do you realize that people trying to help here want to have the test project in the condition that produces the error already?

    Perhaps you can provide the test project with the database with the 20000 registers.

  26. #26

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by Eduardo- View Post
    Do you realize that people trying to help here want to have the test project in the condition that produces the error already?

    Perhaps you can provide the test project with the database with the 20000 registers.
    That will be possible. Just load it with dummy data and test it later.

  27. #27
    Addicted Member
    Join Date
    Jan 2018
    Posts
    217

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Add line numbers before each statement and print the value of Erl in your error handler to see exactly which line it is failing on.

  28. #28

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Not yet to produce the error as my table using simple dummy data yet.

    As I check, I use a Like operator to search for text on a memo field ( description / remark ) which can each contain a short article of more than hundred of words. So that can consume a lot of memory, may be.

    So I will continue the check.

  29. #29

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by ahenry View Post
    Add line numbers before each statement and print the value of Erl in your error handler to see exactly which line it is failing on.
    Doing what you suggest, the error happens at the line
    110: myRs.Open sql, MyCn, adOpenDynamic, adLockOptimistic, adCmdText

    Also, when tested the Like operator with other non-memo fields, there showed no error. Finally, I replaced the data on the ProductName field of the whole table with a sample dummy data, and found no errors again.
    So, it is possible that the production data in my database table associated with the Like operator that cause the error.

  30. #30
    Addicted Member
    Join Date
    Jan 2018
    Posts
    217

    Re: vb6 database query sql contains like operator fail to work in Window 11?

    Quote Originally Posted by LeonChan View Post
    Doing what you suggest, the error happens at the line
    110: myRs.Open sql, MyCn, adOpenDynamic, adLockOptimistic, adCmdText

    Also, when tested the Like operator with other non-memo fields, there showed no error. Finally, I replaced the data on the ProductName field of the whole table with a sample dummy data, and found no errors again.
    So, it is possible that the production data in my database table associated with the Like operator that cause the error.
    Yes, it sounded like a data-specific issue from your initial description. Did your Windows 8 and Windows 11 platforms also use different data sets? If not, the other thing I'd check would be the version of the Jet driver on the two machines.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width