SqlParameter Value or SqlValue?

10,962

Solution 1

This seems to solve the problem.

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

Solution 2

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.

Share:
10,962
Addeladde
Author by

Addeladde

Web developer who lives in Stockholm, Sweden

Updated on August 08, 2022

Comments

  • Addeladde
    Addeladde over 1 year

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