• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1151
  • Last Modified:

Dynamically change a datasource parameter

I have a default select parameter for a datasource in the aspx file.

I need to change the select parameter value in the aspx.cs file.
<asp:SqlDataSource ID="dscExceptionDetail" runat="server" 
         ConnectionString="<%$ ConnectionStrings:StockSelectSQLConnectionString %>" 
         SelectCommand="usp_getPriceChart" 
         SelectCommandType="StoredProcedure"
         DataSourceMode="DataSet">
         <SelectParameters>
            <asp:ControlParameter ControlID="calFromDate" Name="FromDate" 
            propertyname="SelectedDate" Type = "DateTime" DefaultValue="2009-01-05" />
            <asp:ControlParameter ControlID="calToDate" Name="ToDate" 
            propertyname="SelectedDate" Type = "DateTime" DefaultValue="2009-02-05" />
            <asp:Parameter Name="SymbolID" Type = "int16" DefaultValue="1811" />
         </SelectParameters>
    </asp:SqlDataSource>
 
aspx.cs
 
int intSymbolID = int.Parse(grdExceptions.SelectedRow.Cells[1].Text);
 
//This parameter has already been defined in the aspx file for the dscExceptionDetail datasource..
 
<asp:Parameter Name="SymbolID" Type = "int16" DefaultValue="1811" />
 
How do I change it here without clearing the other 3 parameters?
 
//Change the SymbolID selectparameter value of the the dscExceptionDetail from the "1811" 
default value to the  intSymbolID value
 
????

Open in new window

0
Dovberman
Asked:
Dovberman
  • 4
  • 2
1 Solution
 
jabcocoCommented:
Simply by overriding the Selecting event from your SQL DataSource

Let me know
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" OnSelecting="SqlDataSource1_Selecting" ></asp:SqlDataSource>
 
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["FromDate"] = new DateTime();
        //or
        e.Command.Parameters[0] = new DateTime();
    }

Open in new window

0
 
DovbermanAuthor Commented:
this is not working.


I am selecting a row from the DataGrid.
The Datasource DataBind refreshes based on new parmeter values.
The chart which is based on the datasource should change, but it does not.
I need the syntax that refreshes the chart.

Thanks,
0
 
DovbermanAuthor Commented:
The code below works, but only if I set the Command type to Text instead of StoredProcedure.
I really need a way to reset the Stored Prodedure parameters to improve performance.
// Reset ExceptionDetail (Does not work)
           
            //cmd.Parameters.Clear();
            //cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = strFromDate;
            //cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = strToDate;
            //cmd.Parameters.Add("@SymbolID", SqlDbType.Int).Value = grdExceptions.SelectedValue.ToString();
 
            // Reset ExceptionDetail (Works)
 
            int intSymbolID = int.Parse(grdExceptions.SelectedValue.ToString());
            string strSQL = "SELECT QuoteDate, ClosePrice FROM StockHist ";
            strSQL += "WHERE SymbolID=" + intSymbolID + "AND QuoteDate >= '" + strFromDate + "' ";
            strSQL += "AND QuoteDate <= '" + strToDate + "' ";
            strSQL += "ORDER BY QuoteDate ";
 
            dscExceptionDetail.SelectCommand = strSQL;
       
            // This refreshes the chart
            dscExceptionDetail.DataBind();

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jabcocoCommented:
There is what you need to do is:

1) Add the "SelectedIndexChanged" even on your GridView
2) Add the "Selecting" on the SQLDataSource
3) In the "SelectedIndexChanged" simply call "dscExceptionDetail.DataBind()";
4) Then in the Selecting event change your parameters like i show.

Let me know.
0
 
DovbermanAuthor Commented:
I will try your suggestions in the morning.
It is Mardis Gras season here in Biloxi.

Thanks,
0
 
DovbermanAuthor Commented:
Thank you. I will investigate later.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now