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

Question

I keep getting the exception

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

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

Solution 1

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.

Solution 2

I tried all the above-mentioned steps but nothing helps.

Finally found and fixed it by changing the namespace

using System.Data.SqlClient to using Microsoft.Data.SqlClient

Arshad

Solution 3

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;
        }
    }

Solution 4

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

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

Solution 5

Try below

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

View more solutions