Link to home
Create AccountLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mike Eghtebas

ASKER

Thanks
@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.
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'
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.
Yes. I had wrong proc in my DB. How do you like use of IIF()?