是否有任何查询在数据库中搜索被另一个值替换的值 [英] Is there any query to search in database for the value which was replaced by another value

问题描述

我在sql数据库中创建了两个表，并用连接加入它们。其中一个是Products表，其中一列是P_active，带有char（1），默认值为' Y '。在gridview中显示时，状态列显示' Y '，但我想显示' Active '而不是' Y '。我已经这样做了，但问题是在搜索时，如果我输入' Active '或任何字母 a / c / t / i / v / e 并点击搜索，记录为' Active '状态必须显示但不幸的是，如果我输入除 Y 之外的任何字母，我没有找到记录。那么如何实现所需的功能查询。



我尝试过：



CS代码：

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:

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：

BAL:

public DataTable Search(string option, string val)
    {
        return dll.search(option, val);
    }

DAL：

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

推荐答案

A简单的方法是改变处理 Active 搜索的部分的程序;也许这样的事情对你有用

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

-- 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

这篇关于是否有任何查询在数据库中搜索被另一个值替换的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！