Click here to Skip to main content
15,169,249 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

What I have tried:

CS CODE:

C#
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:
C#
public DataTable Search(string option, string val)
    {
        return dll.search(option, val);
    }


DAL:
C#
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:

SQL
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
Posted
Updated 16-Apr-19 14:36pm
v2
Comments
Gerry Schmitz 16-Apr-19 16:14pm
   
You create a secondary index with all the values that you want to resolve to "Y".

1 solution

A simple way would be to ALTER that procedure for the portion dealing with the Active search; perhaps something like this would work for you
SQL
-- prior code
ELSE IF @SearchBy = 'P_active'
BEGIN
-- select * from Products LEFT JOIN Category ON Products.PCid= Category.cid WHERE P_active like '%' + @SearchVal + '%'
  DECLARE @Active CHAR(1) = 'N'
  IF (@SearchVal = 'active') SET @Active = 'Y'
  SELECT *
  FROM   Products  p
  LEFT JOIN Category c ON p.PCid= c.cid
  WHERE  p.P_active= @Active
END
-- continue with previous code
   
Comments
Member 14185275 16-Apr-19 23:33pm
   
Thank u for ur quick response.I will check with it and update.Thank u once again.
Member 14185275 17-Apr-19 2:47am
   
Thank uMyche, that worked perfectly fine and thank u soo much for ur response.
MadMyche 17-Apr-19 6:19am
   
You are welcome

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900