Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Ivy, VA
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Constructing Where clause with multiple conditions in gridview?

    I have a webform (test version here: http://www.albemarle.org/nativeplantstest (choose view ALL as selection is not working) where the viewer can select any number of criteria from a standard .NET webform to display a list of found plants with the selected characteristics--we don't care if they select mutually exclusive options, but this can make a very complex Where clause. It's easily doable in a VBScript .asp using the form fields they selected from the .NET program and passed in as session vars to construct the where clause with multiple conditions, but I don't know how to do this in a Visual Studio 2008 gridview (you can see that version here: http://www.albemarle.org/nativeplants).

    The 6 displayed columns in the gridview are created in the codebehind using Eval statements (text='<%# BuildNamesCol(Eval("TheNameCol")) %>' /> ) to massage the info in "Protected Function BuildNamesCol(ByVal TheNames As String) As String" where field TheNameCol is on the Select statement in the gridview's SelectCommand:

    SELECT keyID, strToken,(strSciName + '|' + strCommonName + '|' + strCategory + '|' + flgNativeTo +
    '|' + flgRecommended ) as TheNameCol,
    [additional fields follow]

    and "TheNames" in the function is the passed in concatenated fields in TheNameCol on the SQL. Those cols are then easily parsed out and returned back for what you see in the respective column on the gridview. This all works fine.

    However, given how complex the Where clause is (as many as 25 or so conditions, most with multiple AND and OR statements), I'd FAR rather do it via the codebehind, i.e, the equivalent of the Eval statement, I guess, but being new to gridviews, I simply don't know the syntax to do that. I did look at the FilterExpression and FilterParameter options, but it didn't seem like they would apply here. Also, this is not a stored procedure, FWIW, although if necessary it could be, I guess.

    Here's the actual gridview code if that will help--I condensed it here by removing all but the first column's template field, as the others are very similar. You can see how complex the query can be from the SelectCommand--all of those fields are potentially selectable:

    <asp:GridView ID="gvPlantList" runat="server" AllowPaging="True"
    CssClass="bodytype" HorizontalAlign="Center" PageSize="100"
    UseAccessibleHeader="False" Width="100%" AutoGenerateColumns="False" DataKeyNames="keyID"
    DataSourceID="SqlDataSource1">
    <Columns>
    <asp:TemplateField HeaderText="Scientific/Common Name" >
    <itemTemplate>
    <asp:label ID="lblNames" runat="server" text='<%# BuildNamesCol(Eval("TheNameCol")) %>' />
    </itemTemplate>
    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" />
    </asp:TemplateField>

    --additional template fields here ---

    </Columns>
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NativePlantsTESTConnectionString %>"
    SelectCommand="SELECT keyID, strToken,(strSciName + '|' + strCommonName + '|' + strCategory + '|' + flgNativeTo + '|' + flgRecommended ) as TheNameCol, flgEvaluated, flgAvail, coalesce(flgPNLightFull,'') + '|' + coalesce(flgPNLightPartialSun,'') + '|' + coalesce(flgPNLightShade,'') + '|' + coalesce(flgPNMoistureLow,'') + '|' + coalesce(flgPNMoistureMed,'') + '|' + coalesce(flgPNMoistureHigh,'') + '|' + coalesce(flgPNSeasonalWet,'') + '|' + coalesce(flgPNAquatic,'') as ThePlantNeeds, COALESCE (flgSWGreenRoof, '') + '|' + COALESCE (flgSWWetland, '') + '|' + COALESCE (flgSWSwale, '') + '|' + COALESCE (flgSWRainGarden, '') + '|' + COALESCE (flgSWBioretention, '') + '|' + COALESCE (flgSWRetentionBasinWet, '') + '|' + COALESCE (flgSWDetentionBasinDry, '') + '|' + COALESCE (flgSWStreamBuffer, '') AS TheStormWater, flgPCEdible, flgPCEasyGrow, coalesce(flgRUErosion,'') + '|' + coalesce(flgRUWildlife,'') + '|' + coalesce(strCaterpHostPlantComName,'') + '|' + coalesce(strCaterpHostPlantSciName,'') + '|' + coalesce(flgRULandscape,'') + '|' + coalesce(flgRUSubdivision,'') + '|' + coalesce(flgRUHorticulture,'') + '|' + coalesce(flgRUScreening,'') + '|' + coalesce(flgRUParkingLot,'') + '|' + coalesce(flgRUStreetscape,'') + '|' + coalesce(flgRUOpenSpace,'') +'|' + coalesce(strPNUniqueSoils,'') AS TheRecUses, coalesce(strPCFoliageColor,'') +'|' + coalesce(strPCFlowerColor,'') +'|' + coalesce(strPCBloomTime,'') +'|' + coalesce(flgPMSeed,'') +'|' + coalesce(flgPMPlug,'') +'|' + coalesce(flgPMRootBall,'') +'|' + coalesce(flgPMStaking,'') +'|' + coalesce(flgGHDeerResistant,'') +'|' + coalesce(flgGHGroundhogResistant,'') +'|' + coalesce(flgGHSpreadRapidly,'') +'|' + coalesce(flgPCGroundCover,'') +'|' + coalesce(strPCHeightLow,'') +'|' + coalesce(strPCHeightHigh,'') +'|' + coalesce(flgPCHeightIncr,'') +'|' + coalesce(flgPCHeightPlus,'') +'|' + coalesce(strPCSpreadLow,'') +'|' + coalesce(strPCSpreadHigh,'') +'|' + coalesce(flgPCSpreadIncr,'') +'|' + coalesce(flgPCSpreadPlus,'') as ThePlantChar, strSpecialUses, strComments, strSource, strPicture1, booActive FROM tblNativePlants WHERE (booActive = 'A') AND (flgEvaluated = 'Y') AND (flgRecommended <> 'N') ORDER BY strSciName">
    </asp:SqlDataSource>

    Any help appreciated!

    TIA
    Elaine

  2. #2
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    69
    Thanks
    12
    Thanked 4 Times in 4 Posts
    In VB.NET I typically use a DataView to set filtering on a data table derived from a dataset. Set the DataGridView datasource to the DataView for the filtered results. I'm assuming that you already have a populated dataset or at least can get that. I believe you can do this also in ASP. In my example I'm building my criteria from an enumerator value. You would probably use a string instead.

    Imports System.Data

    Private dt As New DataTable
    Private AllFiltersSelected As Boolean

    'Assumes you have a filled dataset (ds)
    dt = ds.Tables(0)


    'Examine each check box to see what has been selected and build the criteria string
    Private Sub FilterData()

    Dim dv As DataView
    Dim RowFilter As String = ""
    Dim Criteria As String = ""
    Dim Filter As Boolean = False

    Try

    If AllFiltersSelected Then

    dgvEvents.DataSource = dt
    dgvEvents.Columns(0).Visible = False

    Else

    If chkAPLAlarm.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.APLAlarm)
    RowFilter = Criteria
    Filter = True
    End If

    If chkAppConfig.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.AppConfig)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkAppError.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.AppError)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkAppEvent.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.AppEvent)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkDatabaseError.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.DatabaseError)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkFIFO.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.FIFOEvent)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkPLCAlarm.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.PLCAlarm)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkSystemStatus.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.SystemStatusEvent)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkUnknown.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.Unknown)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If

    If chkUserEvent.Checked = True Then

    Criteria = "EventTypeId = " & CStr(AppDB.EventType.UserEvent)

    If Filter = True Then RowFilter &= " OR " & Criteria Else RowFilter = Criteria
    Filter = True

    End If


    If Filter = True Then

    dv = New DataView(dt, RowFilter, "DateTimeStamp DESC", DataViewRowState.CurrentRows)
    dgvEvents.DataSource = dv
    dgvEvents.Columns(0).Visible = False

    Else

    dgvEvents.DataSource = Nothing

    End If

    End If

    Catch ex As Exception

    End Try


    End Sub
    Last edited by Bender; 2015-04-09 at 08:35. Reason: Addl. Info
    Do your part!!! Report SPAM to http://www.spamcop.net/

Posting Permissions

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