We help IT Professionals succeed at work.

Asp.net <asp:AccessDataSource Select Parameters

Medium Priority
1,693 Views
Last Modified: 2012-05-11

I am building a very simple page which contains a listview control bounded to a AccessDataSource,

The problem I am facing is that I want to "search" by using a texbox  value, a dropdown slected value or both ,,,  this is the code

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Providers.mdb"
        SelectCommand="SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name]
         FROM [tbl_Providers]  Where   
    
        ( (@SpecialtyParam IS NULL OR Specialty = @SpecialtyParam ) OR
        (@SpecialtyParam IS NULL OR Specialty2= @SpecialtyParam )       
       ) 
       
       
       
       Order By Last_Name  
       
       
          "
                               

        DeleteCommand = " DELETE * From [tbl_Providers] WHERE [ID] = ?" 
        UpdateCommand="UPDATE [tbl_Providers] SET [Provider_Type] = ?, [Last_Name] = ?, [First_Name] = ?, [Prof_Designation] = ?, [Group_Business_Name] = ?, [Specialty] = ?, [Specialty2] = ?, [Address] = ?, [Address2] = ?, [City] = ?, [State] = ?, [Zip] = ?, [Phone] = ?, [Fax] = ? WHERE [ID] = ? "    >
        
        <SelectParameters>
      
                <asp:ControlParameter ControlID="specialityDropDown" Name="SpecialtyParam" 
                  PropertyName="SelectedValue" />
                  
              </SelectParameters>
                 
        <UpdateParameters>
            <asp:Parameter Name="Provider_Type" Type="String" />
            <asp:Parameter Name="Last_Name" Type="String" />
            <asp:Parameter Name="First_Name" Type="String" />
            <asp:Parameter Name="Prof_Designation" Type="String" />
            <asp:Parameter Name="Group_Business_Name" Type="String" />
            <asp:Parameter Name="Specialty" Type="String" />
            <asp:Parameter Name="Specialty2" Type="String" />
            <asp:Parameter Name="Address" Type="String" />
            <asp:Parameter Name="Address2" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="Phone" Type="String" />
            <asp:Parameter Name="Fax" Type="String" />
               
        </UpdateParameters>

        <DeleteParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
    </asp:AccessDataSource>

Open in new window

Comment
Watch Question

Commented:
hi,

you have to set a drop down, the textbox or both, and then in the code behind (in the search event button), you have to do something like this.

try
        {
            this.AccessDataSource1.SelectParameters["NOMBRE"].DefaultValue = this.txtAlumno.Text.Trim();
            this.AccessDataSource1.SelectParameters["Clave"].DefaultValue = this.txtClave.Text.Trim();
            this.AccessDataSource1.SelectParameters["ID_INCORPORADORA"].DefaultValue = this.DDLIncorporadora.SelectedValue.ToString();
            this.AccessDataSource1.SelectParameters["CURP"].DefaultValue = this.txtCURP.Text.Trim();
            this.sqlAlumnos.SelectParameters["ID_GRADOACADEMICO"].DefaultValue = this.DDLGradoAcademico.SelectedValue.ToString();
            this.sqlAlumnos.DataBind();

        }
        catch (Exception exc)

Author

Commented:
I am able to select , or pull by using a parameter query , but the problem is that it does not work when I use this:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Put user code to initialize the page here
        If Not IsPostBack Then
            ListView1.DataBind()
        End If

    End Sub

    Protected Sub searchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click

        ListView1.Visible = True
        ListView1.DataBind()

        Dim frmSpecialty As String = specialityDropDown.SelectedValue

        If specialityDropDown.SelectedValue = "All" Then

            ' Show All Results 
            Dim sql12 As String = "SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers]"
            AccessDataSource1.SelectCommand = sql12
            ListView1.DataBind()
            ListView1.Visible = True


        End If


    End Sub

Open in new window

Author

Commented:
I need to define this parameters

If specialityDropDown.SelectedValue = "All" Then

Or if there is a Value Selected

Or All and Textbox vakue entered

Or Value Selected and Texbox..

Does it make sense?


Carlos VillegasFull Stack .NET Developer

Commented:
Hello Tony, try this with your DataBase, change "~/App_Data/Database1.mdb" with your DB:
<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected void Button1_Click(object sender, EventArgs e)
    {
        GridView1.DataBind();
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridView1.DataBind();
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataSourceMode="DataSet"
            DataFile="~/App_Data/Database1.mdb" CancelSelectOnNullParameter="false"
            SelectCommand="SELECT [ID], [First_Name], [Last_Name], [Specialty], [Specialty2] FROM [tbl_Providers] WHERE ((@pDdlValue IS NULL OR [Specialty] = @pDdlValue) AND (@pTextBoxValue IS NULL OR [Specialty] LIKE '%' + @pTextBoxValue + '%')) ORDER BY [Last_Name]">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="@pDdlValue" PropertyName="SelectedValue"
                    Type="String" ConvertEmptyStringToNull="true" />
                <asp:ControlParameter ControlID="TextBox1" Name="@pTextBoxValue" PropertyName="Text"
                    Type="String" ConvertEmptyStringToNull="true" />
            </SelectParameters>
        </asp:AccessDataSource>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="AccessDataSource1">
            <EmptyDataTemplate>
                <span style="color: Red;">No results</span>
            </EmptyDataTemplate>
        </asp:GridView>
        <br />
        <asp:DropDownList ID="DropDownList1" runat="server" Width="300px" AutoPostBack="True"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
            <asp:ListItem Text="All" Value="" />
            <asp:ListItem Text="Value 1" Value="Value 1" />
            <asp:ListItem Text="Value 2" Value="Value 2" />
            <asp:ListItem Text="Value 3" Value="Value 3" />
            <asp:ListItem Text="Value 4" Value="Value 4" />
        </asp:DropDownList>
        <br />
        <asp:TextBox ID="TextBox1" runat="server" Width="300px"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" />
    </div>
    </form>
</body>
</html>

Open in new window

Here the aspx file:
 Tony.aspx

Author

Commented:
Can you help me troubleshooting my actual code, I think is possible to accomplish this but , meabe I have to delete all the select parameters on the aspx page? and use only the ones on the code behind?
Carlos VillegasFull Stack .NET Developer

Commented:
Hi Tony, well, my last example has the behaviour that you are asking for, by using only the objects and properties on the aspx file, I think that you can remove your custom code from the code behind and update your aspx file with this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataSourceMode="DataSet"
    DataFile="~/App_Data/Database1.mdb" CancelSelectOnNullParameter="false"
    SelectCommand="SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers] WHERE ((@pDdlValue IS NULL OR [Specialty] = @pDdlValue) AND (@pTextBoxValue IS NULL OR [Specialty] LIKE '%' + @pTextBoxValue + '%')) ORDER BY [Last_Name]">
    <SelectParameters>
	<asp:ControlParameter ControlID="DropDownList1" Name="@pDdlValue" PropertyName="SelectedValue"
	    Type="String" ConvertEmptyStringToNull="true" />
	<asp:ControlParameter ControlID="TextBox1" Name="@pTextBoxValue" PropertyName="Text"
	    Type="String" ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:AccessDataSource>

Open in new window


and add these controls:
<asp:DropDownList ID="DropDownList1" runat="server" Width="300px" AutoPostBack="True"
    OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
    <asp:ListItem Text="All" Value="" />
    <asp:ListItem Text="Value 1" Value="Value 1" />
    <asp:ListItem Text="Value 2" Value="Value 2" />
    <asp:ListItem Text="Value 3" Value="Value 3" />
    <asp:ListItem Text="Value 4" Value="Value 4" />
</asp:DropDownList>
<br />
<asp:TextBox ID="TextBox1" runat="server" Width="300px"></asp:TextBox>

Open in new window


You will need to fill DropDownList1 with the correct values, also make sure that your ListView control has DataSourceID="AccessDataSource1".

Commented:
to yv989c,

but what happen in the case he is asking for, you need to send parameter via textbox, dropdown, each or both?, in that case how can you handle it?, bind one control to one parameter?, and in that case, how can you send "null" when you select de "defalut" value in the control, like "" for a textbox, or "-1" for a dropdown?

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try removing the parameters from aspx and using the code behind to apply the filtering by changing the sql query.

Author

Commented:
This data soueecw bound to listview ctrl. Do I need to program aticallt define paging?
Carlos VillegasFull Stack .NET Developer

Commented:
@thepanch That is already done buddy:

WHERE ((@pDdlValue IS NULL OR [Specialty] = @pDdlValue) AND (@pTextBoxValue IS NULL OR [Specialty] LIKE '%' + @pTextBoxValue + '%')) ORDER BY [Last_Name]

and:
    <SelectParameters>
      <asp:ControlParameter ControlID="DropDownList1" Name="@pDdlValue" PropertyName="SelectedValue"
          Type="String" ConvertEmptyStringToNull="true" />
      <asp:ControlParameter ControlID="TextBox1" Name="@pTextBoxValue" PropertyName="Text"
          Type="String" ConvertEmptyStringToNull="true" />
    </SelectParameters>

Author

Commented:
Second Parameter Messes up everything
<SelectParameters>
      
                <asp:ControlParameter ControlID="specialityDropDown" Name="SpecialtyParam" 
                  PropertyName="SelectedValue" />
           

<asp:ControlParameter ControlID="providerTextBox" Name="pTextBoxValue" PropertyName="Text"
          Type="String" ConvertEmptyStringToNull="true" />

              </SelectParameters>

Open in new window

Carlos VillegasFull Stack .NET Developer

Commented:
Hi, please can you replace your Access data source with this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataSourceMode="DataSet"
    DataFile="~/App_Data/Providers.mdb" CancelSelectOnNullParameter="false"
    SelectCommand="SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers] WHERE ((@pDdlValue IS NULL OR [Specialty] = @pDdlValue) AND (@pTextBoxValue IS NULL OR [Specialty] LIKE '%' + @pTextBoxValue + '%')) ORDER BY [Last_Name]">
    <SelectParameters>
	<asp:ControlParameter ControlID="specialityDropDown" Name="@pDdlValue" PropertyName="SelectedValue"
	    Type="String" ConvertEmptyStringToNull="true" />
	<asp:ControlParameter ControlID="providerTextBox" Name="@pTextBoxValue" PropertyName="Text"
	    Type="String" ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:AccessDataSource>

Open in new window

and remove any code behind alteration of AccessDataSource1 properties.
Carlos VillegasFull Stack .NET Developer

Commented:
Also Im assuming that your specialityDropDown contains text for it values, not integer values...

Commented:
yv989c:

If i use your code, what is the time the event for "delete" or "update" is called?, i dont see any button or a submit, to commit the sql? i have used this sqldatasource just to bind data to a grid, never to delete or update data. thanks.

Author

Commented:
On this page is fine. If I del ete. Those function as I only need se lecture for searching. And update.
Full Stack .NET Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok below code works 90 % , the only thing is missing is the search all function when dropdown list value "All" s selected ... What is wrong>?
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Providers.mdb"
                       
          
        DeleteCommand = " DELETE * From [tbl_Providers] WHERE [ID] = ?" 
        UpdateCommand="UPDATE [tbl_Providers] SET [Provider_Type] = ?, [Last_Name] = ?, [First_Name] = ?, [Prof_Designation] = ?, [Group_Business_Name] = ?, [Specialty] = ?, [Specialty2] = ?, [Address] = ?, [Address2] = ?, [City] = ?, [State] = ?, [Zip] = ?, [Phone] = ?, [Fax] = ? WHERE [ID] = ? "    
        SelectCommand="SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers] WHERE  
        ((@First_Name IS NULL OR First_Name LIKE '%' +  @First_Name + '%') OR
        (@Last_Name IS NULL OR Last_Name LIKE '%' +  @Last_Name + '%')) AND
        (@Specialty IS NULL OR Specialty LIKE '%' + @Specialty + '%')" 
        CancelSelectOnNullParameter="False">
        <SelectParameters>
            <asp:ControlParameter ControlID="providerTextBox" Name="First_Name" PropertyName="Text"
                Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter ControlID="providerTextBox" Name="Last_Name" PropertyName="Text"
                Type="String" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter ControlID="specialityDropDown" Name="Specialty" PropertyName="SelectedValue"
                Type="String" ConvertEmptyStringToNull="true" />
     
        </SelectParameters>

        <UpdateParameters>
            <asp:Parameter Name="Provider_Type" Type="String" />
            <asp:Parameter Name="Last_Name" Type="String" />
            <asp:Parameter Name="First_Name" Type="String" />
            <asp:Parameter Name="Prof_Designation" Type="String" />
            <asp:Parameter Name="Group_Business_Name" Type="String" />
            <asp:Parameter Name="Specialty" Type="String" />
            <asp:Parameter Name="Specialty2" Type="String" />
            <asp:Parameter Name="Address" Type="String" />
            <asp:Parameter Name="Address2" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="Phone" Type="String" />
            <asp:Parameter Name="Fax" Type="String" />
               
        </UpdateParameters>

        <DeleteParameters>
        <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
    </asp:AccessDataSource>

Open in new window

Protected Sub searchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        ListView1.DataBind()
        ListView1.Visible = True

        '####  First We want to pull all providers #####'

        If specialityDropDown.SelectedValue = "All" Then

            ' Show All Results 
            Dim sql1 As String
            sql1 = "SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers]"
            AccessDataSource1.SelectCommand = sql1

            ListView1.Visible = True
            ListView1.DataBind()
        End If


        If specialityDropDown.SelectedValue = "All" And providerTextBox.Text <> "" Then
            ' Show me all doctors with that name
            Dim sql3 As String
            sql3 = "SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers] WHERE (First_Name LIKE '%" + providerTextBox.Text.ToString + " %' OR Last_Name LIKE '%" + providerTextBox.Text.ToString + "%'  "
            AccessDataSource1.SelectCommand = sql3
            ListView1.Visible = True
            ListView1.DataBind()

        End If

        If specialityDropDown.SelectedValue = "All" And (providerTextBox.Text <> "") Then

            ' Show All Results 
            Dim sql As String
            sql = "SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers],[Provider_Type],[Group_Business_Name] WHERE (First_Name LIKE '%" + providerTextBox.Text.ToString + " %' OR Last_Name LIKE '%" + providerTextBox.Text.ToString + "%')  "

            AccessDataSource1.SelectCommand = sql
            ListView1.Visible = True
            ListView1.DataBind()
        End If



    End Sub

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.