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?

Answer 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.

Answer 2

You can set your parameter's value like that :

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

Answer 3

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

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

SqlDataSource2.SelectCommand = strCommand

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

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