Must declare the scalar variable “@Login”. Error w

Question

I have this error : Must declare the scalar variable "@Login".

My code :

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["intranetv2"].ConnectionString))
{

    SqlCommand cmd = new SqlCommand("insert into [MyBase].[Dbo].[LogErrors] (Username, StackTrace, ShortDescription, DetailDescription, ErrorType) VALUES (@Login, @Stack, @Message, @Txt, @Source)", conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "Login";
                param.Value = user.Login;
                param.ParameterName = "Stack";
                param.Value = ex.StackTrace;
                param.ParameterName = "Message";
                param.Value = ex.Message;
                param.ParameterName = "Txt";
                param.Value = Txt;
                param.ParameterName = "Source";
                param.Value = ex.Source;
                // 3. add new parameter to command object
                cmd.Parameters.Add(param);
                conn.Open();
                return cmd.ExecuteNonQuery();
}

I have try with th e'@' in the param but i have the same error.

Answer 1

The problem is that you're constantly changing the value of 'param', then only adding the very last version.

Try the following instead:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["intranetv2"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand("insert into [MyBase].[Dbo].[LogErrors] (Username, StackTrace, ShortDescription, DetailDescription, ErrorType) VALUES (@Login, @Stack, @Message, @Txt, @Source)", conn);

    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Login", Value = user.Login });
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Stack", Value = ex.StackTrace });
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Message", Value = ex.Message});
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Txt", Value = Txt });
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Source", Value = ex.Source});

    conn.Open();
    return cmd.ExecuteNonQuery();
}

Answer 2

The way you are going about it, only the last parameter is getting added. There are several ways of adding parameters to the command object and the one below is among the easiest:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["intranetv2"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand("insert into [MyBase].[Dbo].[LogErrors] (Username, StackTrace, ShortDescription, DetailDescription, ErrorType) VALUES (@Login, @Stack, @Message, @Txt, @Source)", conn);

    conn.Open();

    cmd.Parameters.AddWithValue("@Login", user.Login);
    cmd.Parameters.AddWithValue("@Stack", ex.StackTrace);
    cmd.Parameters.AddWithValue("@Message", ex.Message);
    cmd.Parameters.AddWithValue("@Txt", Txt);
    cmd.Parameters.AddWithValue("@Source", ex.Source);

    return cmd.ExecuteNonQuery();
}

Answer 3

There is a small mistake. Here is rectified code

using (SqlConnection conn = new  SqlConnection(ConfigurationManager.ConnectionStrings["intranetv2"].ConnectionString))
{

    SqlCommand cmd = new SqlCommand("insert into [MyBase].[Dbo].[LogErrors] (Username, StackTrace, ShortDescription, DetailDescription, ErrorType) VALUES (@Login, @Stack, @Message, @Txt, @Source)", conn);

            SqlParameter param = new SqlParameter();
            param.ParameterName = "@Login";
            param.Value = user.Login;
            cmd.Parameters.Add(param);

            SqlParameter param2 = new SqlParameter();
            param2 .ParameterName = "@Stack";
            param2 .Value = ex.StackTrace;
            cmd.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter();
            param3.ParameterName = "@Message";
            param3.Value = ex.Message;
            cmd.Parameters.Add(param3);

            SqlParameter param4 = new SqlParameter();
            param4.ParameterName = "@Txt";
            param4.Value = Txt;
            cmd.Parameters.Add(param4);

            SqlParameter param5 = new SqlParameter();
            param5.ParameterName = "@Source";
            param5.Value = ex.Source;
            cmd.Parameters.Add(param5);

            conn.Open();
            return cmd.ExecuteNonQuery();

}

Thanks

Answer 4

You must create a new SqlParameter for each Parameter, it should be like this:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["intranetv2"].ConnectionString))
{

    SqlCommand cmd = new SqlCommand("insert into [MyBase].[Dbo].[LogErrors] (Username, StackTrace, ShortDescription, DetailDescription, ErrorType) VALUES (@Login, @Stack, @Message, @Txt, @Source)", conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@Login";
                param.Value = user.Login;
                cmd.Parameters.Add(param);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@Stack";
                param2.Value = ex.StackTrace;
                cmd.Parameters.Add(param2);
    (...)

Answer 5

You override params value and only ever add one parameter to the query. Which is Source

I'd recommend you create a new variable or use AddWithValue.

   SqlParameter param = new SqlParameter();
   param.ParameterName = "Login";
   param.Value = user.Login;

   cmd.Parameters.Add(param);

   SqlParameter param2 = new SqlParameter();
   param2.ParameterName = "Stack";
   param2.Value = ex.StackTrace;

   cmd.Parameters.Add(param2);

etc.

To use AddWithValue you can do:

cmd.Parameters.AddWithValue("@Login", user.Login);
cmd.Parameters.AddWithValue("@Stack", ex.StackTrace);

Answer 6

You only added the last parameter to the parameter list.

Better would be this:

SqlParameter param = new SqlParameter();
param.ParameterName = "@Login";
param.Value = user.Login;
cmd.Parameters.Add(param);

param = new SqlParameter();
param.ParameterName = "@Stack";
param.Value = ex.StackTrace;
cmd.Parameters.Add(param);

param = new SqlParameter();
param.ParameterName = "@Message";
param.Value = ex.Message;
cmd.Parameters.Add(param);

param = new SqlParameter();
param.ParameterName = "@Txt";
param.Value = Txt;
cmd.Parameters.Add(param);

param = new SqlParameter();
param.ParameterName = "@Source";
param.Value = ex.Source;
cmd.Parameters.Add(param);

conn.Open();
return cmd.ExecuteNonQuery();