SqlDataSource的SelectCommand中使用类似不工作 [英] SqlDataSource SelectCommand using LIKE does not work

问题描述

我在下面的T-SQL中的的SelectCommand ：

I have the following T-SQL in a SelectCommand:

SELECT h.Business,
hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE 
(h.Business like '%' + @business + '%' and h.Business is not null) 
and 
(hrl.frn = @frn and hrl.frn is not null)

商业和 FRN 是绑控制参数，它应该返回数据，即使一个或两个是空白，但如果我把数据只为 FRN 例如，它不返回任何东西。我觉得我的T-SQL是不是做正确的事情，我也不能肯定，如果我办理像正常。

business and frn are tied to control parameters and it should return data even if one or both is left blank, but if I put in data just for frn for example, it does not return anything. I think my T-SQL is not doing the right thing and I am also not sure if I am handling the like correctly.

如果这两个文本框保留为空，它应该返回的所有数据。如果 FRN 进入，但商业留空，它应该只返回与该 FRN 。如果商业如果输入，但 FRN 留空，则应该返回所有匹配像 商业。如果两者都被输入，它应该返回的数据只有符合 FRN 和商业。

if both textboxes are left empty, it should return all data. If frn is entered, but business is left blank, it should only return data related to that frn. If business if entered, but frn is left blank, it should return all matches like business. If both are entered, it should return data only matching the frn and the business.

另外，我如果这样做的与is not null我不知道实际上是必要的。

Also, I am not sure if doing the and is not null is actually necessary.

protected void btnSearch_Click(object sender, EventArgs e)
{
    if (txtFRN.Text == "")
        frn = null;

    if (txtBusiness.Text == "")
        business = null;

    sqlDsMaster.SelectParameters[frn].DefaultValue = frn;
    sqlDsMaster.SelectParameters[business].DefaultValue = business;

    sqlDsMaster.DataBind();
}

当它击中这条线以上抛出一个未设置为一个实例对象引用

The above throws an "Object Reference not set to an instance" when it hits this line:

sqlDsMaster.SelectParameters[frn].DefaultValue = frn;

FRN 和商业的属性。

---

下面是 SearchMaster 存储过程：

CREAETE PROCEDURE SearchMaster
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT h.Business,
       hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE (@business IS NULL OR h.Business like '%' + @business + '%') 
  AND (@frn IS NULL OR hrl.frn = @frn)

下面是 SearchDetails 存储过程：

CREATE PROCEDURE SearchDetails
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT hrl.call 
FROM registration hrl 
INNER JOIN holder h ON h.call = hrl.call
WHERE (@business IS NULL OR h.Business LIKE '%' + @business + '%') 
      AND (@frn IS NULL OR hrl.frn = @frn)

下面是的SqlDataSource 为 SearchMaster 程序：

<asp:SqlDataSource ID="sqlDsDetails" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:cnxString %>
                   SelectCommandType="StoredProcedure" 
                   SelectCommand="SearchMaster">
  <SelectParameters>
    <asp:ControlParameter Name="business" ControlID="txtBusiness" 
                          Type="String" PropertyName="Text"  
                          ConvertEmptyStringToNull="true" />
    <asp:ControlParameter Name="frn" ControlID="txtFRN" 
                          Type="String" PropertyName="Text"
                          ConvertEmptyStringToNull="true"/>
  </SelectParameters>
</asp:SqlDataSource>

下面是的SqlDataSource 为 SearchDetails 程序：

<asp:SqlDataSource ID="sqlDsDetails" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:cnxString %>
                   SelectCommandType="StoredProcedure" 
                   SelectCommand="SearchDetails">
  <SelectParameters>
    <asp:Parameter Name="frn" Type="String" DefaultValue="" 
                   ConvertEmptyStringToNull="true" />
    <asp:Parameter Name="business" Type="String" DefaultValue="" 
                   ConvertEmptyStringToNull="true" />
  </SelectParameters>
</asp:SqlDataSource>

下面就是按一下按钮结合的 SqlDsMaster ：

Here is the button click that binds the SqlDsMaster:

protected void btnSearch_Click(object sender, EventArgs e)
{
    sqlDsMaster.DataBind();
}

下面是 gvMaster_RowCreated 创建了详细的行：

Here is the gvMaster_RowCreated that creates the rows for the details:

protected void gvMaster_RowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        SqlDataSource ctrl = 
        e.Row.FindControl("sqlDsDetails") as SqlDataSource;

        if (ctrl != null && e.Row.DataItem != null)
        {
            ctrl.SelectParameters["frn"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["frn"].ToString();

            ctrl.SelectParameters["business"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["business"].ToString();
         }
     }
 }

SearchMaster 和 SearchDetails 如果我运行它通过SQL Server Management Studio中，如果我同时输入它的工作原理都工作为商业和 FRN ，但如果我输入只有一个，不返回任何数据。是参数设置是否正确？另外，如果我是初始化参数的空中的过程，是它仍然需要使用 ConvertEmptyStringToNull ？

SearchMaster and SearchDetails both work if I run it through SQL Server Management Studio and it works if I enter both data for business and frn, but if I enter just one, no data is returned. Are the parameters set up correctly? Also, if I am initializing the parameters to null in the procedure, is it still necessary to use ConvertEmptyStringToNull?

推荐答案

我会做这样的事情：

where (@business is null
          or @business = ''
          or h.Business like '%' + @business + '%')
      and (@frn is null
              or @frn = ''
              or hrl.frn = @frn)

如果您通过之前让你空搜索字符串为空，则可以跳过@yyy =''的一部分。

If you make your empty search strings nulls before passing them, you can skip the @yyy = '' part.

这篇关于SqlDataSource的SelectCommand中使用类似不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！