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

I keep getting the exception

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

while executing the following code:

```csharp
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'm going wrong.

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

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

to this:

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

Similarly for the other parameters.

I had been getting the same error and had to use `AddWithValue` like this...

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

I ran into the same issue, but mine was doing an object[2] = object[1] as SqlParameters, similar to what was being tried.

Just to add to the thread, I have this simple object array of SqlParameters 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 };
}

Where there is a simple switch for the SetSqlDataType(), i.e. SqlDbType.Int is one of the return types to set it.

Then I run

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 to the proper data type and moves the param name, datatype and object out of the command method for easier debugging and then just adding the entire SqlParameter to the cmd.Parameters.

For any future readers of this question - the examples deal with `.Add()`, but `.Remove()` will also throw this error if not used correctly. The proper way to use `.Add()`, as has been documented by others is :

cmd.Parameters.AddWithValue("Key", "Value");

The proper way to use `.Remove()` is:

cmd.Parameters.Remove(command.Parameters["Key"]);

I ran into the same error when I was using

cmd.Parameters.Add(pars)

where `pars` was a an array of `SqlParameter`. The issue was that I was using the `Add()` function but I should've used `AddRange()` instead.

cmd.Parameters.AddRange(pars)

Try below

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

I replaced my reference to System.Data.SqlClient with Microsoft.Data.SqlClient
corrected the using statements and my problems went away

In my .csproj I now have

<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="1.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.3" />
</ItemGroup>

But I also found I had use of the following scenario where I created a System.Data.SqlClient.SqlParameter


public static List<T> RunQuery<T>(ApiDbContext context, string query, Func<DbDataReader, T> map, params SqlParameter[] parameters)
{
var cn = context.Database.GetDbConnection();
var oldState = cn.State;
if (cn.State.Equals(ConnectionState.Closed)) { cn.Open(); }

using (var command = cn.CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
foreach (var param in parameters)
{
var p = new System.Data.SqlClient.SqlParameter
{
ParameterName = param.ParameterName, Value = param.Value, SqlDbType = param.SqlDbType
};
command.Parameters.Add(p);
}
if (cn.State.Equals(ConnectionState.Closed)) { cn.Open(); }
var entities = new List<T>();
using (var result = command.ExecuteReader())
{
while (result.Read())
{
entities.Add(map(result));
}
}

if (oldState.Equals(ConnectionState.Closed) && cn.State == ConnectionState.Open) { cn.Close(); }
return entities;
}
}

The following simple solution works for me:

SqlCommand command = ...
command.Parameters.Add(parameterPlaceholder, SqlDbType.Char);
command.Parameters[parameterPlaceholder].Value = (object)parameterValue ?? DBNull.Value;

Replacing a null value with "DBNull.Value" is the key. Don't forget to adapt the SqlDbType to your needs, in the example above.

I also got this error using System.Data.Entity.Database

[![sqlquery][1]][1]

In that case with EF 6.4 I needed to use something like

var parameters = new List<SqlParameter> parameters // using System.Data.SqlClient
sql = $"{sql} and ( headkey like @headkey)";
parameters.Add(new SqlParameter("@headkey", $"%{headKey}%") { DbType = DbType.String });

[1]: