Solved

untyped dataset, partial search, vb.net

Posted on 2014-12-12
6
391 Views
Last Modified: 2014-12-13
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
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40497977
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40498113
Thanks
0
 
LVL 40
ID: 40498207
@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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40498249
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
 
LVL 40
ID: 40498337
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40498598
Yes. I had wrong proc in my DB. How do you like use of IIF()?
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

696 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