Creating SelectCommand at page load time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James R. Davis - 01 Dec 2007 00:03 GMT

I have a need to change the select command based on user input (dropdown
lists 1 and 2). I'm using a GridView command that is tied to a
SqlDataSource command as follows:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MTT.mdfConnectionString %>" SelectCommand = "<%#
SQLWhereClause %>" >

</asp:SqlDataSource>

<asp:GridView ID="GridView1" DataSourceID="SqlDataSource1" runat="server"
CaptionAlign="Top" EnableSortingAndPagingCallbacks="True" Width="100%"
AutoGenerateColumns = "False" DataKeyNames="TipNumber">

<Columns>

<asp:HyperLinkField DataNavigateUrlFields="TipNumber"
DataNavigateUrlFormatString="Tip.aspx?Num={0}" DataTextField="TipNumber"
HeaderText="No." />

<asp:BoundField ReadOnly="True" HeaderText="Tip Name" InsertVisible="False"
DataField="TipName" SortExpression="TipName"></asp:BoundField>

<asp:BoundField ReadOnly="True" HeaderText="Teaser" InsertVisible="False"
DataField="TipSuffix" SortExpression="TipSuffix"></asp:BoundField>

</Columns>

</asp:GridView>

What I am attempting to do is create the SelectCommand via a Page_Load sub
as follows:

Protected Sub Page_load()

Dim SQLWhereClause As String = ""

SQLWhereClause = "SELECT * FROM [forum_tips] WHERE "

etc.

I build the String and end the Sub. I have verified that the value of
SQLWhereClause is what I want it to be but the GridView renders nothing as a
result (just as if it didn't have a select command.)

What am I doing wrong, please?1

Reply to this Message

Milosz Skalecki [MCAD] - 01 Dec 2007 11:21 GMT

Hi James,

In order to make it work you have to handle SqlDataSource.Selecting event:

<asp:SqlDataSource runat="server" ID="ds" SelectCommand="whatever"
OnSelecting="ds_Selecting"></asp:sqlDataSource>

protected void ds_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.CommandText = SQLWhereClause;
}

You should be fine from this point.

Regards

Signature

Milosz

> I have a need to change the select command based on user input (dropdown
> lists 1 and 2). I'm using a GridView command that is tied to a
[quoted text clipped - 42 lines]
>
> What am I doing wrong, please?1

Reply to this Message

James R. Davis - 01 Dec 2007 16:44 GMT

Thank you, sir.

It seems that some of you have the huge number of 'factoids' needed to be
proficient at ASP.NET at your finger tips. I wonder how long it takes to
get to the point that virtually any new coding problem becomes simply a
matter of knowing where to look for a brief refresher of the details?

Entry level is really intimidating to me and I've been coding for decades.

> Hi James,
>
[quoted text clipped - 58 lines]
> >
> > What am I doing wrong, please?1

Reply to this Message

Milosz Skalecki [MCAD] - 01 Dec 2007 23:31 GMT

Howdy James,

It's just a coincidence as I have solved similar problem recently :) VS
debugger and Reflector helped to realise SelectCommand is not stored in the
viewstate and it's set before Selecting event, so the only place you can
amend its value is Command property. So as you see it's just a matter of
investigation :)

Take care!

Signature

Milosz

> Thank you, sir.
>
[quoted text clipped - 74 lines]
> > >
> > > What am I doing wrong, please?1

Reply to this Message

James R. Davis - 02 Dec 2007 00:58 GMT

Great help, sir. I used a slightly different approach but your advice led
the way.

Jim

> Howdy James,
>
[quoted text clipped - 5 lines]
>
> Take care!

Reply to this Message

Creating SelectCommand at page load time

Free Magazines