SQLDatasource not using SelectCommand from _Selecting Event.

[John Kotuby]

Hello again...

I am cross-posting to the ADO newgroup because nobody in the ASPNET group
had a comment. Note, since my first post I have tried using MS Update to
install VS2005 SP1 but the automatic download and install failed.

I have tried using the SQLDatsource control as part of a user control that
just contains a Repeater and the SQLDatasource control which is designated
as the Datsource for the Repeater. I set it up just like in the
documentation.

--------------------------- code ----------------------------------------

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStringsCConnectionString %>"

DataSourceMode="DataSet" SelectCommand="Select * from user_searchCriteria "

</asp:SqlDataSource>

--------------------------- code ----------------------------------------

Because I need a dynamic query determined at runtime I tried setting the
SelectCommand in the SqlDataSource1_Selecting event, which is SUPPOSED to
fire before the query is sent to the server. The code is below. Note that I
output the new contents of the SelectCommand in Response.Write. That
response line shows up, with correct syntax (I copied and pasted into QA and
it ran fine), at the very top of the page, yet the SQLDatasource1 insists on
using the original SelectCommand in the declaration.

What am I doing wrong? Or is it VS or maybe incorrect documentation?

From the online docs...
===============================
SqlDataSource.Selecting Event -- Occurs before a data retrieval operation.
Handle the Selecting event to perform additional initialization operations
that are specific to your application, to validate the values of parameters,
or to change the parameter values before the SqlDataSource control performs
the select operation.
===============================
Thanks to all....

--------------------------- code ----------------------------------------

'This is run Before the Select command is sent to the Server

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles
SqlDataSource1.Selecting

_selectCMD = " Select
SearchTitle,SearchDescription,sequence,dateUpdate,keywords " & _

" from user_searchCriteria where userid='" & Me.Session.Item("uid") & "'" &
_

" order by SearchTitle"

Me.SqlDataSource1.SelectCommand = _selectCMD

Response.Write(SqlDataSource1.SelectCommand)

End Sub

--------------------------- code ----------------------------------------

 

[John Kotuby]

Hi all..

Well the SP1 for VS2005 didn't fix the problem I was having so I took a
different approach. Instead of assigining the DataSource of the Repeater
during the declaration of the control I left it unassigned. Fot the
SQLDataSource control I left the SelectCommand unassigned. Instead, in the
associated source VB code-behind I did all that in the Page_Init event,
adding a DataBind() call, as follows:

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Init

_userID = Me.Session.Item("uid")

_selectCMD = " Select
SearchTitle,SearchDescription,sequence,dateUpdate,keywords " & _
" from user_searchCriteria where userid='" & Me.Session.Item("uid") &
"'" & _
" order by SearchTitle"

Me.SqlDataSource1.SelectCommand = _selectCMD

Me.Repeater1.DataSource = Me.SqlDataSource1
Me.Repeater1.DataBind()

End Sub

It appears to be working now....
Lesson learned.