locked
Datasource.SelectParameters.Add() not working RRS feed

  • 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;
            }

    Tuesday, February 11, 2020 2:43 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2020 9:54 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2020 9:54 AM
  • 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>

    Tuesday, February 11, 2020 7:29 PM
  • 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

    Tuesday, February 11, 2020 8:09 PM