sqldatasource的FilterExpression用法

最新推荐文章于 2021-11-12 16:40:52 发布

hzleihuan

最新推荐文章于 2021-11-12 16:40:52 发布

阅读量3.8k

点赞数

分类专栏： asp.net(vb) 文章标签： asp textbox server date object button

本文链接：https://blog.csdn.net/hzleihuan/article/details/1433110

版权

asp.net(vb) 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1、一种是简单的单个字段查找法：

<asp:DropDownList ID="ddlSelect" runat="server">
 <asp:ListItem Value ="Fumi_NO">流水号</asp:ListItem>
 <asp:ListItem Value ="Ent_Cname">企业名称</asp:ListItem>
 </asp:DropDownList>

 <asp:textbox id="txtcont" runat="server" text="" />

 <asp:Button ID="search" runat="server" Text="查找" OnClick="Search_Click" />

 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Fumi_NO" DataSourceID="ObjectDataSource1">
 <Columns>
 <asp:BoundField DataField="Fumi_NO" HeaderText="流水号" ReadOnly="True" SortExpression="Fumi_NO" />
 <asp:BoundField DataField="Reg_Date" HeaderText="登记日期" SortExpression="Reg_Date" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />
 <asp:BoundField DataField="Ent_CName" HeaderText="企业名称" SortExpression="Ent_CName" />
 <asp:BoundField DataField="Goods_Name" HeaderText="货物名称" SortExpression="Goods_Name" />
 <asp:BoundField DataField="Goods_Num" HeaderText="货物数量" SortExpression="Goods_Num" />
 <asp:BoundField DataField="AppFumi_Date" HeaderText="除害日期" SortExpression="AppFumi_Date" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />
 <asp:BoundField DataField="Status_Name" HeaderText="状态" SortExpression="Status_Name" />
 </Columns>
 </asp:GridView>

代码页：

Protected Sub Search_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        ddlSel = ddlSelect.SelectedValue
        ObjectDataSource1.FilterExpression = ddlSel & " like '%{0}%'"
        If txtcont.Text = "" Then
            ObjectDataSource1.FilterParameters.Clear()
            ObjectDataSource1.FilterParameters.Add(ddlSel, "")
            'e.ParameterValues.Add(ddlSel, " ")
        Else
            ObjectDataSource1.FilterParameters.Clear()
            ObjectDataSource1.FilterParameters.Add(ddlSel, txtcont.Text)
            'ObjectDataSource1.FilterParameters(ddlSel).DefaultValue = Textbox1.Text

End If

End Sub

第二种用于组合查询：

asp:Label ID="Label14" Runat="server" Text="工作流程查询" SkinID="FormHeading"></asp:Label>
 
 
 
 <asp:DropDownList ID="ddlname" runat="server">
 <asp:ListItem Value="Fumi_NO">流水号</asp:ListItem>
 <asp:ListItem Value="Decl_NO">报检号</asp:ListItem>
 <asp:ListItem Value="Ent_CName">企业名称</asp:ListItem>
 <asp:ListItem Value="Goods_Name">商品名称</asp:ListItem>
 <asp:ListItem Value ="App_Date">申请日期</asp:ListItem>
 <asp:ListItem Value ="Plan_Date">生成方案日期</asp:ListItem>
 <asp:ListItem Value ="Action_Date">实施日期</asp:ListItem>
 <asp:ListItem Value ="ChargeDate">收费日期</asp:ListItem>
 </asp:DropDownList>

 <asp:DropDownList ID="ddlExpress" runat="server">
 <asp:ListItem Value="=">等于</asp:ListItem>
 <asp:ListItem Value ="like">相似</asp:ListItem>
 <asp:ListItem Value =">">大于</asp:ListItem>
 <asp:ListItem Value ="<">小于</asp:ListItem>
 <asp:ListItem Value ="!=">不等于</asp:ListItem>
 </asp:DropDownList>


 <asp:TextBox ID="txtCondition" runat="server"></asp:TextBox>


 <asp:DropDownList ID="ddlAndOr" runat="server" >
 <asp:ListItem ></asp:ListItem>
 <asp:ListItem Value="and">并且</asp:ListItem>
 <asp:ListItem Value ="or">或者</asp:ListItem>


 </asp:DropDownList>
 
  
 <asp:TextBox ID="txtSQLContent" runat="server" TextMode="MultiLine"></asp:TextBox>
 

 <asp:Button ID="BtnAddSQL" runat="server" Text="加入" />
 <asp:Button ID="BtnClrSQL" runat="server" Text="清除" />
 
 <asp:Button ID="BtnQuery" runat="server" Text="查询" /> 

 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
 AutoGenerateColumns="False" DataKeyNames="Fumi_NO" DataSourceID="SqlDataSource1" Width="800px"　PageSize="5">
 <Columns>
 <asp:CommandField ShowSelectButton="True" />
 <asp:BoundField DataField="Fumi_NO" HeaderText="流水号" ReadOnly="True" SortExpression="Fumi_NO" />
 <asp:BoundField DataField="Decl_NO" HeaderText="报检号" SortExpression="Decl_NO" />
 <asp:BoundField DataField="Ent_CName" HeaderText="企业" SortExpression="Ent_CName" />

<asp:BoundField DataField="App_Date" HeaderText="申请日期" SortExpression="App_Date" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />

 <asp:BoundField DataField="Plan_Date" HeaderText="方案日期" SortExpression="Plan_Date" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />

 <asp:BoundField DataField="Action_Date" HeaderText="实施日期" SortExpression="Action_Date" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />

 <asp:BoundField DataField="FirstExamDate" HeaderText="首次审核" SortExpression="FirstExamDate" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />
 <asp:BoundField DataField="LastExamDate" HeaderText="最后审核" SortExpression="LastExamDate" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />
 <asp:BoundField DataField="ChargeDate" HeaderText="计费日期" SortExpression="ChargeDate" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}" />

 </Columns>
 </asp:GridView>

相关代码页内容：

Protected Sub BtnAddSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnAddSQL.Click
        If ddlExpress.SelectedValue = "like" Then
            txtSQLContent.Text = txtSQLContent.Text & " " & ddlAndOr.SelectedValue.ToString & " " & ddlname.SelectedValue.ToString & " " & ddlExpress.SelectedValue.ToString & " '%" & txtCondition.Text & "%' "
        Else
            txtSQLContent.Text = txtSQLContent.Text & " " & ddlAndOr.SelectedValue.ToString & " " & ddlname.SelectedValue.ToString & " " & ddlExpress.SelectedValue.ToString & " '" & txtCondition.Text & "' "
        End If

End Sub

Protected Sub BtnQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnQuery.Click
        SqlDataSource1.FilterExpression = txtSQLContent.Text
        GridView1.DataBind()
    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        SqlDataSource1.FilterExpression = txtSQLContent.Text
        GridView1.DataBind()
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        '解决报检号在导出后格式不对的问题
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(2).Attributes.Add("style", "vnd.ms-excel.numberformat:@;")
            '@表示文本，G/通用格式　表示　常规,0.00_):[红色](0.00)　表示　小数，
        End If
    End Sub

    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
        SqlDataSource1.FilterExpression = txtSQLContent.Text
        GridView1.DataBind()
    End Sub

Protected Sub BtnClrSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnClrSQL.Click
txtSQLContent.Text = ""
End Sub