locked
Help with Access/SQL Selectcommand and Session RRS feed

  • Question

  • User1128081051 posted

    Hi everyone,

    I have the following two pages. One is a search page that have a few listbox/textbox/and dropdownlist. The other will be the result page that take from session those 3 data and using an if/else statement to sort out some detail. After that it will form an access/sql selectcommand to search the database base on that 3 criteria.  But for some weird reason only the first part of my if and else statement work. Can anyone check and help me out why the rest of the if/else doesn't work. Or point me to another direction?

    ---------------------Search.aspx-----------------------------

        Sub Page_Transfer(ByVal sender As Object, ByVal e As EventArgs)
            'Storing in Context
            Session("SearchBox") = SearchBox.Text
            Session("ListBox1") = ListBox1.Text
            Session("DropDownList") = DropDownList.Text
            Server.Transfer("SearchResult.aspx")
            'DataTextFormatString
        End Sub


    -------------------SearchResult.aspx-----------------------

        Protected Overrides Sub OnLoad(ByVal e As EventArgs)
            MyBase.OnLoad(e)
            Dim SearchBoxStr As String = CType(Session.Item("SearchBox"), String)
            Dim ListBoxStr As String = CType(Session.Item("ListBox1"), String)
            Dim DropDownListStr As String = CType(Session.Item("DropDownList"), String)
            AccessDataSource1.SelectCommand = "SELECT * FROM [Books] WHERE "
            
            If (ListBoxStr = "Keyword Anywhere" And DropDownListStr = "All Categories") Then
                'AccessDataSource1.SelectCommand = "SELECT * FROM [Books] WHERE ([Category] = 'Computer')"
                'AccessDataSource1.SelectCommand += String.Format("[Category] = 'Computer'")
                'AccessDataSource1.SelectCommand += String.Format("And [Title] = 'MySQL'")
                AccessDataSource1.SelectCommand += String.Format(" [Title] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @Search + '%'", SearchBoxStr)
            ElseIf ListBoxStr = "Keyword Anywhere" And DropDownListStr <> "All Categories" Then
                AccessDataSource1.SelectCommand += String.Format(" [Category] = @DropDownListID + '%'", DropDownListStr)
                AccessDataSource1.SelectCommand += String.Format(" And ([Title] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @Search + '%')", SearchBoxStr)
            ElseIf ListBoxStr <> "Keyword Anywhere" And DropDownListStr = "All Categories" Then
                AccessDataSource1.SelectCommand += String.Format(" [Title] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @Search + '%'", SearchBoxStr)
            Else
                AccessDataSource1.SelectCommand += String.Format(" @ListBoxID = @Search + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" And [Category] = @DropDownListID + '%'", DropDownListStr)
            End If
        End Sub
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/Books.accdb" SelectCommand = "" >
            <SelectParameters>
                <asp:SessionParameter Name="SearchID" SessionField="SearchBox" Type="String" />
                <asp:SessionParameter Name="ListBoxID" SessionField="ListBox1" Type="String" />
                <asp:SessionParameter Name="DropDownListID" SessionField="DropDownList" Type="String" />
            </SelectParameters>
        </asp:AccessDataSource>



     

    Monday, November 22, 2010 2:15 AM

Answers

  • User1128081051 posted

    It's still the same. It doesn't work. But I think it has something to do with the SQL statement

    and its inability to recognize my session input. Because  I changed the if and else statement into

    a more simpilier SQL statement it worked. Here is the code to that.

    Dim SearchBoxStr As String = Session.Item("SearchBox")
            Dim ListBoxStr As String = Session.Item("ListBox1")
            Dim DropDownListStr As String = Session.Item("DropDownList")
            
            
            
            AccessDataSource1.SelectCommand = "SELECT * FROM [Books] WHERE "
            
            If (ListBoxStr = "Keyword Anywhere") And (DropDownListStr = "All Categories") Then
                AccessDataSource1.SelectCommand += String.Format(" [Title] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @SearchID + '%'", SearchBoxStr)
            ElseIf (ListBoxStr = "Keyword Anywhere") And (DropDownListStr <> "All Categories") Then
                If DropDownListStr = "Computer" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Computer' ")
                ElseIf DropDownListStr = "Literature & Fiction" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Literature & Fiction' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Reference' ")
                ElseIf DropDownListStr = "Romance" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Romance' ")
                ElseIf DropDownListStr = "Science" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Science' ")
                ElseIf DropDownListStr = "Home Design" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Home Design' ")
                ElseIf DropDownListStr = "Accounting & Finance" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Accounting & Finance' ")
                ElseIf DropDownListStr = "Horror" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Horror' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Reference' ")
                End If
                AccessDataSource1.SelectCommand += String.Format("And ([Title] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @SearchID + '%')", SearchBoxStr)
            ElseIf (ListBoxStr <> "Keyword Anywhere") And (DropDownListStr = "All Categories") Then
                If ListBoxStr = "Title" Then
                    AccessDataSource1.SelectCommand += String.Format("[Title] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Author(s)" Then
                    AccessDataSource1.SelectCommand += String.Format("[Author] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Publisher" Then
                    AccessDataSource1.SelectCommand += String.Format("[Publisher] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "ISBN" Then
                    AccessDataSource1.SelectCommand += String.Format("[ISBN] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Price" Then
                    AccessDataSource1.SelectCommand += String.Format("[Price] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Category" Then
                    AccessDataSource1.SelectCommand += String.Format("[Category] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Year" Then
                    AccessDataSource1.SelectCommand += String.Format("[Year] Like @SearchID + '%'", SearchBoxStr)
                End If
                
            Else
                If ListBoxStr = "Title" Then
                    AccessDataSource1.SelectCommand += String.Format("[Title] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Author(s)" Then
                    AccessDataSource1.SelectCommand += String.Format("[Author] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Publisher" Then
                    AccessDataSource1.SelectCommand += String.Format("[Publisher] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "ISBN" Then
                    AccessDataSource1.SelectCommand += String.Format("[ISBN] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Price" Then
                    AccessDataSource1.SelectCommand += String.Format("[Price] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Category" Then
                    AccessDataSource1.SelectCommand += String.Format("[Category] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Year" Then
                    AccessDataSource1.SelectCommand += String.Format("[Year] Like @SearchID + '%'", SearchBoxStr)
                End If
    
                
                If DropDownListStr = "Computer" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Computer' ")
                ElseIf DropDownListStr = "Literature & Fiction" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Literature & Fiction' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Reference' ")
                ElseIf DropDownListStr = "Romance" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Romance' ")
                ElseIf DropDownListStr = "Science" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Science' ")
                ElseIf DropDownListStr = "Home Design" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Home Design' ")
                ElseIf DropDownListStr = "Accounting & Finance" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Accounting & Finance' ")
                ElseIf DropDownListStr = "Horror" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Horror' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Reference' ")
                End If
       
            End If
            
        End Sub


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 22, 2010 9:41 PM

All replies

  • User-231977777 posted

    hi

    put a break point and check where the problem exactly?

     

    Monday, November 22, 2010 4:07 AM
  • User-1675817941 posted

    hi,

    change the code like this

  • asssin this session at list box selected index change event   // Session("ListBox1") = ListBox1.Text   
  • asssin this session at DropDownList  selected index event   // Session("DropDownList") = DropDownList.Tex

    this may works

    Thanks :)

Monday, November 22, 2010 5:15 AM
  • User1128081051 posted

    It's still the same. It doesn't work. But I think it has something to do with the SQL statement

    and its inability to recognize my session input. Because  I changed the if and else statement into

    a more simpilier SQL statement it worked. Here is the code to that.

    Dim SearchBoxStr As String = Session.Item("SearchBox")
            Dim ListBoxStr As String = Session.Item("ListBox1")
            Dim DropDownListStr As String = Session.Item("DropDownList")
            
            
            
            AccessDataSource1.SelectCommand = "SELECT * FROM [Books] WHERE "
            
            If (ListBoxStr = "Keyword Anywhere") And (DropDownListStr = "All Categories") Then
                AccessDataSource1.SelectCommand += String.Format(" [Title] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @SearchID + '%'", SearchBoxStr)
            ElseIf (ListBoxStr = "Keyword Anywhere") And (DropDownListStr <> "All Categories") Then
                If DropDownListStr = "Computer" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Computer' ")
                ElseIf DropDownListStr = "Literature & Fiction" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Literature & Fiction' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Reference' ")
                ElseIf DropDownListStr = "Romance" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Romance' ")
                ElseIf DropDownListStr = "Science" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Science' ")
                ElseIf DropDownListStr = "Home Design" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Home Design' ")
                ElseIf DropDownListStr = "Accounting & Finance" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Accounting & Finance' ")
                ElseIf DropDownListStr = "Horror" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Horror' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format(" [Category] = 'Reference' ")
                End If
                AccessDataSource1.SelectCommand += String.Format("And ([Title] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Author] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Publisher] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [ISBN] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Price] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Category] Like @SearchID + '%'", SearchBoxStr)
                AccessDataSource1.SelectCommand += String.Format(" OR [Year] Like @SearchID + '%')", SearchBoxStr)
            ElseIf (ListBoxStr <> "Keyword Anywhere") And (DropDownListStr = "All Categories") Then
                If ListBoxStr = "Title" Then
                    AccessDataSource1.SelectCommand += String.Format("[Title] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Author(s)" Then
                    AccessDataSource1.SelectCommand += String.Format("[Author] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Publisher" Then
                    AccessDataSource1.SelectCommand += String.Format("[Publisher] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "ISBN" Then
                    AccessDataSource1.SelectCommand += String.Format("[ISBN] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Price" Then
                    AccessDataSource1.SelectCommand += String.Format("[Price] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Category" Then
                    AccessDataSource1.SelectCommand += String.Format("[Category] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Year" Then
                    AccessDataSource1.SelectCommand += String.Format("[Year] Like @SearchID + '%'", SearchBoxStr)
                End If
                
            Else
                If ListBoxStr = "Title" Then
                    AccessDataSource1.SelectCommand += String.Format("[Title] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Author(s)" Then
                    AccessDataSource1.SelectCommand += String.Format("[Author] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Publisher" Then
                    AccessDataSource1.SelectCommand += String.Format("[Publisher] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "ISBN" Then
                    AccessDataSource1.SelectCommand += String.Format("[ISBN] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Price" Then
                    AccessDataSource1.SelectCommand += String.Format("[Price] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Category" Then
                    AccessDataSource1.SelectCommand += String.Format("[Category] Like @SearchID + '%'", SearchBoxStr)
                ElseIf ListBoxStr = "Year" Then
                    AccessDataSource1.SelectCommand += String.Format("[Year] Like @SearchID + '%'", SearchBoxStr)
                End If
    
                
                If DropDownListStr = "Computer" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Computer' ")
                ElseIf DropDownListStr = "Literature & Fiction" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Literature & Fiction' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Reference' ")
                ElseIf DropDownListStr = "Romance" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Romance' ")
                ElseIf DropDownListStr = "Science" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Science' ")
                ElseIf DropDownListStr = "Home Design" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Home Design' ")
                ElseIf DropDownListStr = "Accounting & Finance" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Accounting & Finance' ")
                ElseIf DropDownListStr = "Horror" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Horror' ")
                ElseIf DropDownListStr = "Reference" Then
                    AccessDataSource1.SelectCommand += String.Format("AND [Category] = 'Reference' ")
                End If
       
            End If
            
        End Sub


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 22, 2010 9:41 PM