I am trying to change the parameter of an SqlDataSource on the fly, but the code seems to fail, or in other words I seem to fail.
Here is the code:
int id = 0;
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RHOGEConnection"].ConnectionString);
SqlCommand getId = new SqlCommand(null, conn);
SqlParameter name = new SqlParameter("@name", SqlDbType.NVarChar);
name.Value = SelectTeam.SelectedValue;
getId.Parameters.Add(name);
/* getId.CommandText = */ //changed to a select statement that gets a number
try
{
conn.Open();
id = (int)getId.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
SqlDataSource2.SelectParameters.Add(new Parameter("getId", TypeCode.Int16, Convert.ToString(id)));
Now I know that the code to get the number, the lines above SqlDataSource2, works because I printed it on the screen and they appear correct. I also have tried the SqlDataSource2 select command in SQL Management Studio and it produces the correct results. So my best guess is there is a problem in adding the parameter to the parameters list.
SELECT [TeamName] FROM [Teams] where PlayerIdent = (select playerId
from Players where PlayerShirtId = @getId)
The above is the select command that works.
Do you agree that the parameter thing is the correct diagnosis, and if so what I should do?
EDIT: I was able to solve this problem the following way.
SqlDataSource2.SelectParameters["getId"].DefaultValue = Convert.ToString(id);