programatically change sqldatasource select statement RSS

• • Reply
  

  cjgates

  Member

  6 Points

  63 Posts

  programatically change sqldatasource select statement

  Oct 28, 2007 11:50 PM|LINK

  Hi Everyone,

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

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

  Code Behind

  Protected Sub LocMan_Searching(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs) Handles LocManSearch.Selecting

  If cb_Today.Checked = True Then

  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() & " + '%')"

  End If

  End Sub

  My SQLDATSOURCE

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

  <SelectParameters>

  <asp:ControlParameter ControlID="dd_Area" Name="area" PropertyName="SelectedValue"

  Type="String" />

  </SelectParameters>

  </asp:SqlDataSource>

   

  Thanks in advance

  Chris

  Thanks for your help.
  
  Chris
  
  We all need help from time to time....
  
  Fly-Catcher
• • Reply
  

  gww

  Contributor

  2143 Points

  458 Posts

  Re: programatically change sqldatasource select statement

  Oct 29, 2007 12:17 AM|LINK

  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

  • Marked as answer by Bo Chen – MSFT on Oct 31, 2007 02:58 AM
• • Reply
  

  cjgates

  Member

  6 Points

  63 Posts

  Re: programatically change sqldatasource select statement

  Oct 29, 2007 02:37 PM|LINK

  Thanks for the reply,

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

  
  Chris

  Thanks for your help.
  
  Chris
  
  We all need help from time to time....
  
  Fly-Catcher
• • Reply
  

  cjgates

  Member

  6 Points

  63 Posts

  Re: programatically change sqldatasource select statement

  Oct 29, 2007 04:17 PM|LINK

  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

   

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  Dim SQLstr As String

  If cb_Today.Checked = True Then

  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() & " + '%')"

  End If

  LocManSearch.SelectCommand = SQLstr

  End Sub

  Thanks for your help.
  
  Chris
  
  We all need help from time to time....
  
  Fly-Catcher
• • Reply
  

  Motley

  Star

  13789 Points

  2449 Posts

  MVP

  Re: programatically change sqldatasource select statement

  Oct 29, 2007 05:29 PM|LINK

  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:

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  Dim SQLstr As String

  If cb_Today.Checked = True Then

  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() & "%')"

  End If

  • Marked as answer by Bo Chen – MSFT on Oct 31, 2007 02:58 AM
• • Reply
  

  radfo

  Member

  8 Points

  7 Posts

  Re: programatically change sqldatasource select statement

  Aug 15, 2008 04:33 PM|LINK

   Hi Motley,

   I'm new to the ASP.net scene so if possible I would like know more about the injection statement you made. I think I'm using the parameterized queries as you said, but I would like to make sure. When building my query I use @variable in my query as opposed to putting the variable directly in my query as shown in the previous post. Is that what I'm supposed to do or is there something different?

   selectparameter.addwithvalue('variableName',selectedvalue.tostring)

   

   Any help would be greatly appreciated.
  

• • Reply
  

  Motley

  Star

  13789 Points

  2449 Posts

  MVP

  Re: programatically change sqldatasource select statement

  Aug 15, 2008 07:19 PM|LINK

  Yes, that is exactly what you should do.