locked
Access Database Optional Parameter or where condition RRS feed

  • Question

  • User1497749907 posted

    I like to put some optional parameter in access database like following (sql-server statment) 

    declare @ClientID varchar(15)
    SELECT * FROM CaseInfo WHERE (USERID =ISNULL(@ClientID, ClientID))

    How to write above statement in access database.

    please help.

    Thanks.

     

    Monday, November 17, 2008 12:17 PM

Answers

All replies

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 17, 2008 2:03 PM
  • User1497749907 posted

    Thanks for reply, but NULL value as parameter is not working for me my code is as following, please help

    <%@ 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" runat="server" DataFile="C:\sites\database\magestic.mdb"
                SelectCommand="SELECT * FROM [CaseInfo] WHERE (@USERID IS NULL OR USERID LIKE '%' +  @USERID + '%') and (@BRANCH IS NULL OR BRANCH LIKE '%' +  @BRANCH + '%')">
                <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>

     

    Monday, November 17, 2008 2:43 PM
  • User-821857111 posted

    You didn't read all the article:

    And then we need to change the CancelSelectOnNullParameter property of the AccessDataSource to false. This is true by default, and cancels the DataSource control's Selecting event during Page_Load if NULL parameters are passed, which is what you would normally want. But in this instance, you are allowing users to pass NULL values to parameters, so it needs to be changed. This can be done simply is the Properties panel for the DataSource control.


    Monday, November 17, 2008 2:48 PM
  • User1497749907 posted

    I read in full able to fix this. Thank you very much.

    Monday, November 17, 2008 4:40 PM