Menu
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();
}
}
298
C# SqlParameter is a handy feature allows you to safely pass a parameter to a SqlCommand object in . NET. A security best practice when writing . NET data access code, is to always use parameters in SqlCommand objects (whenever parameters are required of course).
define parameters used in command object SqlParameter param = new SqlParameter(); param. ParameterName = "@City"; param.
Parameters are used to exchange data between stored procedures and functions and the application or tool that called the stored procedure or function: Input parameters allow the caller to pass a data value to the stored procedure or function.
This seems to solve the problem.
.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 128) { Value = username });
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
