Asp.net MS Access for optional search criteria or Where Condition
It similar to use COALESCE in access database, or passing null parameter to access database.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CASEID"
DataSourceID="AccessDataSource1">
<Columns>
<asp:BoundField DataField="CASEID" HeaderText="CASEID" InsertVisible="False" ReadOnly="True"
SortExpression="CASEID" />
<asp:BoundField DataField="CASEIDNO" HeaderText="CASEIDNO" SortExpression="CASEIDNO" />
<asp:BoundField DataField="SERVICEID" HeaderText="SERVICEID" SortExpression="SERVICEID" />
<asp:BoundField DataField="BANKNAME2" HeaderText="BANKNAME2" SortExpression="BANKNAME2" />
<asp:BoundField DataField="Days3Notice" HeaderText="Days3Notice" SortExpression="Days3Notice" />
<asp:BoundField DataField="SERVICEIDDESC" HeaderText="SERVICEIDDESC" SortExpression="SERVICEIDDESC" />
<asp:BoundField DataField="LOANNO" HeaderText="LOANNO" SortExpression="LOANNO" />
<asp:BoundField DataField="BRANCH" HeaderText="BRANCH" SortExpression="BRANCH" />
<asp:BoundField DataField="DTI" HeaderText="DTI" SortExpression="DTI" />
<asp:BoundField DataField="USERID" HeaderText="USERID" SortExpression="USERID" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" CancelSelectOnNullParameter="False"
runat="server" DataFile="C:\database\magestic.mdb"
SelectCommand="SELECT * FROM [CaseInfo] WHERE (@USERID IS NULL OR USERID LIKE '%' + @USERID + '%') and (@BRANCH IS NULL OR BRANCH LIKE '%' + @BRANCH + '%')" OnSelecting="AccessDataSource1_Selecting">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="USERID" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBox2" Name="BRANCH" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
code behind
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (!IsPostBack)
{
e.Cancel = true;
}
}
}
1 comment:
I found this to be very instructional. Thank you for posting it! I am trying to do the same thing in VB and am having trouble with the code behind. Do you by any chance have some input on the code I'd need for the VB version of this search?
Thanks.
D. Buck
dave@djbuck.com
Post a Comment