asp.net dynamic GridView SelectCommand分页/排序抛出Oracle错误

本文关键字：排序 Oracle 错误 分页 net dynamic GridView SelectCommand asp | 更新日期: 2023-09-27 18:07:23

编辑:在Felix的帮助下我自己解决了这个问题(见下面的答案)

我试图使用SelectCommand添加一些基本的搜索/过滤功能到GridView。在更改下拉列表并单击按钮时，SelectCommand应该随着添加SelectParameter而更改。

问题是，一旦改变SelectCommand并添加适当的SelectParameter，只有第一页显示完美，显示正确的过滤结果。试图更改页面(或排序)抛出Oracle错误

(Sys.WebForms。PageRequestManagerServerErrorException: ora - 01036:

当删除过滤(从而恢复SelectCommand并删除SelectParameter)时，分页/排序工作正确。

。ascx代码:

(注意owner_ddl DropdownList是用一个单独的代码隐藏方法中的值填充的)

<asp:DropDownList ID="owner_ddl" runat="server">
    <asp:ListItem Value="*">All</asp:ListItem>
</asp:DropDownList>
&emsp;
<asp:Button ID="filter_btn" runat="server" Text="Filter" OnClick="filter_btn_Click" />
<br />
<br />
<asp:GridView ID="workbasket_gv" runat="server" AllowPaging="True"
 AllowSorting="True" PagerSettings-Mode="NextPreviousFirstLast" PageSize="20"
 AutoGenerateColumns="False" DataKeyNames="INCIDENT_ID" DataSourceID="sqlIncident"
 EmptyDataText="No incidents meet this criteria" Width="90%">
    <Columns>
        <asp:CommandField ShowSelectButton="True" ButtonType="Button" SelectText="View">
            <ItemStyle Width="50px" />
        </asp:CommandField>
        <asp:BoundField DataField="INCIDENT_ID" HeaderText="ID" ReadOnly="True" SortExpression="INCIDENT_ID">
            <ItemStyle Width="50px" />
        </asp:BoundField>
        <asp:BoundField DataField="OWNER" HeaderText="Owner" SortExpression="OWNER">
            <ItemStyle Width="100px" />
        </asp:BoundField>
        <asp:BoundField DataField="TITLE" HeaderText="Title" SortExpression="TITLE" />
        <asp:BoundField DataField="DATE_RAISED" HeaderText="Date Raised" SortExpression="DATE_RAISED" DataFormatString="{0:g}">
            <ItemStyle Width="200px" />
        </asp:BoundField>
    </Columns>
    <PagerSettings Mode="NextPreviousFirstLast" Position="TopAndBottom" FirstPageText="&lt;&lt; First"
     LastPageText="Last &gt;&gt;" NextPageText="Next &gt;" PreviousPageText="&lt; Prev">
    </PagerSettings>
    <PagerStyle HorizontalAlign="Center" />
</asp:GridView>
<asp:SqlDataSource ID="sqlIncident" runat="server" ConnectionString="Data Source=dbmisc89_dbmisc89.w34.bmw.uk;Persist Security Info=True;User ID=W50XS001_W;Password=sales_2;"
 ProviderName="System.Data.OracleClient" SelectCommand="SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL ORDER BY DATE_RAISED DESC">
</asp:SqlDataSource>

后台代码:

protected void filter_btn_Click(object sender, EventArgs e)
{
    if (owner_ddl.SelectedValue != "*")
    {
        // Defined owner
        string sql = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL AND OWNER=:OWNER ORDER BY DATE_RAISED DESC";
        sqlIncident.SelectCommand = sql;
        ControlParameter param = new ControlParameter("OWNER", System.Data.DbType.String, "owner_ddl", "SelectedValue");
        sqlIncident.SelectParameters.Add(param);
    }
    else
    {
        // All owners (Remove variable)
        string sql = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL ORDER BY DATE_RAISED DESC";
        sqlIncident.SelectCommand = sql;
        sqlIncident.SelectParameters.Clear();
    }
    workbasket_gv.DataBind();
}

asp.net dynamic GridView SelectCommand分页/排序抛出Oracle错误

请核对一下。我不确定这是不是问题所在。让我知道它是否正确…

if (owner_ddl.SelectedValue != "*")
    {
        // Defined owner
ControlParameter param = new ControlParameter("OWNER", System.Data.DbType.String, "owner_ddl", "SelectedValue");
        sqlIncident.SelectParameters.Add(param);

        string sql = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL AND OWNER=:OWNER ORDER BY DATE_RAISED DESC";
        sqlIncident.SelectCommand = sql;
    }

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            DeleteCommand="DELETE FROM [Table] WHERE [INCIDENT_ID] = @INCIDENT_ID" 
            InsertCommand="INSERT INTO [Table] ([INCIDENT_ID], [OWNER]) VALUES (@INCIDENT_ID, @OWNER)" 
            SelectCommand="SELECT * FROM [Table] WHERE ([OWNER] = @OWNER)" 
            UpdateCommand="UPDATE [Table] SET [OWNER] = @OWNER WHERE [INCIDENT_ID] = @INCIDENT_ID">
            <DeleteParameters>
                <asp:Parameter Name="INCIDENT_ID" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="INCIDENT_ID" Type="Int32" />
                <asp:Parameter Name="OWNER" Type="String" />
            </InsertParameters>
            <SelectParameters>
                <asp:ControlParameter ControlID="owner_ddl" Name="OWNER" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="OWNER" Type="String" />
                <asp:Parameter Name="INCIDENT_ID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>

 protected void filter_btn_Click(object sender, EventArgs e)
    {
        SqlDataSource1.DataBind();
        workbasket_gv.DataBind();
    }

通过删除SelectParameter并将owner_ddl.SelectedValue直接放入SQL字符串来解决此问题。还添加了一个Session变量，在DataBind上传递给自己，以便分页/排序保持正确的查询。

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack)
    {
        if (!String.IsNullOrEmpty((String)Session["sqlOwnerType"]))
        {
            if ((String)Session["sqlOwnerType"] == "specific")
                sqlIncident.SelectCommand = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL AND OWNER='" + owner_ddl.SelectedValue + "' ORDER BY DATE_RAISED DESC";
            else if ((String)Session["sqlOwnerType"] == "all")
                sqlIncident.SelectCommand = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL ORDER BY DATE_RAISED DESC";
        }
    }
}
protected void filter_btn_Click(object sender, EventArgs e)
{
    if (owner_ddl.SelectedValue != "*")
    {
        // Defined owner
        string sql = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL AND OWNER='" + owner_ddl.SelectedValue + "' ORDER BY DATE_RAISED DESC";
        sqlIncident.SelectCommand = sql;
        Session["sqlOwnerType"] = "specific";
    }
    else
    {
        // All owners (Remove variable)
        string sql = "SELECT INCIDENT_ID, OWNER, TITLE, DATE_RAISED FROM INCIDENT_TBL WHERE TITLE IS NOT NULL ORDER BY DATE_RAISED DESC";
        sqlIncident.SelectCommand = sql;
        Session["sqlOwnerType"] = "all";
    }
    workbasket_gv.DataBind();
}
相关文章：