Monday, March 19, 2012

[SQLDatasource]Dynamic selection of column

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.
I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.

If you see I have put in the querystring as columnname @.sescolumn which I have initialised as asp:sessionparameter.
But it gives no results. When I put @.sescolumn between [] like normal columnnames are it doesn't work also.

Can someon put my on the right path?

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring%>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @.sescolumn like @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where ((@.sescolumn='AutoID' AND [AutoID] LIKE @.seskeyword) OR (@.sescolumn='Merk' AND [Merk] LIKE @.seskeyword) OR (@.sescolumn='Kleur' AND [Kleur] LIKE @.seskeyword) OR (@.sescolumn='Type' AND [Type] LIKE @.seskeyword) OR (@.sescolumn='Autotype' AND [Autotype] LIKE @.seskeyword) OR (@.sescolumn='prijs' AND [prijs] LIKE @.seskeyword) OR (@.sescolumn='Zitplaatsen' AND [Zitplaatsen] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding1' AND [Afbeelding1] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding2' AND [Afbeelding2] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding3' AND [Afbeelding3] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding4' AND [Afbeelding4] LIKE @.seskeyword)) and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

Or...

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where CASE @.sesscolumn WHEN 'AutoID' THEN [AutoID] WHEN 'Merk' THEN [Merk] WHEN 'Kleur' THEN [Kleur] WHEN 'Type' THEN [Type] WHEN 'Autotype' THEN [Autotype] WHEN 'prijs' THEN [prijs] WHEN 'Zitplaatsen' THEN [Zitplaatsen] WHEN 'Afbeelding1' THEN [Afbeelding1] WHEN 'Afbeelding2' THEN [Afbeelding2] WHEN 'Afbeelding3' THEN [Afbeelding3] WHEN 'Afbeelding4' THEN [Afbeelding4] END LIKE @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

|||

Damn didn't knew that this was possible. Thanks a lot mate.

Now I have just to convert some columns in where clause to type of string because my session is a string.
Going to find this out

Thanks again

No comments:

Post a Comment