Question sql parameters and looping (with pictures)

[jamie123]

Hey, I'm programming in vb.net using vs2008 and sqlexpress 2005. I just need a little insight on where to go from here to do what I wanna do, so here I go:

I have a program that draws data from a dataset into a datagridview.

I also have a search feature, as you can see, that uses the following sql code to search the database and then re-fill the dataset with the newly filtered data.

SELECT ID, Frame, [Stock Num], Type, [Insurance Code], Mfgr, Color, Size, Cost, Retail, [% Markup], [Qty on Hand], [Reorder Point], [Ordered From], [Qty on Order], [Qty to Order], [Date Ordered], [Date Recieved], [Order Initials], [Recieve Initials], [Date Sold], BarCode FROM dbo.Materials WHERE Frame like '%'+@search+'%' or [Stock Num] like '%'+@search+'%' or Color like '%'+@search+'%'

It searches certain columns for criteria like this:

If I search for "power" it will return the row of the searchable columns (Frame, Stock Num, Color) that the query matches. Seems to work fine.

THE PROBLEM:
As you can see, one of the frames has a color of "crater". I want to find a power ranger frame with the color crater. So naturally,

Oops, this is because it is searching Frame, Stock Num, or color for "%power crater%".

I was wondering what the best way of making this work is? Should I use an instr function and divide the search string into separate variables and searched based on those terms? But how would I even do that, if I don't know how many parameters there are, how can I construct the sql query?

I just need some insight on how to fix this search to be able to search multiple columns, with multiple parameters, or if theres a better way to do it it'd be greatly appreciated

Thanks!

 

[jmcilhinney]

If you want filter on three different columns then you should have three different search fields. Either that or provide a way to add multiple search criteria one at a time.

 

[Tom]

I would add a combobox that lists each of the column names in your datatable plus the key word textbox. Allow the user to select which field along with adding the search keyword and then add that to a filter list. The user would then be add multiple filters that are more specific.

 

[cjard]

Turn your logic around:

Bad:
SELECT * FROM table WHERE name LIKE '%power crater%' OR color LIKE '%power crater%'

Good:
SELECT * FROM table WHERE 'power crater' LIKE '%' & name & '%' OR 'power crater' LIKE '%' & color & '%'

note: this can be very heavy as a search because the DB won't use indexes. If you have thousands of items, split up the search into separate boxes for the user to type in like Jmc says
-
Read the PQ link in my signature; don't write SQLs like that all your programming life, please - it's a very bad way of doing them

 

[jamie123]

I ended up just doing different text boxes because this program is dealing with thousands of entries, and I think you guys are right in saying that it'll be a more efficient way of searching.

Thanks for all the help!

 

[cjard]

don't forget to do your query like this then:


SELECT * FROM table WHERE
(name like @name) AND
(color like @color)


Now to wildcard any parameter just set its value to %

User searches only for "crater" color, parameters should be:
cmd.Parameters)("@name").Value = '%'
cmd.Parameters)("@color").Value = 'crater'

Note, you can do this with typed datasets and save some hassle in writing code