Datasource.SelectParameters.Add() not working

Question

User-1215707945 posted

I have a datasource which calls a stored procedure and uses a select parameter.
When I use the <SelectParameters> and provide a hardcoded default value then everything is perfect... however I wish to pass this value programmatically.

I have added this to the code behind page but when I run the page I get the error that a default parameter was expected but not provided.
I have added a breakpoint to the code below, it is getting hit and it contains the value I expect so I can only assume it is running in the wrong part of the page lifecycle to take effect or I have done something else wrong?

protected void SQLgetCRMs_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            SQLgetCRMs.SelectParameters.Add("CRMtype", theParam);
            SQLgetCRMs.SelectParameters["CRMtype"].DefaultValue = theParam;
        }

Answer 1

User288213138 posted

Hi QPR_JAY,

SQLgetCRMs.SelectParameters["CRMtype"].DefaultValue = theParam;

If you want to set the value of the CRMtype at the code behind, please make sure you add a declarative parameter to the SelectParameters collection.

<asp:SqlDataSource ... >

   <SelectParameters>

      <asp:Parameter Name="CRMtype" />

   </SelectParameters>

</asp:SqlDataSource

If you still have question, please post your complete code.

Best regards,

Sam

Sam

Answer 2

User-1215707945 posted

Thanks that stopped the error but I'm not convinced the value is being passed as the datagrid is empty.
These 3 things convince me the code is sound...
When I debug I see that theParam holds the expected value.
When I config the datasource and test it I add the value that theParam holds and I get 4 records.
I write theParam out as a labels value and it is what I expect.
When I execute the Stored procedure passing the value I get the expected 4 records.

I know the code block is being hit SQLgetCRMs_Selecting() Could it be that this is the wrong event handler and at this point the gridview has already been "drawn"?

public partial class _default : System.Web.UI.Page
    {

        
        string theParam;
        protected void Page_Load(object sender, EventArgs e)
        {
            string theUser = Request.ServerVariables["LOGON_USER"].Replace(@"xxxx\", "");
            if (theUser == "xxxx")
            {
                theParam = "rdAlli";
                SQLgetCRMs.SelectParameters.Add("CRMtype", theParam);
            }
            else
            {
                theParam = "RdCont";
                SQLgetCRMs.SelectParameters.Add("CRMtype", theParam);
            }
            Label1.Text = theParam;
            
           
        }

        protected void SQLgetCRMs_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            SQLgetCRMs.SelectParameters.Add("CRMtype", theParam);
            SQLgetCRMs.SelectParameters["CRMtype"].DefaultValue = theParam;
        }
    }

<asp:SqlDataSource ID="SQLgetCRMs" runat="server" ConnectionString="<%$ ConnectionStrings:OnlineServicesConnectionString %>" SelectCommand='GetRemoteCRMrecords' SelectCommandType="StoredProcedure" OnSelecting="SQLgetCRMs_Selecting">
 <SelectParameters>
<asp:Parameter Name="CRMtype" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Answer 3

User-1215707945 posted

ok I got it working, I changed
SQLgetCRMs.SelectParameters.Add("CRMtype", Session["theParam"].ToString());
to
e.Command.Parameters[0].Value = Session["theParam"].ToString();

And it works as expected! Thanks for the heads up regarding the declarative parameter