LIKE Operator with ODBC & Access is causing pain.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: LIKE Operator with ODBC & Access is causing pain.

Hybrid View

  1. #1
    Adam Phillips Guest

    LIKE Operator with ODBC & Access is 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
    Erwin Guest

    Re: LIKE Operator with ODBC & Access is causing pain.

    Hi
    If You are using ADO then see example
    change the asterisk into %

    SELECT ... FROM ... WHERE Languages LIKE "%COBOL%" ORDER BY ...;

    Erwin

    "Adam Phillips" <adamp@absa.co.za> schreef in bericht
    news:39bf9c4a$1@news.devx.com...
    >
    > 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.




  3. #3
    Adam Phillips Guest

    Re: LIKE Operator with ODBC & Access is causing pain.


    Thanks - It works!
    Adam.

    "Erwin" <erwin.christiaens@pandora.be> wrote:
    >Hi
    >If You are using ADO then see example
    >change the asterisk into %
    >
    >SELECT ... FROM ... WHERE Languages LIKE "%COBOL%" ORDER BY ...;
    >
    >Erwin
    >



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