SqlParameter Value or SqlValue?
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.
Comments
-
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(); } }