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=” /> ) 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:label ID="lblNames" runat="server" text='’ />
–additional template fields here —
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="”
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”>
Any help appreciated!
TIA
Elaine
