Creating SelectCommand at page load time

[James R. Davis]

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

 

[Milosz Skalecki [MCAD]]

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

 

[James R. Davis]

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,

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
--
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
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

 

[Milosz Skalecki [MCAD]]

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!
--
Milosz

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,

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
--
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
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

 

[James R. Davis]

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

Jim