untyped dataset, partial search, vb.net

As shown on the attached image, there are wildcard searches for:

1. Title (works fine).
2. Unit Price (works only for exact price not partial digits)

Question: How can I modify this vb.net sample project to allow partial searches work as well?

FYI, spSoftwareSearch works for exact or partial searches in SSMS . Meaning, the proc is okay. The focus of my question is on the application side.

Also, this sample is using untyped dataset. I will be happy to upload this small application for you to take a look at it. The application includes a small database set to work with SQL Express12.

ALTER PROCEDURE [dbo].[spSoftwareSearch]
     @Title varchar(30)
    , @UnitPrice money

As
BEGIN

SELECT 
    s.SoftwareID
	, s.Title
	, s.Description
	, s.UnitPrice 
FROM tblSoftware s 
WHERE s.Title  Like iif(@Title='', s.Title, '%' + @Title + '%') And
     s.UnitPrice = iif(@UnitPrice = 0,  s.UnitPrice, @UnitPrice)

END 

Open in new window


Here is where you can download the app itself (8.1 MB): https://onedrive.live.com/?cid=420CDD6A13807C9B&id=420CDD6A13807C9B%21116
PartialSearch.png
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
The most likely reason is that the database data type for UnitPrice is set to money which in .Net code translate to decimal type and you are attempting to do an alphanumeric search on a object that is NOT a string.

Either change the Database type of UnitPrice to String or don't go back to the database to do the filtering of the data table and do it locally.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks
0
Jacques Bourgeois (James Burger)PresidentCommented:
@Fernando.

I do not think that changing the database type because you have a specific need in the application is the right thing to do. You might need to use it a number somewhere else in the application or in another application.

The type of a field in a database should be the best type for the type of data it will record. It's the role of the application to  adapt to the database structure, not the reverse. This is one of the reasons so many programmers have problems with dates. They store them as strings. It might work for the application they were developing along the database. But another application or environment will break with that approach. A date should be kept in a date field, a numerical value should be kept in a numeric field.
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi C# masters,

It turns out that I had problem with my proc. I have used the following to search or unit prices like:

9.9  
2
59.95
empty cell
etc.

But .9 would work but not 0.9 the application should change it to .9

ALTER PROCEDURE [dbo].[spSoftwareSearch]
     @Title varchar(30)
    , @UnitPrice  varchar(10)

As
BEGIN

SELECT
    s.SoftwareID
        , s.Title
        , s.Description
        , s.UnitPrice
FROM tblSoftware s
WHERE s.Title  Like iif(@Title='', s.Title, '%' + @Title + '%') And
     cast(s.UnitPrice as varchar(10)) Like iif(@UnitPrice = '',
cast(s.UnitPrice as varchar(10)) , '%' + @UnitPrice + '%')

END

Open in new window


-- exec spSoftwareSearch '', '.9'
0
Jacques Bourgeois (James Burger)PresidentCommented:
This is exactly the type of problem that you have when you deal with numeric values as a string (varchar). It is treated as a series of characters ("0.9" is not the same as ".9")  instead of being treated as a number (where 0.9 and 9 are the same thing). Is the price of an item a word or a number?

The problem was thus not in your code, it is in the design of the database.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Yes. I had wrong proc in my DB. How do you like use of IIF()?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.