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-01 17:13:42Z

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 1, 2014 at 17:13

answered Jan 1, 2014 at 16:28

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

8 Comments

Random Over a year ago

How am I supposed to know what to insert in @YourParameterName ? The SqliteParameter function is pretty straight forward.

Soner Gönül Over a year ago

@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.

Random Over a year ago

Code is too long for a comment and I can't answer my own question because I have less than 10 reputation

Soner Gönül Over a year ago

@Random Just put your relevant command part. You can always edit your question with edit button in under your question.

Random Over a year ago

Added it in the question. Also, another question, why is the WHERE UserID = ?, the question mark I mean.

|

Collectives™ on Stack Overflow

Right syntax of SqlParameter

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related