[RESOLVED] Dynamically change an SQL statement

I'd like to have a standard sql select statement that depending on user input changes to alternative one.

I've written code similar to this:

if (something is true....)

{
SQLdataSRC1.SelectCommand = "Select *...  etc";

// where SQLdataSRC1 is the name of the sql select statement datasource in the front end code.
}

I've put this code in the onload event and in the SQL datasource event handler but still it gets ignored - and only the SQL select statement in the front end code is recognised.

What am I doing wrong?

Can you show how the two different options compare? And where exactly within the code-behind have you put your conditional code?

 

I think best place to alter the SELECT Command is the SQLDataSource Selecting event.

I seem to have fixed it.

I was adding the new select statement to the onSelecting event - and I discovered that if I add the code to the onLoad event that it seems to work. Take a look at the code I have pasted below.

--------------------------------------------------------------------------------------

protected void Page_Load(object sender, EventArgs e)
    {
        if
           (Request.QueryString["LinkID"] == "11")
        {
            AccessDataSource2.SelectCommand = "SELECT * FROM [BizSales] WHERE ([id] = 170)";
        }
        if
          (Request.QueryString["LinkID"] == "12")
        {
            AccessDataSource2.SelectCommand = "SELECT * FROM [BizSales] WHERE ([id] = 165)";
        }

    }

-----------------------------------------------------------------------------------------------

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
        DataKeyNames="id" DataSourceID="AccessDataSource2" Height="50px"
        onpageindexchanging="DetailsView1_PageIndexChanging" Width="125px">
        <Fields>
            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False"
                ReadOnly="True" SortExpression="id" />
            <asp:BoundField DataField="AdType" HeaderText="AdType"
                SortExpression="AdType" />
            <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"
                SortExpression="CompanyName" />
            <asp:BoundField DataField="BodyTxt" HeaderText="BodyTxt"
                SortExpression="BodyTxt" />
            <asp:BoundField DataField="WebLink" HeaderText="WebLink"
                SortExpression="WebLink" />
            <asp:BoundField DataField="Logo" HeaderText="Logo" SortExpression="Logo" />
            <asp:BoundField DataField="Ranking" HeaderText="Ranking"
                SortExpression="Ranking" />
            <asp:BoundField DataField="DateAdded" HeaderText="DateAdded"
                SortExpression="DateAdded" />
            <asp:BoundField DataField="RemovalDate" HeaderText="RemovalDate"
                SortExpression="RemovalDate" />
            <asp:BoundField DataField="StartDate" HeaderText="StartDate"
                SortExpression="StartDate" />
        </Fields>
    </asp:DetailsView>
    <asp:AccessDataSource ID="AccessDataSource2" runat="server"
        DataFile="~/App_Data/Biz.mdb" 
        SelectCommand="SELECT * FROM [BizSales] WHERE ([id] = 160)"
        onselecting="AccessDataSource2_Selecting1">
   </asp:AccessDataSource>

Save yourself a ton of grief in future and use parameters.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.querystringparameter.aspx

 

Thanks Mike, but could you think of a reason why it only works in the onload event and not in the selecting event?

The only reason I can think of is that the selecting event isn't being fired. And the only reason for that is if it isn't properly hooked up to the datasource control.

Put a breakpoint on the Selecting event in your code behind and see if execution halts when you press F5. If it does, hit F10 to step through the code line by line and see if anything obvious jumps out in your Locals window.

 

Thanks, I give it a go.

thoughts on "[RESOLVED] Dynamically change an SQL statement "

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>