LIKE Operator with ODBC & Access causing pain!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: LIKE Operator with ODBC & Access causing pain!

Hybrid View

  1. #1
    A. Phillips Guest

    LIKE Operator with ODBC & Access causing pain!


    Hi,

    I'm trying to query a table on an Access database using the LIKE operator
    with wildcards within a VB app. The technique I usually use is to massage
    a 'template' query with Access via 'cut-and-paste'. I am using ADO 2.1, ODBC,
    WinNT 4(SP5), VB6 (SP3).

    I get the following errors (using Debug.Print) in VB:
    ---SQL(1)---
    SELECT ... FROM ... WHERE Languages LIKE "*COBOL*" ORDER BY ...;
    -2147217904 [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 1.

    NOTE: The above works perfectly from within Access itself, but not within
    VB.

    Changing " to ' seems to work until I try to access the recordset:
    ---SQL(2)---
    SELECT ... FROM ... WHERE Languages LIKE '*COBOL*' ORDER BY ...;
    3021 Either BOF or EOF is True, or the current record has been deleted;
    the operation requested by the application requires a current record.

    My VB code is as follows:

    Dim rsTCCSource As ADODB.Recordset 'Source recordset
    ...
    'Open a connection
    Set cnTCC = New ADODB.Connection
    cnTCC.Open "TCC"
    ...
    strSQL = ... (the above SQL statements)
    Debug.Print strSQL
    'Get the Source recordset - Static & Read Only
    Set rsTCCSource = New ADODB.Recordset
    rsTCCSource.Open strSQL, cnTCC, adOpenStatic, adLockReadOnly, adCmdText
    'ERROR here for SQL(1)
    ...
    'Loop through the Source recordset
    rsTCCSource.MoveFirst 'ERROR here for SQL(2) - no records

    Any ideas, suggestions or clues on how to overcome the above problem will
    be much appreciated. Thanks - AP.


  2. #2
    Mehmood Guest

    Re: LIKE Operator with ODBC & Access causing pain!


    hi,
    replace this "*COBOL*" with this "%COBOL%".
    Ado use % for wild cards. it is suppose to work. try it!!!!


    "A. Phillips" <adamp@absa.co.za> wrote:
    >
    >Hi,
    >
    >I'm trying to query a table on an Access database using the LIKE operator
    >with wildcards within a VB app. The technique I usually use is to massage
    >a 'template' query with Access via 'cut-and-paste'. I am using ADO 2.1,

    ODBC,
    >WinNT 4(SP5), VB6 (SP3).
    >
    >I get the following errors (using Debug.Print) in VB:
    >---SQL(1)---
    >SELECT ... FROM ... WHERE Languages LIKE "*COBOL*" ORDER BY ...;
    >-2147217904 [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    >Expected 1.
    >
    >NOTE: The above works perfectly from within Access itself, but not within
    >VB.
    >
    >Changing " to ' seems to work until I try to access the recordset:
    >---SQL(2)---
    >SELECT ... FROM ... WHERE Languages LIKE '*COBOL*' ORDER BY ...;
    > 3021 Either BOF or EOF is True, or the current record has been

    deleted;
    >the operation requested by the application requires a current record.
    >
    >My VB code is as follows:
    >
    > Dim rsTCCSource As ADODB.Recordset 'Source recordset
    >...
    > 'Open a connection
    > Set cnTCC = New ADODB.Connection
    > cnTCC.Open "TCC"
    >...
    > strSQL = ... (the above SQL statements)
    >Debug.Print strSQL
    > 'Get the Source recordset - Static & Read Only
    > Set rsTCCSource = New ADODB.Recordset
    > rsTCCSource.Open strSQL, cnTCC, adOpenStatic, adLockReadOnly, adCmdText
    > 'ERROR here for SQL(1)
    >...
    > 'Loop through the Source recordset
    > rsTCCSource.MoveFirst 'ERROR here for SQL(2) - no records
    >
    >Any ideas, suggestions or clues on how to overcome the above problem will
    >be much appreciated. Thanks - AP.
    >



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