ACCESS 2003 SQL LIKE Operator not working in VB6 runtime


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: ACCESS 2003 SQL LIKE Operator not working in VB6 runtime

Hybrid View

  1. #1
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319

    ACCESS 2003 SQL LIKE Operator not working in VB6 runtime

    Hi All,

    I am using VB6 script via ADO objects to read ACCESS 2003 backend database using SQL String LIKE Operator. It doesn't seem to be working. I tested the SQL string under ACCESS 2003 Query mode and it's OK but not in VB6 runtime. What the Sales Manager wanted is instead of keying in the complete 8 digits SalesOrder number he wants to key in only the first 3 digits and the SQL string will extract all transactions that matches the first 3 digits and display it in LISTVIEW CONTROL where he can select the specific line item. Please help me.

    Here is the SQL string :
    SalesOrder datatype is numeric


    "Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"


    Thank you,
    Have a good day.
    Cheers,
    Lennie

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    I only have Access 2002; but it works fine there. There is nothing wrong with the SQL string. Try checking your Connection and Recordset Objects. Make sure they are ok. Also you can do a Debug.Print of your SQL String, then copy and paste it directly into a new query in SQL View. Run it, and if there are no errors and it does what you want then your SQL string is fine.
    Once you piece together your SQL string and send it to Access, it is Access that is executing it not VB. So if the Like operator is not working it is not VB, it must be your SQL syntax that Access does not like.

  3. #3
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319
    Hi Ron,
    The SQL string that I quoted ran well under ACCESS 2003 Query View but not under VB6 runtime.

    Here is the VB6 script I used to run it and the SQL did not return any data.

    Dim adoRst as new AdoDb.RecordSet
    Dim adoConn as new AdoDb.connection
    Dim strSql as string
    dim strConn as string

    strConn = "Provider=Microsoft.Jet.OleDb.4.0;" & _
    "Data Source = G:\SalesDept\SalesData.mdb;" & _
    "Persist Security Info = False"

    With AdoConn
    .connectionstring = connstr
    .open
    End with

    "Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

    adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic
    With adoRst
    if not .EOF and not .BOF then
    'display data in LISTVIEW control.
    endif
    End with

    adorst.close
    adoconn.close
    set adorst = nothing
    set adoconn = nothing


    Hope the above script will help to explain where the problem is. This is the standard script that I used in all my VB6 programs and they all worked fine except SQL LIKE Operator.


    Thanks.
    Cheers,
    Lennie

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Quote Originally Posted by Lennie
    "Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

    adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic
    Two things I noticed here:
    First is you never set the string variable to the sql statement string:
    strSsql = "Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

    Second is that ADO Recordset open options maybe defaulting to Table which only works with table names and saved query names. Try adding the adCmdText option to your open recordset options parameter:

    adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic, adCmdText

  5. #5
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319

    (resolved)

    Hi there,

    There was type error regarding my VB6 script when I typed in here instead of COPY and PASTE from my VB6 program.

    Nevertheless, I have sorted out the problem. I have use % instead of * in the LIKE operator.

    Here is the SQL string that works:-

    Eg.
    Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "%'"


    Thanks RON for your time and help.
    Cheers,
    Lennie

Similar Threads

  1. Cannot Add Then Cancel Record In VB6 & Access 2003
    By mark1110 in forum VB Classic
    Replies: 5
    Last Post: 05-07-2007, 04:31 PM
  2. trsafering access record in sql data base...
    By luca90 in forum Database
    Replies: 1
    Last Post: 01-14-2007, 07:03 AM
  3. Access or SQL??
    By Josh in forum ASP.NET
    Replies: 5
    Last Post: 07-25-2000, 12:14 PM
  4. Replies: 1
    Last Post: 05-04-2000, 09:37 AM
  5. Access 2000 automation from VB6
    By Mak Foka in forum authorevents.kurata
    Replies: 3
    Last Post: 04-18-2000, 08:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center