CODEWITHSUNDEEP
asp.netsqldatasource
thegunner
thegunner

Reputation: 7163

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

SqlDataSource2.SelectCommand = strCommand

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

What am I doing wrong?

Upvotes: 8

Views: 52568

Answers (8)

MaJiD
MaJiD

Reputation: 11

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();
    }
}

Upvotes: 1

Fanda Kučera
Fanda Kučera

Reputation: 11

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

Upvotes: 1

StudioWoofa
StudioWoofa

Reputation: 11

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)

Upvotes: 1

Anton
Anton

Reputation: 1437

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.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

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.

Upvotes: 9

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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.

Upvotes: 0

thegunner
thegunner

Reputation: 7163

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

Upvotes: 2

Canavar
Canavar

Reputation: 48108

You can set your parameter's value like that : 

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

Upvotes: 3

Related Questions