Solved

untyped dataset, partial search, vb.net

Posted on 2014-12-12
6
350 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
  • 3
  • 2
6 Comments
 
LVL 62

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 33

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 33

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 33

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to pass stored proc parameters in list 13 32
SQL syntax in VB.net 5 28
C# HTTP GET method sample code 3 40
Close tabpage of custom control 9 23
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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now