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

Try this .Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar,128)).Value = username; — Sriram Sakthivel
– Sriram Sakthivel, Commented Oct 20, 2014 at 9:48
That wont build beacuase Value is not a property when is placed outside the sqlparameter. — Addeladde
– Addeladde, Commented Oct 20, 2014 at 9:52
Did you try that first? That should work. please try it out before commenting. If you tried post what is the compiler error. — Sriram Sakthivel
– Sriram Sakthivel, Commented Oct 20, 2014 at 9:54
I tried it. The compiler says: Error 28 'int' does not contain a definition for 'Value' and no extension method 'Value' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?) — Addeladde
– Addeladde, Commented Oct 20, 2014 at 9:56
We don't know about your abstraction. If you directly use SqlCommand you can do this command.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 128)).Value = username;. Based on this you can update your code is what I believe, — Sriram Sakthivel
– Sriram Sakthivel, Commented Oct 20, 2014 at 9:59

Addeladde · Accepted Answer · 2014-10-20 14:05:18Z

10

This seems to solve the problem.

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

answered Oct 20, 2014 at 14:05

Addeladde

7872 gold badges9 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saeb Amini · Accepted Answer · 2014-10-20 10:03:20Z

1

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.

answered Oct 20, 2014 at 10:03

Saeb Amini

24.6k10 gold badges85 silver badges78 bronze badges

2 Comments

Addeladde Over a year ago

that gives compiler error: No overload for method 'Add' takes 3 arguments

Saeb Amini Over a year ago

@Addeladde ofc it does

Kenneth Bautista · Accepted Answer · 2014-10-20 10:08:40Z

0

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.

edited Oct 20, 2014 at 10:08

answered Oct 20, 2014 at 9:55

Kenneth Bautista

12 bronze badges

6 Comments

Addeladde Over a year ago

Same error. This works but I want to specify length and datatype so I dont get a lot of Query plans. db.Parameters.Add(new SqlParameter("@username",username));

Kenneth Bautista Over a year ago

Edited previous post.

Addeladde Over a year ago

Thanks, this works but it would be really nice to have it all on one line

Kenneth Bautista Over a year ago

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

Addeladde Over a year ago

Kenneth that gives compiler error: No overload for method 'Add' takes 3 arguments

|

İsmet Alkan · Accepted Answer · 2014-10-20 10:53:34Z

0

Try AddWithValue.

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

answered Oct 20, 2014 at 10:53

İsmet Alkan

5,4474 gold badges43 silver badges64 bronze badges

2 Comments

Addeladde Over a year ago

That works but will give me a Query plan for each length of the username.

İsmet Alkan Over a year ago

can't you do the necessary controls within code part? I think it's more logical to do the controls before sending the data to database.

Collectives™ on Stack Overflow

SqlParameter Value or SqlValue?

4 Answers 4

Comments

2 Comments

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related