How can I set the sqldatasource parameter’s value?

I’m trying to set the value of the sqldatasource‘s selectcommand parameter @ClientID as in the code below, but it’s not working out.

My code:

Dim strCommand = "SELECT caller_id, phone, name, email FROM callers WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ea8986838f849eb5838ed7aaa986838f849ea3ae">[email protected]</a>"

SqlDataSource2.SelectCommand = strCommand

SqlDataSource2.SelectParameters.Add("@ClientID", iClientID)

What am I doing wrong?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The trick to make it work is to remove the paremeter you are trying to use before adding it. The following adapted version of your code should work:

' NOTE that there is no "@" sign when you use your parameters in the code
Parameter p = strCommandSqlDataSource2.SelectParameters["ClientID"]
strCommandSqlDataSource2.SelectParameters.Remove(p)
strCommandSqlDataSource2.SelectParameters.Add("ClientID", iClientID)

You should not use “@” sign when naming parameters in the code portion of its usage. You should use it only in the SQLCOMMAND string.

Hope it helps.

Method 2

You can set your parameter’s value like that :

SqlParameter parameter1 = new SqlParameter("@ClientID", SqlDbType.BigInt);
parameter1.Value = 32;
SqlDataSource2.SelectParameters.Add(parameter1);

Method 3

Never mind…configured the datasource’s parameter to take the value of another control..

Method 4

If you’ve used the WYSWIG editor to create your data source and you want to update the SQL parameters programmatically, then you need to do the following:

Dim strCommand = "SELECT caller_id, phone, name, email FROM callers WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="53303f3a363d270c3a376e13103f3a363d271a17">[email protected]</a>"

SqlDataSource2.SelectCommand = strCommand

**SqlDataSource2.SelectParameters.Clear();**

SqlDataSource2.SelectParameters.Add("@ClientID", iClientID)

Method 5

I have solution for variable from GET to parameter for SelectCommand

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
   Dim p As Parameter = SQLDataSource.SelectParameters("Order_id")
   If IsNothing(p) Then
        SQLDataSource.SelectParameters.Add("Order_id", Server.HtmlEncode(Request.QueryString("Order_id")).ToString())
   End If
End Sub

Method 6

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        SqlDataSource SqlDataSource1 = new SqlDataSource();
        SqlDataSource1.ID = "SqlDataSource1";
        this.Page.Controls.Add(SqlDataSource1);
        SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConID"].ConnectionString;
        SqlDataSource1.SelectCommand = "SELECT caller_id, phone, name, email FROM callers WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bbd8d7d2ded5cfe4d2df86fbf8d7d2ded5cff2ff">[email protected]</a>";
        SqlDataSource1.SelectParameters.Add("ClientID",ClientID);
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
    }
}

Method 7

You can workaround it by the Selecting event on the SqlDataSource, i now how frustraiting is to be restricted in this kind of controls !!!

Another alternative would be to add a HiddenField to your form, and the SqlDataSource could take its value from there.

Method 8

Here’s the VB version:

Dim parameter As New System.Web.UI.WebControls.Parameter("ClientID", Data.DbType.Int32)
parameter.DefaultValue = 45
sqlTicketInfo.SelectParameters.Add(parameter)

With the VB.NET version, there wasn’t a way to actually set the value, so I set the default value instead. The default value gets used if the value isn’t initialized, so since we can’t set the value, it’ll automatically use the default value anyways.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0