Search Function

Question

I need to make a Search function but the Search Button will be validated by different options at a time. What I mean can be seen in the link with the screen shot below.

Screenshot ----> http://imageshack.us/photo/my-images/199/searchh.jpg/

Can anyone help me so I can validate all the 4 filters at the same time.

Thanks.

Mark

Vulpes · Answer

One thing that's still wrong is that you're calling ToString() in these lines: cmd.Parameters.Add(new SqlParameter(@minPrice SqlDbType.Decimal)).Value = minPrice.ToString(); cmd.Parameters.Add(new SqlParameter(@maxPrice SqlDbType.Decimal)).Value = maxPrice.ToString(); even though the type of the parameters is decimal. I assume that the types you're using for the parameters are compatible with the actual types in the database table. I'd also make sure that p.Color and p.Type are empty strings rather than null.

Sam Hobbs · Answer

Look at your query and ensure that the parameters in the query match the parameters you specify. Then look at sample uses of the SqlParameter class; the @ is used for two entirely different purposes. You are using it the C# way but you want to use it the SQL parameter way.

Mark Fenech · Answer

What am I doing wrong here ? public void SearchProduct(Product p DataGridView dgv int minSize int maxSize decimal minPrice decimal maxPrice) { sqlConn.Open(); string query = SELECT * FROM tbl_Products WHERE (Size >= @minSize AND Size = @minPrice AND [Buying Price] cmd = new SqlCommand(query sqlConn); cmd.Parameters.Add(new SqlParameter(@Color SqlDbType.VarChar)).Value = p.Color; cmd.Parameters.Add(new SqlParameter(@minSize SqlDbType.Int)).Value = minSize; cmd.Parameters.Add(new SqlParameter(@maxSize SqlDbType.Int)).Value = maxSize; cmd.Parameters.Add(new SqlParameter(@minPrice SqlDbType.Decimal)).Value = minPrice.ToString(); cmd.Parameters.Add(new SqlParameter(@maxPrice SqlDbType.Decimal)).Value = maxPrice.ToString(); cmd.Parameters.Add(new SqlParameter(@Type SqlDbType.VarChar)).Value = p.Type; if (p.Color !=  && p.Type != ) { query += AND (Colour = @Color) AND (Type = @Type); } else if (p.Color != ) { query += AND (Colour = @Color); } else if (p.Type != ) { query += AND (Type = @Type); } ds = new DataSet(products); da = new SqlDataAdapter(cmd); SqlCommandBuilder sqb = new SqlCommandBuilder(da); da.Fill(ds tbl_Products); dgv.DataSource = ds.Tables[tbl_Products]; sqlConn.Close(); }

Vulpes · Answer

That query would only apply if no color or type were specified. It would then return all items of clothing within the minimum and maximum values specified. If in addition the user didn't specify any figures for size or price (or even both) then the query would still work because you'd be substituting the minimum or maximum possible values for the missing fields.

Mark Fenech · Answer

But What about this : string queryString = SELECT * FROM table WHERE (size >= @minSize AND size = @minPrice AND price This wont work in this case right ? Can you please give me ax example if its possible can should it be dont so that the user can search for data with 2 entries 3 or even 1 (2 entries ex: only colour and type) (1 entry ex: only sizes max and min) etcc.... Thanks

Vulpes · Answer

OK if you're using textboxes rather than radiobuttons then that's not a problem. If the minimum box is left empty by the user substitute the minimum possible value. If the maximum box is left empty by the user substitute the maximum possible value. The query should then still work as long as those fields are not empty in the database itself. Incidentally in your screenshot for the Size Range textboxes you have labels of Min and Min rather than Min and Max.

Mark Fenech · Answer

Yes of course....the user can type and choose any different posibilities say if the user only chooses a colour all the clothes in that colour should show up with all sizes and all prices and type. You can find a screenshot below with my new GUI cause I removed the radio buttons with textboxes. ScreenShot -----> http://imageshack.us/photo/my-images/88/searchid.jpg/

Vulpes · Answer

Well my original idea if no size or price radiobuttons were selected was to use the minimum and maximum possible values for these fields. You wouldn't then need to introduce a different kind of query string as the field value would always fall within that range. However that was before I knew you were going to query the database directly. Are you saying that the color or type fields might sometimes contain nothing at all?

Mark Fenech · Answer

Regarding to this post : string queryString = SELECT * FROM table WHERE (size >= @minSize AND size = @minPrice AND price if (colour !=  && type != ) { queryString += AND (colour = @colour) AND (type = @type); } else if (color != ) { queryString += AND (colour = @colour); } else if (type != ) { queryString += AND (type = @type); } What if you dont have and price range nor size range just a color or just a type. It won't work right ?

Vulpes · Answer

For those last two parameters you don't want to convert them to strings. So they should be: cmd.Parameters.Add(new SqlParameter(@minPrice SqlDbType.Float)).Value = minPrice; cmd.Parameters.Add(new SqlParameter(@maxPrice SqlDbType.Float)).Value = maxPrice; Also you'll need to redeclare the minPrice and maxPrice variables as double (assuming the database field contains 8 byte floats) rather than decimal.

Mark Fenech · Answer

cmd.Parameters.Add(new SqlParameter(@minSize SqlDbType.Int)).Value = minSize; cmd.Parameters.Add(new SqlParameter(@maxSize SqlDbType.Int)).Value = maxSize; cmd.Parameters.Add(new SqlParameter(@minPrice SqlDbType.Float)).Value = minPrice.ToString(); cmd.Parameters.Add(new SqlParameter(@maxPrice SqlDbType.Float)).Value = maxPrice.ToString(); What am i doing wrong about the conversion ?

Vulpes · Answer

What you could do is to have four possible query strings depending on whether: 1. Colour and type are both specified. 2. Only colour is specified. 3. Ony type is specified. 4. Neither are specified: string queryString = SELECT * FROM table WHERE (size >= @minSize AND size = @minPrice AND price if (colour !=  && type != ) { queryString += AND (colour = @colour) AND (type = @type); } else if (color != ) { queryString += AND (colour = @colour); } else if (type != ) { queryString += AND (type = @type); }

Sam Hobbs · Answer

To do it the way I was suggesting you would usually have a simple query such as  SELECT * FROM YOURTABLE . Then the filter would be specified dynamically. Unfortunately it does not support parameters so you would have to put the data in the filter instead of using  @SizeRange  or parameters such as that.

Mark Fenech · Answer

I don't know if I can do this or not SELECT * FROM YOURTABLE WHERE SizeRange = @SizeRange OR Colors = @Colors OR PriceRanage = @PriceRange OR Type = @Type Cause I need the search to work even if the user doesn't tick everything that is if the user only choose a type and press search it works on the type. If the user enter a type and a colour it filters both. What do you think ? Best Regards

Mark Fenech · Answer

Yes the grey thing is a DataGridView and it is connected with the SQL. So that to show the searched data in their.

Sam Hobbs · Answer

There is a lot that you are not telling us. So we must guess. Please excuse us if we guess wrong. I will assume that the big grey area at the lower half of the form is a DataGridView but you did not tell us what it is. I will also assume that the DataGridView is bound to the data but realistically speaking if it was then you would have said so. When I say DataGridView is bound to the data I mean something such as in my Database Table Update in a DataGridView without Writing Code . If you bind the control to the data then you can filter using the data source (actually the BindingSource for the control). The filter would be the same as the When part of a SQL query. Or at least as far as I know the filter is the same as a SQL query Where clause. So is that the type of thing you need?

Mark Fenech · Answer

I have a table of colours and products. I know how to search example for products example: a certain colour and it gives all the products with that colour using the SELECT WHERE command. But I need to check when for example the Colour is Red and the size is 30-32 and price 20. How can I do that ? I am connected with a database.

Vulpes · Answer

It looks like your application is concerned with items of clothing. I'm assuming that you have a Clothing class: public class Clothing { public int Size {get; set;} public string Color {get; set;} public decimal Price {get; set;} public string Type {get; set;} } and a list of stock to search for items which satisfy the criteria: List clothes = new List (); // populate clothes list with all stock You'd need to start by setting some filter variables: int minSize; int maxSize; if (radioButtonSizeRange1.Checked) { minSize = 29; maxSize = 29; } else if (radioButtonSizeRange2.Checked) { minSize = 30; maxSize = 32; } else if (radioButtonSizeRange3.Checked) { minSize = 33; maxSize = 35; } else if (radioButtonSizeRange4.Checked) { minSize = 36; maxSize = 38; } else if (radioButtonSizeRange5.Checked) { minSize = 39; maxSize = 50; // or whatever is the maximum size available } else // nothing selected { minSize = 29; maxSize = 50; } string color; if (comboBoxColor.SelectedIndex = - 1) { color = ; } else { color = comboBoxColor.SelectedItem.ToString().ToLower(); } decimal minPrice; decimal maxPrice; if (radioButtonPriceRange1.Checked) { minPrice = 10m; maxPrice = 10.99m; } else if (radioButtonPriceRange2.Checked) { minPrice = 11m; maxPrice = 30.99m; } else if (radioButtonPriceRange3.Checked) { minPrice = 31m; maxPrice = 50.99m; } else if (radioButtonPriceRange4.Checked) { minPrice = 51m; maxPrice = 80.99m; } else if (radioButtonPriceRange5.Checked) { minPrice = 81m; maxPrice= 499.99m ; // or whatever is the maximum price available } else // nothing selected { minPrice = 10m; maxPrice = 499.99m; } string type = textBoxType.Text.Trim().ToLower(); You can then get an array of clothing which satisfies the search criteria using this LINQ query: clothes[] = (from c in clothes where (c.Size >= minSize && c.Size && (color ==  || c.Color.ToLower() == color) && (c.Price >= minPrice && c.Price && (type ==  || c.Type.ToLower() == type) select c).ToArray();