如何编写查询以过滤数据 [英] How write a query for filtering the data

问题描述

在我的应用程序中，我有4个下拉列表供选择
产品组
产品编号
仓库编号和
库存编号

我有一个带有列的表
productgroup productid数量，仓库编号和其他一些字段.

根据用户选择，它应该显示表中的所有数据.根据所选值

为此，我编写了此存储过程

In my application I have 4 Dropdown list for selecting
Product group
product id
warehouse id and
inventory id

I have a table with columns
productgroup productid qty, warehouseid,and some other fields.

accoring to user selection it should display all the data from the table. based on the selected value

for this i wrote this stored procedure

ALTER PROCEDURE SP1(@wid int, @pgid int, @pid int, @invcat int)
AS
BEGIN
    select PROD_GRP_ID,PRODUCT_ID,WAREHOUSE_ID, QTY from PROD_INVENTORY where (WAREHOUSE_ID=@wid or WAREHOUSE_ID is null ) and (PROD_GRP_ID=@pgid or PROD_GRP_ID is null )
    and (PRODUCT_ID=@pid or PRODUCT_ID is null ) AND (INV_CAT_ID=@invcat or INV_CAT_ID is NULL)
    END

这是代码

And this is the code

protected void Btnsearch_Click(object sender, EventArgs e)
   {
       cn = new SqlConnection("Data Source=ASHU;Initial Catalog=BP;User ID=sa;Password=123");
       //cmd.Parameters.Clear();
       cmd = new SqlCommand("SP1", cn);
       cmd.CommandType = CommandType.StoredProcedure;

       cmd.Parameters.AddWithValue("@wid", ddlwrid.SelectedItem.ToString());
       cmd.Parameters.AddWithValue("@pgid",ddlprodgrpid.SelectedValue.ToString());
       cmd.Parameters.AddWithValue("@pid", ddlprodid.SelectedValue.ToString());
       cmd.Parameters.AddWithValue("@invcat",ddlinvcatid.SelectedValue.ToString());

       da = new SqlDataAdapter(cmd);
       ds = new DataSet();
       da.Fill(ds);
       GridView2.DataSource = ds;
       GridView2.DataBind();
   }

但是请正确操作，请按以下步骤操作.

在Advance中致谢

BUT IT IS NOT WORKING PROPERLY PLEASE HELP.

Thanks in Advance

推荐答案

是因为在某些情况下您使用的是SelectedItem，而在其他情况下则使用的是SelectedValue?

我将从检查您要馈送到SP的参数值开始.
如果那没有帮助，那么我们将需要知道它是如何工作的-它在做什么，它应该不应该做什么，或者不应该这样做?

它不能正常工作"对诊断没有帮助...


顺便说一句:不要喊.使用所有大写字母被认为是在互联网上大喊大叫，并且粗鲁(使用所有小写字母被认为是幼稚的).如果您想认真对待，请使用适当的大写字母.

Is it because in some cases you are using SelectedItem, and others SelectedValue?

I would start by checking the parameter values you are feeding to the SP.
If that doesn''t help, then we would need to know how it isn''t working - what is it doing that it shouldn''t, or not doing that it should?

"it is not working properly" is not helpful in diagnosis...


BTW: DON''T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.

编写where子句，如下所示
(WAREHOUSE_ID = @ wid或ISNULL(WAREHOUSE_ID，-1)= -1)和
(PROD_GRP_ID = @ pgid或ISNULL(PROD_GRP_ID，-1)= -1)和
(PRODUCT_ID = @ pid或ISNULL(PRODUCT_ID，-1)= -1)AND
(INV_CAT_ID = @ invcat或ISNULL(INV_CAT_ID，-1)= -1)

问候

write the where clause something like below
(WAREHOUSE_ID=@wid or ISNULL(WAREHOUSE_ID, -1) = -1 ) and
(PROD_GRP_ID=@pgid or ISNULL(PROD_GRP_ID, -1) = -1 ) and
(PRODUCT_ID=@pid or ISNULL(PRODUCT_ID, -1) = -1 ) AND
(INV_CAT_ID=@invcat or ISNULL(INV_CAT_ID, -1) = -1)

Regards

这篇关于如何编写查询以过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！