programatically change sqldatasource select statement

Hi Everyone,

I am trying to change the select statement of an sqldatasource if a check box is checked.

I am using theSqlDataSourceSelectingEventArgs but i can't get it to work, anyone got any pointers?

Code Behind

ProtectedSub LocMan_Searching(ByVal senderAsObject,ByVal eAs SqlDataSourceSelectingEventArgs)Handles LocManSearch.Selecting

If cb_Today.Checked =TrueThen

LocManSearch.SelectCommand ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &"+ '%') AND [available] LIKE '%' + " &Date.Today &"+ '%')"

Else : LocManSearch.SelectCommand ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &" + '%')"

EndIf

EndSub

My SQLDATSOURCE

<asp:SqlDataSourceID="LocManSearch"runat="server"ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>">

<SelectParameters>

<asp:ControlParameterControlID="dd_Area"Name="area"PropertyName="SelectedValue"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

Thanks in advance

Chris

I have always just used a string for my SQL statement assigned to a variable and just changed what the variable is assigned to, such as:

If checkbox.checked = true

SQLstr = "Select *..."

Else

SQLstr = "Select Column1..."

End if

|||

Thanks for the reply,

How then do i pass the string to my sqldatasource as the select command?

Chris

|||

Hi Chris,

Thanks again for the reply. I have made the change you suggested and moved the sub to the page load event handler which made it work. Problem is i get SQL errors.. Can anyone see where my select might be wrong?

Cheers

Chris

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim SQLstrAsString

If cb_Today.Checked =TrueThen

SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &"+ '%') AND [available] LIKE '%' + " &Date.Today &"+ '%')"

Else : SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &" + '%')"

EndIf

LocManSearch.SelectCommand = SQLstr

EndSub

|||

Besides the fact that you suffer from possibly getting SQL Injection attacks because you are using sql string concatenation instead of either parameterized queries or encoded strings, here is your problem:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim SQLstrAsString

If cb_Today.Checked =TrueThen

SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() &"%') AND [available] LIKE '%" &Date.Today &"%')"

Else : SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() &"%')"

EndIf