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.
Here is where you can download the app itself (8.1 MB): https://onedrive.live.com/?cid=420CDD6A13807C9B&id=420CDD6A13807C9B%21116
PartialSearch.png
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
Here is where you can download the app itself (8.1 MB): https://onedrive.live.com/?cid=420CDD6A13807C9B&id=420CDD6A13807C9B%21116
PartialSearch.png
.NET ProgrammingVisual Basic.NETC#
Last Comment
Mike Eghtebas
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
ASKER
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
-- exec spSoftwareSearch '', '.9'
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
-- 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.
The problem was thus not in your code, it is in the design of the database.
ASKER
Yes. I had wrong proc in my DB. How do you like use of IIF()?