?
Solved

untyped dataset, partial search, vb.net

Posted on 2014-12-12
6
Medium Priority
?
395 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 2000 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
Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 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