即使返回多行，GridView也只显示1行

本文关键字：显示 1行 GridView 返回 | 更新日期: 2023-09-27 18:13:15

我有一个GridView，其数据源是一个数据表，由SqlDataAdapter填充。我已经在SQL Management Studio中使用多个标准多次测试了SQL查询，并在数据集中返回了多行，但是当数据绑定到我的ASP上的GridView时。. NET页中，只有第一行显示应该有几行。

此问题的其他解决方案与OnRowDataBound事件有关，但我不使用该事件。

任何关于使我的代码(特别是我的SQL查询)更有效/高效的指针也将受到赞赏。

UPDATE:当使用以下参数执行查询时，查询似乎返回多个结果，但在其他时间:assign = 2 requesttype = OT otcode = 3

这是ASP。. NET页面的搜索条件(文本框和下拉列表)和GridView的问题:

<div>
    <label id="lblCardNumber" for="tbCardNumber">Card number</label>
    <asp:TextBox runat="server" ID="tbCardNumber"></asp:TextBox>
    <Label ID="lblUsername" for="tbUsername">Username</Label>
    <asp:TextBox runat="server" ID="tbUsername"></asp:TextBox>
    <Label ID="lblAssignment" for="ddlAssignment">Assignment</Label>
    <asp:DropDownList runat="server" CssClass="textbox" ID="ddlAssignment">
        <asp:ListItem Value='NULL' Text='Select' />
        <asp:ListItem Value="1" Text='1' />
        <asp:ListItem Value="2" Text='2' />
        <asp:ListItem Value="3" Text='3' />
        <asp:ListItem Value="4" Text='4' />
        <asp:ListItem Value="5" Text='5' />
    </asp:DropDownList>
    <Label ID="lblRequestType" for="ddlRequestType">Request Type</Label>
    <asp:DropDownList runat="server" ID="ddlRequestType">
        <asp:ListItem Value="NULL" Text="select" />
        <asp:ListItem Value="All" Text="All" />
        <asp:ListItem Value="OT" Text="OT" />
        <asp:ListItem Value="CO" Text="CO" />
        <asp:ListItem Value="TR" Text="TR" />
        <asp:ListItem Value="EP" Text="EP" />
        <asp:ListItem Value="TO" Text="TO" />
    </asp:DropDownList>
    <div id="OTSubTypes" class="subtypes OT">
        <label for="ddlOTCode">ot code no.</label>
        <asp:DropDownList runat="server" CssClass="textbox" ID="ddlOTCode">
            <asp:ListItem Value='0' Text='Select' />
            <asp:ListItem Value="1">1</asp:ListItem>
            <asp:ListItem Value="2">2</asp:ListItem>
            <asp:ListItem Value="3">3</asp:ListItem>
            <asp:ListItem Value="4">4</asp:ListItem>
            <asp:ListItem Value="5">5</asp:ListItem>
            <asp:ListItem Value="6">6</asp:ListItem>
        </asp:DropDownList>
    </div>
    <div id="COSubTypes" class="subtypes CO">
        <label for="rblCourtSession">Court Session</label>
        <asp:RadioButtonList runat="server" ID="rblCourtSession">
            <asp:ListItem>morning</asp:ListItem>
            <asp:ListItem>afternoon</asp:ListItem>
            <asp:ListItem>standby</asp:ListItem>
        </asp:RadioButtonList>
        <label for="rblCourtType">Court Type</label>
        <asp:RadioButtonList runat="server" ID="rblCourtType">
            <asp:ListItem>superior</asp:ListItem>
            <asp:ListItem>ygc</asp:ListItem>
            <asp:ListItem>traffic</asp:ListItem>
        </asp:RadioButtonList>
        <label for="rblAppearanceType">Appearance Type</label>
        <asp:RadioButtonList runat="server" ID="rblAppearanceType">
            <asp:ListItem>trailing</asp:ListItem>
            <asp:ListItem>da Conference</asp:ListItem>
            <asp:ListItem>administrative hearing</asp:ListItem>
        </asp:RadioButtonList>
    </div>
    <div id="TRSubTypes" class="subtypes TR">
        <label for="ddlTrainingCode">Training Code</label>
        <asp:DropDownList runat="server" CssClass="textbox" ID="ddlTrainingCode">
            <asp:ListItem Value='0' Text='Select' />
            <asp:ListItem>1</asp:ListItem>
            <asp:ListItem>2</asp:ListItem>
            <asp:ListItem>3</asp:ListItem>
            <asp:ListItem>4</asp:ListItem>
            <asp:ListItem>5</asp:ListItem>
            <asp:ListItem>6</asp:ListItem>
        </asp:DropDownList>
    </div>
    <Label ID="lblBeginningDate" for="tbBeginningDate">Beginning Date</Label>
    <asp:TextBox runat="server" ID="tbBeginningDate"></asp:TextBox>
    <Label ID="lblAccountNumber" for="tbAccountNumber">Account Number</Label>
    <asp:TextBox runat="server" ID="tbAccountNumber"></asp:TextBox>
    <div>
        <asp:Button runat="server" ID="btnSearch" Text="search" OnClick="btnSearchClick" />
        <asp:Button runat="server" ID="btnClearFields" Text="clear fields"/>
    </div>
    <div class="table-responsive">
        <asp:GridView runat="server" class="table table-striped table-hover" ID="gvSearchResults" GridLines="None" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="card_number" HeaderText="Card Number" />
                <asp:BoundField DataField="RequestType" HeaderText="Card Type" />
                <asp:BoundField DataField="beginning_date" HeaderText="Date" />
                <asp:BoundField DataField="status_id" HeaderText="Status" />
                <asp:TemplateField>
                    <HeaderTemplate>
                        edit
                    </HeaderTemplate>
                    <ItemTemplate>
                    <a href="<%#Eval("RequestType") %>Request.aspx?id=<%#Eval("card_number") %>">edit/view</a>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        <RowStyle CssClass="cursor-pointer" />
    </asp:GridView>
</div>

这是c#:

protected void btnSearchClick(object sender, EventArgs e)
{
    string connstring = ConfigurationManager.ConnectionStrings["TimeHubDBCS"].ConnectionString;
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    using (SqlConnection conn = new SqlConnection(connstring))
    {
        cmd.Connection = conn;
        //declare parameters that are independent of request type
        cmd.Parameters.Add("@CardNumber", SqlDbType.VarChar).Value = tbCardNumber.Text;
        cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = tbUsername.Text;
        cmd.Parameters.Add("@Assignment", SqlDbType.VarChar).Value = ddlAssignment.SelectedValue;
        cmd.Parameters.Add("@BeginningDate", SqlDbType.VarChar).Value = tbBeginningDate.Text;
        //declare sqlcommand arguments and parameters that are dependent on request type
        switch (ddlRequestType.SelectedValue.ToString().ToLower())
        {
            case "co":
                cmd = new SqlCommand("spSearchCORequests", conn);
                cmd.Parameters.Add("@CourtSession", SqlDbType.VarChar).Value = rblCourtSession.SelectedValue;
                cmd.Parameters.Add("@CourtType", SqlDbType.VarChar).Value = rblCourtType.SelectedValue;
                cmd.Parameters.Add("@AppearanceType", SqlDbType.VarChar).Value = rblAppearanceType.SelectedValue;
                break;
            case "ep":
                cmd = new SqlCommand("spSearchEPRequests", conn);
                break;
            case "ot":
                cmd.CommandText = "spSearchOTRequests";
                //cmd = new SqlCommand("spSearchOTRequests", conn);
                cmd.Parameters.Add("@AccountNumber", SqlDbType.VarChar).Value = tbAccountNumber.Text;
                cmd.Parameters.Add("@OTCode", SqlDbType.VarChar).Value = ddlOTCode.SelectedValue;
                break;
            case "pt":
                cmd = new SqlCommand("spSearchPTRequests", conn);
                break;
            case "to":
                cmd = new SqlCommand("spSearchTORequests", conn);
                break;
            case "tr":
                cmd = new SqlCommand("spSearchTRRequests", conn);
                cmd.Parameters.Add("@TrainingCode", SqlDbType.VarChar).Value = ddlTrainingCode.SelectedValue;
                break;
        }
        SqlDataAdapter daSearchResults = new SqlDataAdapter();
        daSearchResults.SelectCommand = cmd;
        DataTable dtSearchResults = new DataTable();
        try
        {
            conn.Open();
            daSearchResults.Fill(dtSearchResults);
            conn.Close();
        }
        catch(Exception ex)
        {
            message = "search error: ";
            ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + ex.Message + "');", true);
        }
        gvSearchResults.DataSource = dtSearchResults;
        gvSearchResults.DataBind();
        gvSearchResults.UseAccessibleHeader = true;
        gvSearchResults.HeaderRow.TableSection = TableRowSection.TableHeader;
    }
}
下面是SQL查询: 
ALTER PROCEDURE spSearchOTRequests
@CardNumber VARCHAR(MAX)=NULL,
@Username VARCHAR(MAX)=NULL,
@Assignment VARCHAR(MAX)=NULL,
@BeginningDate VARCHAR(MAX)=NULL,
@AccountNumber VARCHAR(MAX)=NULL,
@OTCode VARCHAR(MAX)=NULL   
AS
BEGIN
    SET @CardNumber = ISNULL(@CardNumber, '')
    SET @Username = ISNULL(@Username, '')
    SET @Assignment = ISNULL(@Assignment, '')
    SET @BeginningDate = ISNULL(@BeginningDate, '')
    SET @AccountNumber = ISNULL(@AccountNumber, '')
    SET @OTCode = ISNULL(@OTCode, '')
    IF @CardNumber = '' BEGIN SET @CardNUmber = @CardNumber + '%' END
    IF @Username = '' BEGIN SET @Username = @Username + '%' END
    IF @Assignment = '' BEGIN SET @Assignment = @Assignment + '%' END
    IF @BeginningDate = '' BEGIN SET @BeginningDate = @BeginningDate + '%' END
    IF @AccountNumber = '' BEGIN SET @AccountNumber = @AccountNumber + '%' END
    IF @OTCode = '' BEGIN SET @OTCode = @OTCode + '%' END
    SELECT
        card_number,
        UserCredited,
        user_star,
        assignment,
        beginning_date,
        event_number,
        ot_code,
        status_id,
        RequestType
    FROM dbo.OTCard
    WHERE
        (@CardNumber IS NULL OR (@CardNumber = '') OR (card_number LIKE @CardNumber))
        AND (@Username IS NULL OR (@Username = '') OR (UserCredited LIKE @Username))
        AND (@Assignment IS NULL OR (@Assignment = '') OR (assignment LIKE @Assignment))
        AND (@BeginningDate IS NULL OR (@BeginningDate = '') OR (beginning_date LIKE @BeginningDate))
        AND (@AccountNumber IS NULL OR (@AccountNumber = '') OR (event_number LIKE @AccountNumber))
        AND (@OTCode IS NULL OR (@OTCode = '') OR (ot_code LIKE @OTCode))
    OPTION (RECOMPILE)
END

即使返回多行，GridView也只显示1行

在下拉菜单中有第一个选项当发送到存储过程时，它将作为文本'NULL'而不是空值试试这个

cmd.Parameters.Add("@Assignment", SqlDbType.VarChar).Value = ddlAssignment.SelectedValue == "NULL" ? null : ddlAssignment.SelectedValue;

首先，您需要确保从过程返回的记录不止一条，您可以在填充dtSearchResults并检查它包含多少条记录之后中断。如果表格包含多行并且网格显示一行，使用浏览器检查器检查表格的html输出，也许css样式与它有一些关系。

用于SQL查询这些条件

(@CardNumber    IS NULL OR  (@CardNumber    =   ''  )
(@Username      IS NULL OR  (@Username      =   ''  )
(@Assignment    IS NULL OR  (@Assignment    =   ''  )
(@BeginningDate IS NULL OR  (@BeginningDate =   ''  )
(@AccountNumber IS NULL OR  (@AccountNumber =   ''  )
(@OTCode        IS NULL OR  (@OTCode        =   ''  )

它们永远不会为真，因为你将NULL和Empty参数转换为sp开头的%所以你应该准备好它们

我看到所有参数都是varchar(max)这是表中的数据类型吗?

可以尝试使用参数化的动态sql 

declare @SQL nvarchar(max)
set @SQL =' SELECT
    card_number     ,
    UserCredited    ,
    user_star       ,
    assignment      ,
    beginning_date  ,
    event_number    ,
    ot_code         ,
    status_id       ,
    RequestType
FROM dbo.OTCard 
WHERE 1=1 '
IF ISNULL(@CardNumber,'') <>''
   SET @SQL=@SQL+' AND card_number LIKE @CardNumber'
IF ISNULL(@Username,'') <>''
   SET @SQL=@SQL+' AND UserCredited LIKE @Username'
   Continue with the rest of the parameters
  ....
  ....
EXECUTE sp_executesql @SQL,
N'@CardNumber VARCHAR(MAX),@Username  VARCHAR(MAX),@Assignmen VARCHAR(MAX),@BeginningDate VARCHAR(MAX),@AccountNumber VARCHAR(MAX), @OTCode VARCHAR(MAX)',
@CardNumber,@Username,@Assignment,@BeginningDate,@AccountNumber ,@OTCode
对于参数嗅探问题，您可以在@SQL 中使用OPTION (RECOMPILE)

或者如果你有一些参数当值改变时它会显著改变执行计划并且你想要每次重新编译的成本你可以使用像这样的

declare @HashKey varbinary(20)
    select @HashKey= HASHBYTES('SHA1', @Username )  you can concatenate more parameters if you wish
    SET @SQL='declare @HashKey nvarchar(200)
    SET @HashKey='''+ CONVERT(nvarchar(100),@HashKey,1 )+'''
    '+@SQL

添加到EXECUTE前

相关文章：