Advanced search ....
-
Hi to all, I am working on an advanced search module. It consist of 6 fields given to user to fill and search. The user can fill any one or two and may be all the fields to search. Some fields are text based and some are float. The logic is that after taking the input, the application should check which fields are filled up by the user. And neglect the fields which are not filled from the sql query. What I did is that, I fetched all the values from the textboxes and the values which are not empty has been added to a arraylist. Now I got the filledup values. But application will know that it is productid, description or what... Please assist me.
protected void Button1\_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["newcon"\].ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //find out which feilds are not null, then make search on the basis of those feilds //Now the feilds which are not empty are: ArrayList arl = new ArrayList(); if (TextBox1.Text.Trim() != "") { arl.Add("productid"); } if (TextBox2.Text.Trim() != "") { arl.Add("sh\_desc"); } if (TextBox3.Text.Trim() != "") { arl.Add("weight"); } if (TextBox4.Text.Trim() != "") { arl.Add("length"); } if (TextBox5.Text.Trim() != "") { arl.Add("width"); } if (TextBox6.Text.Trim() != "") { arl.Add("height"); } //Now take out the items from the arraylist for (int i = 0; i <= arl.Count; i++ ) { if (arl\[i\].ToString().StartsWith("productid")) { } } cmd.CommandText = "select sh\_desc,productid,price from PRODUCTS where "; cmd.Connection.Open(); dt = new DataTable(); dt.Columns.Add("REF", typeof(string)); dt.Columns.Add("Description", typeof(string)); dt.Columns.Add("Price", typeof(float)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { pr\_id = (rdr\["productid"\]).ToString(); testsh\_desc = (rdr\["sh\_desc"\]).ToString(); test\_price = Convert.ToSingle(rdr\[I suspect your best bet is to build your SQL based on what fields were entered. Doing a response.redirect obviously means throwing away the results you've found, and the data that has been entered.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
I suspect your best bet is to build your SQL based on what fields were entered. Doing a response.redirect obviously means throwing away the results you've found, and the data that has been entered.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
you mean to say if I have 3 fields. Then I should use all the permutations and combinations. That is if all three are filled none is filled 1 is not filled , 2 & 3 are filled . . . . .
cheers, sneha
-
Hi Amit, Thanks for the reply. I am the count of the values filled by the user through arraylist. Suppose I get 3 values but how I will know that these 3 values are which values. Is it a product id, description and short description or it is price, weight and length. Please provide me an example if you have..I am confused..
cheers, sneha
Hi sneha, what i am saying u need to put if and else in ur code. intially ur query will be like this String query = Select * from urtablename where if(product id!=null) { query = query +"product id = input" } if(price) { query =query+ add ur query; } thanks and regars
-
you mean to say if I have 3 fields. Then I should use all the permutations and combinations. That is if all three are filled none is filled 1 is not filled , 2 & 3 are filled . . . . .
cheers, sneha
Hi sneha, what i am saying u need to put if and else in ur code. intially ur query will be like this String query = Select * from urtablename where if(product id!=null) { query = query +"product id = input" } if(price) { query =query+ add ur query; } thanks and regars amit patel
-
Hi sneha, what i am saying u need to put if and else in ur code. intially ur query will be like this String query = Select * from urtablename where if(product id!=null) { query = query +"product id = input" } if(price) { query =query+ add ur query; } thanks and regars amit patel
Ok I got it. I will try like this and came back to you with the results. :)
cheers, sneha
-
Hi to all, I am working on an advanced search module. It consist of 6 fields given to user to fill and search. The user can fill any one or two and may be all the fields to search. Some fields are text based and some are float. The logic is that after taking the input, the application should check which fields are filled up by the user. And neglect the fields which are not filled from the sql query. What I did is that, I fetched all the values from the textboxes and the values which are not empty has been added to a arraylist. Now I got the filledup values. But application will know that it is productid, description or what... Please assist me.
protected void Button1\_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["newcon"\].ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //find out which feilds are not null, then make search on the basis of those feilds //Now the feilds which are not empty are: ArrayList arl = new ArrayList(); if (TextBox1.Text.Trim() != "") { arl.Add("productid"); } if (TextBox2.Text.Trim() != "") { arl.Add("sh\_desc"); } if (TextBox3.Text.Trim() != "") { arl.Add("weight"); } if (TextBox4.Text.Trim() != "") { arl.Add("length"); } if (TextBox5.Text.Trim() != "") { arl.Add("width"); } if (TextBox6.Text.Trim() != "") { arl.Add("height"); } //Now take out the items from the arraylist for (int i = 0; i <= arl.Count; i++ ) { if (arl\[i\].ToString().StartsWith("productid")) { } } cmd.CommandText = "select sh\_desc,productid,price from PRODUCTS where "; cmd.Connection.Open(); dt = new DataTable(); dt.Columns.Add("REF", typeof(string)); dt.Columns.Add("Description", typeof(string)); dt.Columns.Add("Price", typeof(float)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { pr\_id = (rdr\["productid"\]).ToString(); testsh\_desc = (rdr\["sh\_desc"\]).ToString(); test\_price = Convert.ToSingle(rdr\[ -
Hi to all, I am working on an advanced search module. It consist of 6 fields given to user to fill and search. The user can fill any one or two and may be all the fields to search. Some fields are text based and some are float. The logic is that after taking the input, the application should check which fields are filled up by the user. And neglect the fields which are not filled from the sql query. What I did is that, I fetched all the values from the textboxes and the values which are not empty has been added to a arraylist. Now I got the filledup values. But application will know that it is productid, description or what... Please assist me.
protected void Button1\_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["newcon"\].ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; //find out which feilds are not null, then make search on the basis of those feilds //Now the feilds which are not empty are: ArrayList arl = new ArrayList(); if (TextBox1.Text.Trim() != "") { arl.Add("productid"); } if (TextBox2.Text.Trim() != "") { arl.Add("sh\_desc"); } if (TextBox3.Text.Trim() != "") { arl.Add("weight"); } if (TextBox4.Text.Trim() != "") { arl.Add("length"); } if (TextBox5.Text.Trim() != "") { arl.Add("width"); } if (TextBox6.Text.Trim() != "") { arl.Add("height"); } //Now take out the items from the arraylist for (int i = 0; i <= arl.Count; i++ ) { if (arl\[i\].ToString().StartsWith("productid")) { } } cmd.CommandText = "select sh\_desc,productid,price from PRODUCTS where "; cmd.Connection.Open(); dt = new DataTable(); dt.Columns.Add("REF", typeof(string)); dt.Columns.Add("Description", typeof(string)); dt.Columns.Add("Price", typeof(float)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { pr\_id = (rdr\["productid"\]).ToString(); testsh\_desc = (rdr\["sh\_desc"\]).ToString(); test\_price = Convert.ToSingle(rdr\[ -
I think You have to work on the query..! make the query parameter default as null in the store procedure and write the store procedure depending on the params..!
LatestArticle :Log4Net Why Do Some People Forget To Mark as Answer .If It Helps.
Hi, I did that but every time result was all the products.
ALTER PROCEDURE [dbo].[ap_SearchProduct]
(
@productid nvarchar(100) = null,
@product_type nvarchar(100) = null,
@sh_desc nvarchar(4000) = null,
@weight float = null,
@length float = null,
@width float = null,
@height float = null
)as
SET NOCOUNT ON SELECT productid,sh\_desc,price FROM PRODUCTS e WHERE (@productid IS NULL OR e.productid = @productid) OR (@product\_type IS NULL OR e.product\_type = @product\_type) OR (@sh\_desc IS NULL OR e.sh\_desc = @sh\_desc) OR (@weight IS NULL OR e.weight = @weight) OR (@length IS NULL OR e.length = @length) OR (@width IS NULL OR e.width = @width) OR (@height IS NULL OR e.height = @height)return
cheers, sneha
-
Hi, I did that but every time result was all the products.
ALTER PROCEDURE [dbo].[ap_SearchProduct]
(
@productid nvarchar(100) = null,
@product_type nvarchar(100) = null,
@sh_desc nvarchar(4000) = null,
@weight float = null,
@length float = null,
@width float = null,
@height float = null
)as
SET NOCOUNT ON SELECT productid,sh\_desc,price FROM PRODUCTS e WHERE (@productid IS NULL OR e.productid = @productid) OR (@product\_type IS NULL OR e.product\_type = @product\_type) OR (@sh\_desc IS NULL OR e.sh\_desc = @sh\_desc) OR (@weight IS NULL OR e.weight = @weight) OR (@length IS NULL OR e.length = @length) OR (@width IS NULL OR e.width = @width) OR (@height IS NULL OR e.height = @height)return
cheers, sneha
-
Hi, I did that but every time result was all the products.
ALTER PROCEDURE [dbo].[ap_SearchProduct]
(
@productid nvarchar(100) = null,
@product_type nvarchar(100) = null,
@sh_desc nvarchar(4000) = null,
@weight float = null,
@length float = null,
@width float = null,
@height float = null
)as
SET NOCOUNT ON SELECT productid,sh\_desc,price FROM PRODUCTS e WHERE (@productid IS NULL OR e.productid = @productid) OR (@product\_type IS NULL OR e.product\_type = @product\_type) OR (@sh\_desc IS NULL OR e.sh\_desc = @sh\_desc) OR (@weight IS NULL OR e.weight = @weight) OR (@length IS NULL OR e.length = @length) OR (@width IS NULL OR e.width = @width) OR (@height IS NULL OR e.height = @height)return
cheers, sneha
You have to go for 6 cases..!For one Case..!
SELECT productid,sh_desc,price FROM PRODUCTS e
WHERE productid = CASE WHEN @productid IS NULL
THEN productid
ELSE @productid
END
AND.....LatestArticle :Log4Net Why Do Some People Forget To Mark as Answer .If It Helps.