CODEWITHSUNDEEP
c#sqlparameters
Hancs
Hancs

Reputation: 323

A SqlParameter with ParameterName '@0' is not contained by this SqlParameterCollection

See code snippet, I can't seem to find my error, any help would be appreciated. The exception is on the first parameter add, (@0).

Error: An SqlParameter with ParameterName '@0' is not contained by this SqlParameterCollection

Code:

using (SqlConnection conn = new SqlConnection(sqlConString))
{
    SqlCommand insertQuery = new SqlCommand("INSERT INTO user_table (Username, Password, Name, Surname, DOB) VALUES ( @0, @1, @2, @3, @4 )", conn);

    insertQuery.Parameters["@0"].Value = edtUsername.Text;
    insertQuery.Parameters.AddWithValue("@1", edtPassword.Text);
    insertQuery.Parameters.AddWithValue("@2", edtName.Text);
    insertQuery.Parameters.AddWithValue("@3", edtSurname.Text);
    insertQuery.Parameters.AddWithValue("@4", dateTimePicker.Text);

    conn.Open();
    insertQuery.ExecuteNonQuery();
    conn.Close();
} // using conn

Upvotes: 0

Views: 2534

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

There are a few problems with this code:

  1. You are not adding the parameter @0, you try to set its value before you added it.
  2. Don't use AddWithValue. Use Add instead.
  3. Don't use @0, @1, etc as parameter names, instead use meaningful names. It's not that important when there are only 5 parameters but using meaningful names everywhere is a good habit.
  4. Don't pass dates as strings, pass them as instances of DateTime. This will save you a lot of trouble messing around with formats.
  5. Don't store plain text password in the database. Use a hashing algorithm with salt.
  6. SqlCommand is an IDisposable too.

A better code would be something like this:

using (var conn = new SqlConnection(sqlConString))
{
    using(var insertQuery = new SqlCommand("INSERT INTO user_table (Username, Password, Name, Surname, DOB) VALUES ( @Username, @Password, @Name, @Surname, @DOB)", conn))
    {
        // I'm assuming nvarchar as the data type, change it if needed....
        insertQuery.Parameters.Add("@Username", SqlDbType.NVarChar).Value = edtUsername.Text;
        // uniqueSalt can be stored as plain text in the database, but should be unique for each password.
        insertQuery.Parameters.Add("@Password", SqlDbType.NVarChar).Value = HashPassword(edtPassword.Text, uniqueSalt); 
        insertQuery.Parameters.Add("@Name", SqlDbType.NVarChar).Value = edtName.Text;
        insertQuery.Parameters.Add("@Surname", SqlDbType.NVarChar).Value = edtSurname.Text;
        insertQuery.Parameters.Add("@DOB", SqlDbType.DateTime).Value = dateTimePicker.Value;
        conn.Open();
        insertQuery.ExecuteNonQuery();
    }
} 

string HashPassword(string password, string salt)
{
    // TODO: Implement hashing with salt
}

Upvotes: 4

Related Questions