How to add parameters to SqlCommand?

Question

I'm trying to add parameters to SqlCommand, but it inserts parameter names instead of parameter values.

This is the code snippet:

var QueryString1 = "Insert into UsersTable (Username, Password, IsAdmin, Email, Budget, Phone) " +
                   "values ('@Un', '@P','" + user.IsAdmin + "', '@E', '@B', '@Ph')";

using (SqlCommand command = new SqlCommand(QueryString1, con))
{
    command.Parameters.Add("@Un", SqlDbType.Text);
    command.Parameters["@Un"].Value = user.UserName;
    command.Parameters.Add("@P", SqlDbType.Text);
    command.Parameters["@P"].Value = user.Password;
    command.Parameters.Add("@E", SqlDbType.Text);
    command.Parameters["@E"].Value = user.Email;
    command.Parameters.Add("@B", SqlDbType.Text);
    command.Parameters["@B"].Value = user.Budget.Amount + "-" + user.Budget.Currency;
    command.Parameters.Add("@Ph", SqlDbType.VarChar);
    command.Parameters["@Ph"].Value = user.Phone;

    if (command.ExecuteNonQuery().Equals(0))
    {
        con.Close();
        return InternalServerError();
    }

    con.Close();

    return Ok();
}

sorry for blurred records :)

This successful records were added when i was not using this sql injection prevention :D — Alvan Rahimli
– Alvan Rahimli, Commented Jan 10, 2019 at 19:23
could you please read up on the additional security salted and hashed passwords provide? thank you. — Hogan
– Hogan, Commented Jan 10, 2019 at 19:37
The user.IsAdmin is still being inlined instead of sent as a parameter. — Alejandro
– Alejandro, Commented Jan 10, 2019 at 20:05
Also, the TEXT data type is obsolete and should be replaced by VARCHAR(MAX) and on your last parameter, always specify the length of varchar parameters. — Alejandro
– Alejandro, Commented Jan 10, 2019 at 20:07
Oh, these are very helpful comments! Thank you all, u mentioned, i'm new with this. And this salted and hashed passwords is kind of hard now, but I will read about those. And the reason is (of that I insert IsAdmin inline) It is false default. Bcoz, I will chose Admins manually. Or users will gain it when i add reputation. Again, thank you very much <3 — Alvan Rahimli
– Alvan Rahimli, Commented Jan 10, 2019 at 21:05

Menahem · Accepted Answer · 2019-01-10 19:24:30Z

8

You are wrapping the variable names with single quotes, instead of wrapping the string values

answered Jan 10, 2019 at 19:24

Menahem

4,1742 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel A. White Over a year ago

there's no need to quote them in the first place.

Alvan Rahimli Over a year ago

Oh, thank you, soo much. U know, i'm new with this wonderful things:) <3 Ty, man :)

Alvan Rahimli Over a year ago

Oh, i thought multiple answers are acceptable, but i was wrong :D That's why your answer was not checked. <3 I'm so sorry about that. :)

gouravm · Accepted Answer · 2022-12-01 14:52:27Z

2

This will give you an idea of inserting data of student name and student Id in student table

using (SqlCommand command = new SqlCommand()){
      foreach (var data in result.Values)
           {
             command.Connection = conn;
             command.CommandType = CommandType.Text;
             command.CommandText = "INSERT INTO studentTable (studentId,studentName) VALUES (@studentId,@studentName)";

            // Add with value parameterName and its value
             command.Parameters.AddWithValue("@studentId", data.studentId);
             command.Parameters.AddWithValue("@studentName", data.studentName);

             command.ExecuteNonQuery();
          }
}

Hope this helps someone.

answered Dec 1, 2022 at 14:52

gouravm

3191 silver badge14 bronze badges

Collectives™ on Stack Overflow

How to add parameters to SqlCommand?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related