SelectCommand Where LIKE ? + '%'

Posted on 2005-05-15
Last Modified: 2008-02-01

I've configured a data adaptor so I can search on letters I select from a ComboBox.

I've done this before with no problems but this one I cannot get to work!!!

Theres only  4 coloumns in a table named ServiceDetails from a data base called Repairs.

Coloumn 0 is Brand
Coloumn 1 is Company
Coloumn 2 is CustomerID
Coloumn 3 is DocketNumber
Coloumn 4 is Model

If I click on the data adaptor(daService) and check properties the selectcommand commandtext looks like this

SELECT     Brand, Company, CustomerID, DocketNumber, Model
FROM         ServiceDetails
WHERE     (Company LIKE ? + '%')
ORDER BY DocketNumber DESC

if I check parameters everything looks good.

In the form load section i've writtern this

When I run the program i get this message:

"An unbalanced exception of type ' System Data.oleDB.exception' occured in "

and this line is highlighted daService.fill(dsService)

If I remove the where (Company LIKE ? + '%') from the query table then 'no problem'
but of coarse I cannot use the selectcommand.parameters statement.

Wierd thing is if I do this with another table in the same Database(Repairs) with a data adaptor daCustomers then it all works.

Everything looks identical.

Can anyone shine some light on this.

Using Access2000  and


Question by:delphimate
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Do it like this:

    SELECT     Brand, Company, CustomerID, DocketNumber, Model
    FROM         ServiceDetails
    WHERE     Company LIKE ?
    ORDER BY DocketNumber DESC

    Than give the parameter whatever value plus '%'

    Author Comment


    If I have a WHERE line with any criteria  when I preview the dataset  dsService no records load up. But if I remove the WHERE I can preview
    the dataset dsService with all the records loaded.

    LVL 75

    Expert Comment

    by:Anthony Perkins
    I am afraid, I have no idea.
    LVL 13

    Accepted Solution

    There is some kind of parameter parsing bug in SQL Server drivers. Rewrite it to a stored procedure.

    1. Create SP in isqlw.exe

    use YourDatabase
    create proc dbo.usp_TestParam (@Like varchar(256)) as
    SET @Like=@Like+'%'
    SELECT     Brand, Company, CustomerID, DocketNumber, Model
    FROM         ServiceDetails
    WHERE     Company LIKE @Like
    ORDER BY DocketNumber DESC

    2. Put the following code in your application

    EXEC dbo.usp_TestParam ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now