SqlParameter Value or SqlValue?

Question

I get an error saying this:

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

On this code:

.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar,128).Value = username);

If I change this to:

.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar,128).SqlValue = username);

Shouldn't just value work?

is'nt sqlValue the database type?

Here is the DAL i use:

public class DBAccess : IDisposable
{

    private IDbCommand cmd = new SqlCommand();
    private string strConnectionString = "";
    private bool handleErrors = false;
    private string strLastError = "";

    public DBAccess()
    {
        strConnectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        SqlConnection cnn = new SqlConnection();
        cnn.ConnectionString = strConnectionString;
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.StoredProcedure;
    }


    public CommandType CommandType
    {
        get
        {
            return cmd.CommandType;
        }
        set
        {
            cmd.CommandType = value;
        }
    }

    public IDataReader ExecuteReader()
    {
        IDataReader reader = null;
        try
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                this.Open();
            }
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }
        return reader;
    }

    public IDataReader ExecuteReader(string commandtext)
    {
        IDataReader reader = null;
        try
        {
            cmd.CommandText = commandtext;
            reader = this.ExecuteReader();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return reader;
    }

    public object ExecuteScalar()
    {
        object obj = null;
        try
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                this.Open();
            }
            obj = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return obj;
    }

    public object ExecuteScalar(string commandtext)
    {
        object obj = null;
        try
        {
            cmd.CommandText = commandtext;
            obj = this.ExecuteScalar();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return obj;
    }

    public int ExecuteNonQuery()
    {
        int i = -1;
        try
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                this.Open();
            }
            i = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return i;
    }


    public int ExecuteNonQuery(string commandtext)
    {
        int i = -1;
        try
        {
            cmd.CommandText = commandtext;
            i = this.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return i;
    }


    public DataSet ExecuteDataSet()
    {
        SqlDataAdapter da = null;
        DataSet ds = null;
        try
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                this.Open();
            }
            da = new SqlDataAdapter();
            da.SelectCommand = (SqlCommand)cmd;
            ds = new DataSet();
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return ds;
    }


    public DataSet ExecuteDataSet(string commandtext)
    {
        DataSet ds = null;
        try
        {
            cmd.CommandText = commandtext;
            ds = this.ExecuteDataSet();
        }
        catch (Exception ex)
        {
            if (handleErrors)
                strLastError = ex.Message;
            else
                throw;
        }

        return ds;
    }

    public int CommandTimeout
    {
        get
        {
            return cmd.CommandTimeout;
        }
        set
        {
            cmd.CommandTimeout = value;
        }
    }

    public IDbConnection Connection
    {
        get
        {
            return cmd.Connection;
        }
        set
        {
            cmd.Connection = value;
        }
    }

    public string CommandText
    {
        get
        {
            return cmd.CommandText;
        }
        set
        {
            cmd.CommandText = value;
            cmd.Parameters.Clear();
        }
    }

    public IDataParameterCollection Parameters
    {
        get
        {
            return cmd.Parameters;
        }
    }

    public IDbTransaction Transaction
    {
        get
        {
            return cmd.Transaction;
        }
        set
        {
            cmd.Transaction = value;
        }
    }

    public void AddParameter(string paramname, object paramvalue)
    {
        var param = new SqlParameter(paramname, paramvalue);
        cmd.Parameters.Add(param);
    }

    public void AddParameter(IDataParameter param)
    {
        cmd.Parameters.Add(param);
    }


    public IDbTransaction BeginTransaction()
    {
        var tran = cmd.Connection.BeginTransaction();
        cmd.Transaction = tran;
        return tran;
    }

    public void CommitTransaction()
    {
        cmd.Transaction.Commit();
    }

    public void RollbackTransaction()
    {
        cmd.Transaction.Rollback();
    }

    public System.Data.ConnectionState State
    {
        get
        {
            return cmd.Connection.State;
        }
    }

    public string ConnectionString
    {
        get
        {
            return strConnectionString;
        }
        set
        {
            strConnectionString = value;
        }
    }

    private void Open()
    {
        cmd.Connection.Open();
    }

    public bool HandleExceptions
    {
        get
        {
            return handleErrors;
        }
        set
        {
            handleErrors = value;
        }
    }

    public string LastError
    {
        get
        {
            return strLastError;
        }
    }

    public void Dispose()
    {
        cmd.Connection.Close();
        cmd.Connection.Dispose();
        cmd.Dispose();
    }
}

Answer 1

This seems to solve the problem.

.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 128) { Value = username });

Answer 2

Try AddWithValue.

command.Parameters.AddWithValue("@username", username);

Answer 3

Try your code like this.

SqlParameter param = new SqlParameter("@username",SqlDbType.NVarChar, 128);

param.Value = username;

command.Parameters.Add(param);  

see http://msdn.microsoft.com/en-us/library/40959t6x(v=vs.110).aspx.

Answer 4

Actually even your second line of code won't work, because both

new SqlParameter("@username", SqlDbType.NVarChar,128).Value = username

and

new SqlParameter("@username", SqlDbType.NVarChar,128).SqlValue = username

are expressions which evaluate to a string object, and as the error is telling you SqlParameterCollection does not accept string objects.

A neater way to add your parameters with values would be like this:

.Parameters.Add("@username", SqlDbType.NVarChar, 128).Value = username;

This is possible because Add returns a SqlParameter object so you can set its Value property on the same line.