Archief - SQL parameter negeren

Piecemaker · 21 apr 2009

ik ben met een asp.net project bezig, en ik gebruik ergens een sql query om profielen te zoeken op bepaalde zoektermen, de query heeft een 30-tal parameters, zoals oa oogkleur, haarkleur, haarsnit, ... Al de zoektermen worden ingesteld via dropdownlists die uit de DB gevuld worden. Manueel voeg ik dan bij enkele van die zoektermen een optie 'maakt niet uit' toe, bijvoorbeeld bij oogkleur. Hoe moet ik er nu voor zorgen dat dan die parameter genegeerd wordt?

passero · 21 apr 2009

Hangt ervan af of die functie daarop voorzien is. Ofwel geef je die gewoon niet mee maar weet niet of dat gaat werken ofwel geef je NULL mee maar dan kan het zijn dat hij zoekt naar haarkleur = NULL ipv die parameter te negeren. Het hangt er dus vanaf hoe de functie opgebouwd is.

Piecemaker · 21 apr 2009

geen van beiden werkt.

de functie:

Code:

Dim cmd As New OleDbCommand("SELECT PersoonlijkeInfo.* FROM PersoonlijkeInfo, WoonplaatsRegio, VoorkeurInfo " & _
                                        "WHERE PersoonlijkeInfo.GeslachtID=@geslacht AND " & _
                                        "WoonplaatsRegio.RegioID=@regio AND " & _
                                        "VoorkeurInfo.TypeID=@zoekvoor AND " & _
                                        "PersoonlijkeInfo.GeboorteDatum >= @minDatum AND " & _
                                        "PersoonlijkeInfo.GeboorteDatum <= @maxDatum AND " & _
                                        "PersoonlijkeInfo.Lengte >= @minLengte AND " & _
                                        "PersoonlijkeInfo.Lengte <= @maxLengte AND " & _
                                        "PersoonlijkeInfo.Gewicht >= @minGewicht AND " & _
                                        "PersoonlijkeInfo.Gewicht <= @maxGewicht AND " & _
                                        "PersoonlijkeInfo.OgenKleurID = @Ogen AND " & _
                                        "PersoonlijkeInfo.HaarKleurID = @Haarkleur AND " & _
                                        "PersoonlijkeInfo.HaarSnitID = @Haarsnit AND " & _
                                        "PersoonlijkeInfo.RokenID = @Roken AND " & _
                                        "PersoonlijkeInfo.DrankgebruikID = @Drankgebruik AND " & _
                                        "PersoonlijkeInfo.WoonplaatsID=WoonplaatsRegio.WoonplaatsID AND " & _
                                        "PersoonlijkeInfo.AbonnementID=VoorkeurInfo.AbonnementID")


            cmd.Parameters.AddWithValue("@geslacht", geslacht)
            cmd.Parameters.AddWithValue("@regio", regio)
            cmd.Parameters.AddWithValue("@zoekvoor", zoekvoor)
            cmd.Parameters.AddWithValue("@minDatum", Date.Today.AddYears(-maxLeeftijd))
            cmd.Parameters.AddWithValue("@maxDatum", Date.Today.AddYears(-minLeeftijd))
            cmd.Parameters.AddWithValue("@minLengte", minLengte)
            cmd.Parameters.AddWithValue("@maxLengte", maxLengte)
            cmd.Parameters.AddWithValue("@minGewicht", minGewicht)
            cmd.Parameters.AddWithValue("@maxGewicht", maxGewicht)
            cmd.Parameters.AddWithValue("@OgenKleurID", ogen)
            cmd.Parameters.AddWithValue("@HaarkleurID", haarkleur)
            cmd.Parameters.AddWithValue("@HaarSnitID", haarsnit)
            cmd.Parameters.AddWithValue("@RokenID", roken)
            cmd.Parameters.AddWithValue("@DrankgebruikID", drankgebruik)

in een andere functie filter ik hier nog verder op, maar doet hier niet echt ter zake. De functie is er dus idd niet op voorzien, hoe voorzie ik ze hierop??

Parnakra · 21 apr 2009

Bouw de query-string vooraf op?

passero · 21 apr 2009

ah maja ge gebruikt geen functie

Ge gebruikt gewoon nen query waar ge uw values van uw ddls als parameter aan doorgeeft. Das al iets totaal anders

Ge hebt 2 mogelijkheden.
Ofwel stelt ge uwe query dynamisch op:

dim sSql as String
ssq = "select * from uwTabellen where 1 = 1 "

if geslacht is not null then
ssql += " and PersoonlijkeInfo.GeslachtID=@geslacht "
cmd.Parameters.AddWithValue("@geslacht", geslacht)
end if

da doet ge voor alle parameters...

ofwel past ge uwe query aan:
WHERE (PersoonlijkeInfo.GeslachtID=@geslacht or @geslacht is null)....

Het 2de ben ik zelf nie zeker of werkt op die manier met parameters maar bon...

enticore · 21 apr 2009

@passero:
Da tweede gaat werken als hij het juist doet (default value op NULL zetten), maar optionele parameters op die manier maken ne query verschrikkelijk traag.

Vulcanor · 21 apr 2009

Ofwel zet ge elke voorwaarde tussen haakjes, dus
bv:
"(PersoonlijkeInfo.HaarKleurID = @Haarkleur) AND "

en als er dan bijvoorbeeld geen haarkleur is ingegeven zet je:
cmd.Parameters.AddWithValue("@Haarkleur", "-1 OR 1=1")

Normaal werkt dat wel

Greetz,

Piecemaker · 21 apr 2009

straks eens proberen met query dynamisch op te stellen, zal zo wel lukken dan.

nog een ander vraagje: bij het tonen van een profiel gebruik ik een formview om alles te tonen.
Op bepaalde plaatsen heb ik binnenin de formview een repeater nodig, dus binnenin de itemtemplate van de formview maak ik een repeater, die dezelfde datasource gebruikt als de formview.
Ik krijg geen enkele foutmelding, maar het veld dat door de repeater opgevuld wordt blijft leeg. Ik kan het veel simpeler oplossen dmv een label, en dit dan via een aparte query te laten opvullen, en dit werkt ook perfect. Maar we zijn verplicht om zoveel mogelijk formviews en repeaters (en detailsview, ...) te gebruiken (schoolproject).
Is een repeater binnenin een formview wel mogelijk?

Adelbert · 22 apr 2009

overigens is een query dynamisch opstellen veel veiliger, zeker als je controle doet op de informatie.

Dit om SQL injectie tegen te gaan. (wat is SQL injectie?: als bijvoorbeeld iemand bij de textbox naam "Drop table members" zet, en die effectief uitgevoerd wordt, dan zijt ge uw database kwijt door een kwagebruiker

)

Moto · 22 apr 2009

Zo dus

Where
(t.Geslacht = @geslacht OR @geslacht IS NULL) AND
(t.MaxLengte = @maxLengte OR @maxLengte IS NULL) AND
....

Zo'n Where clause zal proberen aan 1 van de 2 zaken in de OR clause te voldoen,
als een param dus idd NULL doet is het voor hem gedaan, anders gaat hij toch vergelijken

overigens is een query dynamisch opstellen veel veiliger, zeker als je controle doet op de informatie. Dit om SQL injectie tegen te gaan.

controle op parameters gaat ook ze

Edit: was dus al gepost zie ik nu

Da tweede gaat werken als hij het juist doet (default value op NULL zetten), maar optionele parameters op die manier maken ne query verschrikkelijk traag.

Heb daar vroeger in Sql Server nooit problemen mee gehad, tis wel zo dat hem geen deftig execution plan kan opstellen voor deze queries.

Dynamisch is altijd mogelijk maar vind dat gewoon niet proper uitzien, + mogelijke fouten op string-concatenaties

apa · 25 apr 2009

Moto zei:
Where
(t.Geslacht = @geslacht OR @geslacht IS NULL) AND
(t.MaxLengte = @maxLengte OR @maxLengte IS NULL) AND
....

Kan je ook korter schrijven als volgt:

Code:

Where
t.Geslacht = ISNULL(@geslacht, t.Geslacht)
AND t.MaxLengte = ISNULL(@maxLengte, t.MaxLengte) AND ...

Voordeel van deze methode (en ook Moto's) boven het dynamisch bouwen van je SQL statement is dat de execution plan voor deze query gecached wordt op de server (waardoor die beduidend sneller uitgevoerd zal worden vanaf de tweede maal).

Archief - SQL parameter negeren

Piecemaker

Legacy Member

passero

Legacy Member

Piecemaker

Legacy Member

Parnakra

Legacy Member

passero

Legacy Member

enticore

Legacy Member

Vulcanor

Legacy Member

Piecemaker

Legacy Member

Adelbert

Legacy Member

Moto

Legacy Member

apa

Legacy Member