Asp.net Access database for optional search criteria or Where Condition Code Sample

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;
}
}
}