• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

SelectCommand Where LIKE ? + '%'

Hi,


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
daService.fill(dsService)

When I run the program i get this message:

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

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 VB.net


thanks
Bruce


0
delphimate
Asked:
delphimate
  • 2
1 Solution
 
Anthony PerkinsCommented:
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 '%'
0
 
delphimateAuthor Commented:
Hi,

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.

0
 
Anthony PerkinsCommented:
I am afraid, I have no idea.
0
 
ispalenyCommented:
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
GO
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
GO

2. Put the following code in your application

EXEC dbo.usp_TestParam ?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now