?
Solved

Asp.net <asp:AccessDataSource Select Parameters

Posted on 2011-04-26
17
Medium Priority
?
1,491 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

0
Comment
Question by:TonyReba
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 4

Expert Comment

by:thepanch
ID: 35471478
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)
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35471602
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

0
 
LVL 9

Author Comment

by:TonyReba
ID: 35471609
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?


0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35471837
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
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35472078
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?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35472543
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".
0
 
LVL 4

Expert Comment

by:thepanch
ID: 35472788
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35473811
Try removing the parameters from aspx and using the code behind to apply the filtering by changing the sql query.
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35474705
This data soueecw bound to listview ctrl. Do I need to program aticallt define paging?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35474725
@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>
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35475117
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

0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35475159
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35475177
Also Im assuming that your specialityDropDown contains text for it values, not integer values...
0
 
LVL 4

Expert Comment

by:thepanch
ID: 35475936
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.
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35475983
On this page is fine. If I del ete. Those function as I only need se lecture for searching. And update.
0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 35476103
@thepanch

I think that Tony have enough knowledge to do that, just must copy that values from his original accessdatasource to this and it is ready to go, but well, here is the copy & paste:
<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]"

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="@pDdlValue" PropertyName="SelectedValue"
	    Type="String" ConvertEmptyStringToNull="true" />
	<asp:ControlParameter ControlID="providerTextBox" Name="@pTextBoxValue" PropertyName="Text"
	    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

0
 
LVL 9

Author Comment

by:TonyReba
ID: 35476109
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

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question