How can I set the sqldatasource parameter's value?

Question

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 client_id=@ClientID"

SqlDataSource2.SelectCommand = strCommand

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

What am I doing wrong?

score 9 · Answer 1 · answered Oct 10 '09 at 20:06

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.

score 3 · Answer 2 · answered Jun 12 '09 at 19:36

3

You can set your parameter's value like that :

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

answered Jun 12 '09 at 19:36

Canavar

47,715
17
91
122

This does not work in C# either. The two types of parameters cannot be converted automatically like you're doing in the example. – Brad Jul 26 '19 at 19:03

score 2 · Accepted Answer · answered Jun 12 '09 at 21:17

2

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

answered Jun 12 '09 at 21:17

thegunner

6,883
30
94
143

It's really a good solution. You can read the parameter's value from Session either. – Sali Hoo Mar 11 '12 at 11:28

score 1 · Answer 4 · answered Jul 19 '14 at 23:14

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 client_id=@ClientID"

SqlDataSource2.SelectCommand = strCommand

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

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

score 1 · Answer 5 · answered May 16 '15 at 20:44

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

score 1 · Answer 6 · edited Nov 20 '15 at 13:03

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 client_id=@ClientID";
        SqlDataSource1.SelectParameters.Add("ClientID",ClientID);
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
    }
}

score 0 · Answer 7 · answered Aug 20 '10 at 16:06

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.

score 0 · Answer 8 · answered Jun 12 '09 at 21:21

0

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.

answered Jun 12 '09 at 21:21

Jhonny D. Cano -Leftware-

17,663
14
81
103

How can I set the sqldatasource parameter's value?

My code:

8 Answers8

Linked