Right syntax of SqlParameter

Question

I'm trying to convert :

command.Parameters.Add (new SqliteParameter (DbType.Int32) { Value = id });

To a normal SqlParameter :

command.Parameters.Add(new SqlParameter(DbType.Int32) { Value = id });

I've managed to convert every line now besides this one, I'm getting these errors :

Error   3   Argument 1: cannot convert from 'System.Data.DbType' to 'object[]'
Error   2   The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(object[])' has some invalid arguments

Full function code :

public User GetUser(int id)
{
    var u = new User();
    lock (locker)
    {
        connection = new SqlConnection("Data Source=" + path + ";Initial Catalog=DB;User ID=sa;Password=***********th");
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "Select * from tblUsers WHERE UserID = ?";
            command.Parameters.Add(new SqlParameter(DbType.Int32) { Value = id });
            var r = command.ExecuteReader();
            while (r.Read())
            {
                u = FromReader(r);
                break;
            }
        }
        connection.Close();
    }
    return u;
}

Soner Gönül · Accepted Answer · 2014-01-01T17:13:42.947

3

I think you are looking something this;

command.Parameters.Add(new SqlParameter("@YourParameterName", SqlDbType.Int32).Value = id;

After edit your question;

You can use AddWithValue method. Add(String, Object) method is obsolete. like;

command.CommandText = "Select * from tblUsers WHERE UserID = @id";
command.Parameters.AddWithValue("@id", id);

If you use SQL Server as a database, you should named your parameter add it to AddWithValue with the same name.

When you use OleDbCommand, order of parameter is important. Because as MSDN says:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

edited Jan 01 '14 at 17:13

answered Jan 01 '14 at 16:28

Soner Gönül

97,193
102
206
364

How am I supposed to know what to insert in `@YourParameterName` ? The SqliteParameter function is pretty straight forward. – Random Jan 01 '14 at 16:30
@Random This is a parameter name where you put your sql command. Show your sql command also to figure out what is your problem exactly. – Soner Gönül Jan 01 '14 at 16:33
Code is too long for a comment and I can't answer my own question because I have less than 10 reputation – Random Jan 01 '14 at 16:35
@Random Just put your relevant command part. You can always edit your question with `edit` button in under your question. – Soner Gönül Jan 01 '14 at 16:37
Added it in the question. Also, another question, why is the `WHERE UserID = ?`, the question mark I mean. – Random Jan 01 '14 at 16:49
So I should just disregard the `command.Parameters.Add(new SqlParameter(DbType.Int32) { Value = id });` And replace it with `command.Parameters.AddWithValue("@id", id);` ? – Random Jan 01 '14 at 17:17
@Random Yes, `Add` method is obsolete now. You should use `AddWithValue` instead. Also don't forget to change `UserID = ?` to `UserID = @id`. – Soner Gönül Jan 01 '14 at 17:21
Just a note for clarification: Only Add(String, Object) is obsolete. Add(String, SqlDbType), Add(String, SqlDbType, Int32), etc. are still legit and, in some circumstances, are better to use instead of AddWithValue(String, Object). (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection%28v=vs.110%29.aspx) – fodder Sep 24 '15 at 14:42

Right syntax of SqlParameter

1 Answers1

Linked