"SqlParameterCollection accepts only nonzero objects of type SqlParameter, not String objects."

Question

I get an exception

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects 

by executing the following code:

 string StrQuery; using (SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SanFransiscoData;Integrated Security=True;Pooling=False")) { using (SqlCommand comm = new SqlCommand()) { comm.Connection = conn; conn.Open(); // SqlParameter author = new SqlParameter("@author", dataGridView1.Rows[0].Cells[0].Value.ToString()); comm.Parameters.Add("@author", SqlDbType.VarChar); comm.Parameters.Add("@title", SqlDbType.NVarChar); comm.Parameters.Add("@genre", SqlDbType.VarChar); comm.Parameters.Add("@price", SqlDbType.Float); comm.Parameters.Add("@publish_date", SqlDbType.Date); comm.Parameters.Add("@description", SqlDbType.NVarChar); comm.Parameters.Add("@bookid", SqlDbType.VarChar); for (int i = 0; i < dataGridView1.Rows.Count; i++) { StrQuery = "INSERT INTO BooksData VALUES(@author,@title,@genre,@price,@publish_date,@description,@bookid)"; comm.Parameters.Add(dataGridView1.Rows[i].Cells[0].Value.ToString()); comm.Parameters.Add(dataGridView1.Rows[i].Cells[1].Value.ToString()); comm.Parameters.Add(dataGridView1.Rows[i].Cells[2].Value.ToString()); comm.Parameters.Add(Convert.ToDecimal(dataGridView1.Rows[i].Cells[3].Value)); comm.Parameters.Add(Convert.ToDateTime(dataGridView1.Rows[i].Cells[4].Value)); comm.Parameters.Add(dataGridView1.Rows[i].Cells[5].Value.ToString()); comm.Parameters.Add(dataGridView1.Rows[i].Cells[6].Value.ToString()); comm.CommandText = StrQuery; comm.ExecuteNonQuery(); } } 

Please tell me where I am wrong.

Answer 1

When you use the Add method, you are trying to add a new parameter. What you want to do is assign a value. Therefore change this:

 comm.Parameters.Add(dataGridView1.Rows[i].Cells[0].Value.ToString()); 

:

 comm.Parameters["@author"].Value = dataGridView1.Rows[i].Cells[0].Value.ToString(); 

Similarly for other parameters.

Answer 2

I was getting the same error and should have used AddWithValue like this ...

 cmd.Parameters.AddWithValue(@columnToUpdate, newValue); cmd.Parameters.AddWithValue(@conditionalColumn, conditionalValue); 

Answer 3

I ran into the same problem, but mine was making object [2] = object [1] as SqlParameters, similar to what was tried.

Just to add to the stream, I have this simple array of SqlParameters objects added from a method like this,

  private SqlParameter GetGenericParamsObject(string name, object data) { return new SqlParameter(name, SetSqlDataType(data.GetType().ToString())) { Direction = Input, Value = data }; } 

If there is a simple switch for SetSqlDataType (), then SqlDbType.Int is one of the return types for setting it.

Then i ran

  private static void ExecuteSqlCommand(DbContext dbContext, string sql, params object[] sqlParameters) { try { if (dbContext.Database.Connection.State == ConnectionState.Closed) dbContext.Database.Connection.Open(); var cmd = dbContext.Database.Connection.CreateCommand(); cmd.CommandText = sql; foreach (var param in sqlParameters) cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e); throw; } } 

This helps with casting the appropriate data type and moves the parameter name, data type and object from the command method to simplify debugging, and then simply adds the entire SqlParameter to cmd.Parameters.

Answer 4

I encountered the same error when I used

 cmd.Parameters.Add(pars) 

where pars was an array of SqlParameter. The problem was that I used the Add function, but instead I had to use the AddRange.

 cmd.Parameters.AddRange(pars)