Query to search in database for modified value

Ganesh babu

I have created two tables in sql database and joined them with joins.In that one is Products table, in that one column is P_active with char(1), and default value as 'Y'.While displaying in gridview, status column is showing 'Y', but i want to show 'Active' instead of 'Y'.I have done this, but the problem is while searching,in textbox if i enter 'Active' or any letter a/c/t/i/v/e and click on search, the records with 'Active' status must be shown but unfortunately, i am getting no records found if i enter any letter except 'Y'.So how to achieve the required functionality with query.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ganesh babu7y ago
CS CODE: protected void GridView1_RowDataBound(object sender,GridViewRowEventArgs e) { DataRowView drv = (DataRowView)e.Row.DataItem; if (e.Row.RowType == DataControlRowType.DataRow) { if (drv["P_active"].ToString() == "Y") e.Row.Cells[4].Text = "Active"; } } protected void btnsearch_Click(object sender, EventArgs e) { if (ddlsearch.SelectedItem.Text == "Product Name") { GridView1.DataSource = bll.Search("P_name", txtsearch.Text); } else if (ddlsearch.SelectedItem.Text == "Status") { GridView1.DataSource = bll.Search("P_active", txtsearch.Text); } GridView1.DataBind(); } BAL: public DataTable Search(string option, string val) { return dll.search(option, val); } DAL: public DataTable search(string searchBy, string searchVal) { SqlCommand cmd = new SqlCommand("spsearchp", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SearchBy", searchBy); cmd.Parameters.AddWithValue("@SearchVal", searchVal); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } Stored Procedure: CREATE PROC [dbo].[spsearchp] @SearchBy varchar(50), @SearchVal varchar(50) AS BEGIN IF @SearchBy = 'P_name' BEGIN select * from Products LEFT JOIN Category ON Products.PCid= Category.cid where P_name like '%' + @SearchVal + '%' END ELSE IF @SearchBy = 'P_active' BEGIN select * from Products LEFT JOIN Category ON Products.PCid= Category.cid WHERE P_active like '%' + @SearchVal + '%' END ELSE BEGIN select * from Products LEFT JOIN Category ON Products.PCid= Category.cid END END
0
Tushar Dikshit7y ago
Hi,
For active/inactive search, just provide radio or drodown near search text box as filter.
Or in gridview itself you can implement this filter with value Y or N.
Mostly websites has such functionality, as there is either active or inactive then it is not feasible to do text or wild card search, it is filter.
While displaying or fetching records you can use CASE statements.

In anyways you have to use texbox for active status search, you may have to use CASE, IF in stored proc if text like 'a%' the in where use 'Y' etc. But I don't think it would be recommended solution.

Thank you.
0