locked
Where Statement With DropDownList.SelectedValue RRS feed

  • Question

  • User-706647060 posted

    hi ! I have a acceaadatasource select commond like below;

    SELECT * FROM MyTable WHERE ([MyDate] = @parameter1)


    And I have Dropdownlist.SelectedValue as select parameter which has a name parameter1

    This works good but when my parameter selectedvalue is empty it shows the datalist as empty.

    What I want is that when the dropdownlist.selectedvalue is empty, datalist should show all the values !

    How can I do that ? thanks !

    Saturday, May 15, 2010 6:02 AM

Answers

  • User-1179452826 posted

    WHERE( @parameter is null OR [MyDate] = @parameter)

    and ensure that convert empty strings to nulls is set to true on the datasource. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 15, 2010 6:59 AM
  • User-1179452826 posted

    Part of what you need:

            <asp:AccessDataSource CancelSelectOnNullParameter="false">
                <SelectParameters>
                    <asp:ControlParameter ControlID='ddlId' ConvertEmptyStringToNull="true" />
                </SelectParameters>
            </asp:AccessDataSource>


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 15, 2010 7:31 AM

All replies

  • User-1179452826 posted

    WHERE( @parameter is null OR [MyDate] = @parameter)

    and ensure that convert empty strings to nulls is set to true on the datasource. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 15, 2010 6:59 AM
  • User-706647060 posted

    Hi ! thanks for your help ! I will try that !

    and ensure that convert empty strings to nulls is set to true on the datasource. 

    what do you mean by that ? where can I do that ?

    Saturday, May 15, 2010 7:18 AM
  • User-1179452826 posted

    Part of what you need:

            <asp:AccessDataSource CancelSelectOnNullParameter="false">
                <SelectParameters>
                    <asp:ControlParameter ControlID='ddlId' ConvertEmptyStringToNull="true" />
                </SelectParameters>
            </asp:AccessDataSource>


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 15, 2010 7:31 AM
  • User-706647060 posted

    Thanks a lot ! Worked perfect !

            <asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server">
            <asp:ListItem Selected="True" Value="" Text="Seçim"></asp:ListItem>
            <asp:ListItem Text="Deneme" Value="Dalyan Daily"></asp:ListItem>
            </asp:DropDownList>
        <br />
        <asp:DataList ID="DataList1" DataSourceID="AccessDataSource23" runat="server">
    <ItemTemplate>
        <asp:Label ID="Label25" runat="server" Text='<%# Eval("DateOfIT")%>'></asp:Label>
    </ItemTemplate>
    </asp:DataList>
    
    <asp:AccessDataSource CancelSelectOnNullParameter="false" ID="AccessDataSource23" runat="server" DataFile="~/App_Data/KoralSurvey.mdb" 
    SelectCommand="SELECT distinct FORMAT([DateOfSurvey], 'DD.MMMM, YYYY') As DateOfIT FROM [Surveys]  WHERE (FORMAT([DateOfSurvey], 'YYYY') = '2010') AND (@par1 IS NULL OR [SurveyType] = @Par1)">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" ConvertEmptyStringToNull="true" Type="String" PropertyName="SelectedValue" Name="Par1" />
    </SelectParameters>
    </asp:AccessDataSource>



    Saturday, May 15, 2010 7:39 AM
  • User-706647060 posted

    Hi again !

    I used something as below;

            Dim Access1ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|KoralSurvey.mdb"
            Dim Access1SelectQuery As String = "SELECT COUNT(*) As CountOF FROM [Surveys] WHERE ([SurveyType] = ?)  AND (FORMAT([DateOfSurvey], 'YYYY') = 2010)"
            'Dim myConnectionAccess1 As New OleDbConnection(Access1ConStr)
            'Dim myCommand As New OleDbCommand(Access1SelectQuery, myConnectionAccess1)
            Using connAccess1 As New OleDbConnection(Access1ConStr)
                Using cmdAccess1 As New OleDbCommand(Access1SelectQuery, connAccess1)
                    cmdAccess1.CommandType = CommandType.Text
                    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)
                    connAccess1.Open()
                    Using readerAccess1 As OleDbDataReader = cmdAccess1.ExecuteReader()
                        While readerAccess1.Read()
                            'Response.Write(reader("FirstName").ToString() + " " + reader("LastName").ToString())
                            'MsgBox(readerAccess1("CountOF").ToString())
                            If Not DropDownList1.SelectedValue = "" Then
                                Label1.Text = "<i><b>'" + readerAccess1("CountOF").ToString() + "'</b></i>" + " Survey Is Calculated In This Satatistical Report !"
                                GLBTotVal = readerAccess1("CountOF").ToString()
                            Else
                                Label1.Text = "No Result Is Detected!"
                            End If
                        End While
                    End Using
                End Using
            End Using


    And I want the same thing here. If the dropdownlist.selectedvalue is null, it should show all data.

    But how can I say here the below one;

            <asp:AccessDataSource CancelSelectOnNullParameter="false">
                <SelectParameters>
                    <asp:ControlParameter ControlID='ddlId' ConvertEmptyStringToNull="true" />
                </SelectParameters>
            </asp:AccessDataSource>


    Saturday, May 15, 2010 11:15 AM
  • User-706647060 posted

    How can I indicate this in code behind;

            <asp:AccessDataSource CancelSelectOnNullParameter="false">
                <SelectParameters>
                    <asp:ControlParameter ControlID='ddlId' ConvertEmptyStringToNull="true" />
                </SelectParameters>
            </asp:AccessDataSource>



    Saturday, May 15, 2010 11:41 AM
  • User-1199946673 posted

    And I want the same thing here. If the dropdownlist.selectedvalue is null, it should show all data.
     

    This is explained in this article:

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    Saturday, May 15, 2010 1:29 PM
  • User-706647060 posted

    Hi hans ! as you can see above, my datasource is not a datasource in aspx page. It is source in VB page. So I am not familiar to set CancelSelectOnNullParameter as False in code behind. Can you give me a hand here pls?


    Saturday, May 15, 2010 4:30 PM
  • User-1179452826 posted

    WARNING: Pseudocode

    Dim Access1SelectQuery As String = "SELECT COUNT(*) As CountOF FROM [Surveys] WHERE ([SurveyType] = ? OR ? is null)  AND (FORMAT([DateOfSurvey], 'YYYY') = 2010)"   

    ...

    ..

    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)

    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)

     

    explanation:

    Access doesn't support named parameters like SQL (as far as I know). So, just pass in the parameter twice. The first ? will refer to the first param added and the second ? will do the same for the second. The reason for the warning:

    1. You may need to give two distinct names for the two parameters when adding to cmdAccess.Parameters.

    2. For the second, if nothing is selected, be sure to pass in null (or DBNull or DBNull.Value - one of them should work).

    3. Be on the lookuout for null reference exceptions when doing SelectedItem.ToString()

    The solution presented here will work, but will need a little testing to get it right (coz of the 3 warning areas).

    Saturday, May 15, 2010 5:13 PM
  • User-1199946673 posted

    Access doesn't support named parameters like SQL (as far as I know)
     

    Although MSDN is telling otherwise, Access does support named parameters!

    1. You may need to give two distinct names for the two parameters when adding to cmdAccess.Parameters.

    No, because OleDb parameters are recognized by position, the names are not relevant at all. You could even give all parameters the same name, even if you're referring to different fields in the query!

    Dim Access1SelectQuery As String = "SELECT COUNT(*) As CountOF FROM [Surveys] WHERE ([SurveyType] = ? OR ? is null)  AND (FORMAT([DateOfSurvey], 'YYYY') = 2010)"   

    ...

    ..

    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)

    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)

    So this will work, but the following will work also:

    Dim Access1SelectQuery As String = "SELECT COUNT(*) As CountOF FROM [Surveys] WHERE ([SurveyType] = @SurveyType OR @SurveyType is null)  AND (year([DateOfSurvey]) = 2010)"   

    ...

    ..

    cmdAccess1.Parameters.AddWithValue("SurveyType", DropDownList1.SelectedItem.ToString)

     

    Saturday, May 15, 2010 5:41 PM
  • User-1199946673 posted

    So I am not familiar to set CancelSelectOnNullParameter as False in code behind. Can you give me a hand here pls?
     

    This parameter isn't really relevant when you're using code behind. Only when using a datasource in your aspx page, you need to set this parameter otherwise the datasource will not execute the SELECT statement when one or more parameters are empty. In code behind, you decide when to execute the statement

    Saturday, May 15, 2010 5:45 PM