CODEWITHSUNDEEP
sqlvb.netvisual-studio-2012
User113
User113

Reputation: 71

SQL query not performing as expected : Visual Studio

I have a program that populates a page with search results using a query to draw them from a database. The user can select multiple options from drop-down menus and it would only return results that contained the specific selections, however when I try to use it, it returns results that do not contain the user's selections. Here's one of the queries I use (the others are very, very similar, experiencing the same problem).

Dim o = From c In myContext.Competitions.Include("CodeJusticeBranches").Include("CodeJusticeLocations").Include("CodeCompetitionTypes").Include("CodePositionTypes").Include("CompetitionPositions") _
        Where (pstrCompNum Is Nothing OrElse c.comp_number = pstrCompNum) _
          And (pstrCompYear Is Nothing OrElse c.comp_number.Length <= 8 And c.comp_number.StartsWith(strYear) = True) _
           Or (pstrCompYear Is Nothing OrElse c.comp_number.Length > 8 And c.comp_number.Substring(6, 2) = strYear) _
          And (pstrCompTypeId Is Nothing OrElse c.CodeCompetitionTypes.code_ct_id = CInt(pstrCompTypeId)) _
          And (pstrBranchId Is Nothing OrElse c.CodeJusticeBranches.code_branch_id = CInt(pstrBranchId)) _
          And (pstrPosTypeId Is Nothing OrElse c.CodePositionTypes.code_pos_type_id = CInt(pstrPosTypeId)) _
        Order By c.comp_number _
        Select c

If o.Count > 100 Then
    Throw New FaultException("Your search has returned more than 100 Competitions - please narrow your search")
Else
    Return o.ToList
End If

The way the database is set up, if a year is not selected, as in I just select a branchId, PosTypeId or CompTypeId there are over 100 results, so the list won't be returned. That's not the problem. The problem comes when a year is selected as well as the other parameters (we're ignoring CompNum since we want multiple results, not a specific Num).

What happens is the list will contain all the elements for that year, not even looking at the other parameters.

For example, if I asked it to find all the elements that had a branch of 1, a PosType of 2 and a compType of 3 in the year 2019, it will return all the elements in the year 2019. It will ignore the other search criteria.

Any suggestions would be immensely appreciated. Thanks.

Upvotes: 0

Views: 47

Answers (1)

Melissa Guzman
Melissa Guzman

Reputation: 56

Add parentheses before and after the pstrCompYear filter, so the Or doesn't affect the rest of the conditions.

And ((pstrCompYear Is Nothing OrElse c.comp_number.Length <= 8 And c.comp_number.StartsWith(strYear) = True) _
       Or (pstrCompYear Is Nothing OrElse c.comp_number.Length > 8 And c.comp_number.Substring(6, 2) = strYear)) _

Upvotes: 1

Related Questions