Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    11

    Unanswered: "ORDER BY" issue

    ASP .NET
    SQL 2005

    Trying to sort a search on products by GroupID in the MPDB_ItemTypes Table.

    Code:
    Public Shared Function GetFurniture(ByVal iCollectionID As Integer, ByVal iRoomGroupID As Integer, ByVal iItemTypeID As Integer, ByVal iStyleGroupID As Integer, ByVal iPhotoTypeID As Integer, ByVal sStyleNumber As String, ByVal sSearchText As String, ByVal iStartIndex As Integer, ByVal iPageSize As Integer, ByRef iVirtualItemCount As Integer) As HCDataset.SearchResultsDataTable
            Dim cn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection
            cn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
            cn.Open()
    
            Dim iEndIndex As Integer = iStartIndex + iPageSize - 1 'we may override the iEndIndex below
            If iPageSize > 0 Then
                Dim cmdGetFurnitureCount As New System.Data.SqlClient.SqlCommand
                cmdGetFurnitureCount.Connection = cn
                cmdGetFurnitureCount.CommandType = Data.CommandType.Text
                cmdGetFurnitureCount.CommandText = "SELECT COUNT(*) " & _
                                                      "FROM MPDB_Items I " & _
                                                       "INNER JOIN MPDB_Photos P ON I.PhotoID = P.PhotoID " & _
                                                       "INNER JOIN MPDB_Collections C ON I.CollectionID = C.CollectionID " & _
                                                       "LEFT OUTER JOIN MPDB_PhotoTypes T ON P.PhotoTypeID = T.PhotoTypeID " & _
                                                       "LEFT OUTER JOIN MPDB_StyleGroups S ON C.StyleGroupID = S.StyleGroupID " & _
                                                      "WHERE I.ShowOnWeb = 1 AND NOT I.FriendlyDescription IS NULL AND " & _
                                                        "((@CollectionID = 0) OR (C.CollectionID = @CollectionID)) AND " & _
                                                        "((@RoomGroupID = 0) OR (I.RoomGroupID = @RoomGroupID)) AND " & _
                                                        "((@StyleGroupID = 0) OR (C.StyleGroupID = @StyleGroupID)) AND " & _
                                                        "((@PhotoTypeID = 0) OR (P.PhotoTypeID = @PhotoTypeID)) AND " & _
                                                        "((@StyleNumber = '') OR (I.StyleNumber LIKE '%' + @StyleNumber + '%')) AND " & _
                                                        "(" & _
                                                         "(@SearchText = '') OR " & _
                                                         "((NOT EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND ((I.StyleNumber LIKE '%' + REPLACE(@SearchText, '-', '') + '%') OR (UPPER(I.FriendlyDescription) LIKE '%' + UPPER(@SearchText) + '%') OR (UPPER(I.Comments) LIKE '%' + UPPER(@SearchText) + '%'))) OR " & _
                                                         "(    (EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND (I.ItemTypeID IN (SELECT ItemTypeID FROM Keywords_Relate_ItemTypes INNER JOIN Keywords ON Keywords.ID = Keywords_Relate_ItemTypes.KeywordID AND UPPER (Keywords.Keyword) = UPPER (@SearchText)))) " & _
                                                        ") "
                cmdGetFurnitureCount.Parameters.Add("@CollectionID", Data.SqlDbType.Int).Value = iCollectionID
                cmdGetFurnitureCount.Parameters.Add("@RoomGroupID", Data.SqlDbType.Int).Value = iRoomGroupID
                cmdGetFurnitureCount.Parameters.Add("@StyleGroupID", Data.SqlDbType.Int).Value = iStyleGroupID
                cmdGetFurnitureCount.Parameters.Add("@PhotoTypeID", Data.SqlDbType.Int).Value = iPhotoTypeID
                cmdGetFurnitureCount.Parameters.Add("@StyleNumber", Data.SqlDbType.VarChar).Value = IIf(Not sStyleNumber Is Nothing, sStyleNumber, "")
                cmdGetFurnitureCount.Parameters.Add("@SearchText", Data.SqlDbType.VarChar).Value = IIf(Not sSearchText Is Nothing, sSearchText, "")
                iVirtualItemCount = cmdGetFurnitureCount.ExecuteScalar
                If iEndIndex > iVirtualItemCount Then
                    iPageSize = iPageSize - (iEndIndex - iVirtualItemCount)
                    iEndIndex = iVirtualItemCount
                End If
            End If
    
            'Note that the "ORDER BY" uses a tricky way of determing if there are any alphabetical characters in the sSearchText by comparing the .ToUpper with the .ToLower
            Dim cmdGetFurniture As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
            cmdGetFurniture.Connection = cn
            cmdGetFurniture.CommandType = Data.CommandType.Text
            cmdGetFurniture.CommandText = IIf(iPageSize > 0, "SELECT * FROM (SELECT TOP " & iPageSize & " * FROM (SELECT TOP " & iEndIndex, "SELECT ") & _
                                            "I.ItemID AS ID, I.FriendlyDescription AS Description, I.StyleNumber, P.FileName, 1 AS Type " & _
                                          "FROM MPDB_Items I " & _
                                           "INNER JOIN MPDB_Photos P ON I.PhotoID = P.PhotoID " & _
                                           "INNER JOIN MPDB_Collections C ON I.CollectionID = C.CollectionID " & _
                                           "LEFT OUTER JOIN MPDB_PhotoTypes T ON P.PhotoTypeID = T.PhotoTypeID " & _
                                           "LEFT OUTER JOIN MPDB_StyleGroups S ON C.StyleGroupID = S.StyleGroupID " & _
                                          "WHERE I.ShowOnWeb = 1 AND NOT I.FriendlyDescription IS NULL AND " & _
                                            "((@CollectionID = 0) OR (C.CollectionID = @CollectionID)) AND " & _
                                            "((@RoomGroupID = 0) OR (I.RoomGroupID = @RoomGroupID)) AND " & _
                                            "((@StyleGroupID = 0) OR (C.StyleGroupID = @StyleGroupID)) AND " & _
                                            "((@PhotoTypeID = 0) OR (P.PhotoTypeID = @PhotoTypeID)) AND " & _
                                            "((@StyleNumber = '') OR (I.StyleNumber LIKE '%' + @StyleNumber + '%')) AND " & _
                                            "(" & _
                                             "(@SearchText = '') OR " & _
                                             "((NOT EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND ((I.StyleNumber LIKE '%' + REPLACE(@SearchText, '-', '') + '%') OR (UPPER(I.FriendlyDescription) LIKE '%' + UPPER(@SearchText) + '%') OR (UPPER(I.Comments) LIKE '%' + UPPER(@SearchText) + '%'))) OR " & _
                                             "(    (EXISTS (SELECT TOP 1 * FROM Keywords WHERE UPPER(Keyword) = UPPER(@SearchText))) AND (I.ItemTypeID IN (SELECT ItemTypeID FROM Keywords_Relate_ItemTypes INNER JOIN Keywords ON Keywords.ID = Keywords_Relate_ItemTypes.KeywordID AND UPPER (Keywords.Keyword) = UPPER (@SearchText)))) " & _
                                            ") " & _
                                         "ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "I.StyleNumber", "I.FriendlyDescription") & _
            IIf(iPageSize > 0, ") NoBottom ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "StyleNumber", "Description") & " DESC) NoTop ORDER BY " & IIf(Not [String].IsNullOrEmpty(sSearchText) AndAlso sSearchText.ToUpper = sSearchText.ToLower, "StyleNumber", "Description"), "")
            cmdGetFurniture.Parameters.Add("@CollectionID", Data.SqlDbType.Int).Value = iCollectionID
            cmdGetFurniture.Parameters.Add("@RoomGroupID", Data.SqlDbType.Int).Value = iRoomGroupID
            cmdGetFurniture.Parameters.Add("@ItemTypeID", Data.SqlDbType.Int).Value = iItemTypeID
            cmdGetFurniture.Parameters.Add("@StyleGroupID", Data.SqlDbType.Int).Value = iStyleGroupID
            cmdGetFurniture.Parameters.Add("@PhotoTypeID", Data.SqlDbType.Int).Value = iPhotoTypeID
            cmdGetFurniture.Parameters.Add("@StyleNumber", Data.SqlDbType.VarChar).Value = IIf(Not sStyleNumber Is Nothing, sStyleNumber, "")
            cmdGetFurniture.Parameters.Add("@SearchText", Data.SqlDbType.VarChar).Value = IIf(Not sSearchText Is Nothing, sSearchText, "")
    
            Dim daGetFurniture As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
            daGetFurniture.SelectCommand = cmdGetFurniture
    
            Dim dtSearchResults As HCDataset.SearchResultsDataTable = New HCDataset.SearchResultsDataTable
            daGetFurniture.Fill(dtSearchResults)
    
            If iPageSize <= 0 Then
                iVirtualItemCount = dtSearchResults.Rows.Count
            End If
    
            cn.Close()
    
            Return dtSearchResults
        End Function
    I want to sort the results by the GroupID field located in the MPDB_ItemTypes Table.

    Example: Lets says that there are 4 items in the MPDB_Items table

    Plate (ItemTypeID = 1 & GroupID = 1)
    Glass (ItemTypeID = 3 & GroupID = 2)
    Bowl (ItemTypeID = 4 & GroupID = 3)
    Silverware (ItemTypeID = 2 & GroupID = 4)

    Both Tables MPDB_Item & MPDB_ItemTypes have the field ItemTypeID (int). So, that is how they will be joined together.

    What I need to do is if I search for a product # of 44 it will show all products that have 44 in the # (this is done and works fine). When I go to view the results it shows them in the ORDER BY GroupID.

    So, I would see all Plates together, Glass together, Bowls together, and Silverware together. At the present time it ORDER BY is done via determing if there are any alphabetical characters in the sSearchText by comparing the .ToUpper with the .ToLower

    Right now whatever I try I fail on and the site throws back compile errors and such. I am new to .NET and SQL but need this to work soon as possible. Any ideas would be appreciated.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's a lot of unpleasant looking vb code to wade through.

    I think you'll fare better with responses from regulars here if you post just the sql, minus the vb, nicely indented and formatted. just a thought.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think he'll get better responses if he decides to do this properly and create a sproc to return the resultset. I, and probably other forum members, tend to be reluctant to advise people on how to implement bad designs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    agreed. write a proc.
    Last edited by jezemine; 03-21-07 at 03:03.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •