How to pass int parameters in Sql commandText

Question

How to pass an integer value like SQL command parameters?

I am trying like this:

cmd.CommandText = ("insert_questions '" + 
cmd.Parameters.AddWithValue(store_result,store_result) + "','" + 
cmd.Parameters.AddWithValue(store_title, store_title) + "', '" + 
cmd.Parameters.AddWithValue(store_des, store_des) + "'");

store_result is int and other 2 parameter are string type.

store_result is giving a error message like below.

Argument 1: cannot convert from 'int' to 'string'

in SP ,there is a another int type variable which will get store_result's value.

What is correct syntax for passing int parameters?

Thank you.

Don't put string delimiters around your int value (but I suspect this is the least of your problems). — phoog
– phoog, Commented Sep 7, 2012 at 6:35

aiodintsov · Accepted Answer · 2012-09-07 06:45:13Z

24

the correct way to go is

using(var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using(var command = new SqlCommand("SELECT * FROM Table WHERE ID=@someID",connection))
    {
        command.Parameters.AddWithValue("someID",1234);
        var r = command.ExecuteQuery();
    }
}

this means it works even with text queries. it's even easier with stored procedures - instead of sql query you just provide stored procedure name:

using(var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using(var command = new SqlCommand("insert_sproc",connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("someID",1234);
        var r = command.ExecuteQuery();
    }
}

answered Sep 7, 2012 at 6:45

aiodintsov

2,61517 silver badges17 bronze badges

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

Comments

John Woo · Accepted Answer · 2012-09-07 06:38:51Z

7

it should be like this,

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ("insert_questions") ;
cmd.Parameters.AddWithValue("@value", valueHere);
cmd.Parameters.AddWithValue("@value2", valueHere);

note that @value and @value2 are the parameters declared in your stored procedure.

answered Sep 7, 2012 at 6:38

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Ivan Golović · Accepted Answer · 2012-09-07 06:38:24Z

6

Try this:

        cmd.CommandText = ("insert_questions @store_result, @store_title, @store_des");
        cmd.Parameters.AddWithValue("@store_result", store_result);
        cmd.Parameters.AddWithValue("@store_title", store_title);
        cmd.Parameters.AddWithValue("@store_des", store_des);

answered Sep 7, 2012 at 6:38

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

Comments

Marc Gravell · Accepted Answer · 2012-09-07 06:44:10Z

You don't concatenate the SqlParameter instances; instead:

cmd.CommandText = "insert_questions @store_result, @store_title, @store_des";
cmd.Parameters.AddWithValue("store_result", store_result);
cmd.Parameters.AddWithValue("store_title", store_title);
cmd.Parameters.AddWithValue("store_des", store_des);

The names used in AddWithValue are used in the TSQL as @store_result, etc.

If we assume that inert_questions is actually a proc, then it is even simpler:

cmd.CommandText = "insert_questions";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("store_result", store_result);
cmd.Parameters.AddWithValue("store_title", store_title);
cmd.Parameters.AddWithValue("store_des", store_des);

Alternatively, if all of that seems tedious, tools like dapper-dot-net make this easier:

someOpenConnection.Execute("insert_questions",    
      new { store_result, store_title, store_des },
      commandType: CommandType.StoredProcedure);

Ram moorti · Accepted Answer · 2019-04-24 07:04:26Z

0

 private void ProcessData(MerchantLead data)
        {            
            var cmdCardJournal = new SqlCommand { CommandText = "BusinessLeadsInsert" };
            var sql = new Sqlquery();
            sql._cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            cmdCardJournal.Parameters.AddWithValue("@Name", data.FirstName);
            cmdCardJournal.Parameters.AddWithValue("@LastName", data.LastName);
            cmdCardJournal.Parameters.AddWithValue("@BusinessName", data.BusinessName);
            cmdCardJournal.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
            cmdCardJournal.Parameters.AddWithValue("@Email", data.Email);
            cmdCardJournal.Parameters.AddWithValue("@Website", data.Website);
            cmdCardJournal.Parameters.AddWithValue("@OTP", data.OTP);
            cmdCardJournal.Parameters.AddWithValue("@OTPExpired", DateTime.UtcNow.AddMinutes(30));

            sql.SpCommandExeNon(cmdCardJournal);
}

answered Apr 24, 2019 at 7:04

Ram moorti

11 bronze badge

1 Comment

Dieter Meemken Over a year ago

Can you explain a bit more?

Collectives™ on Stack Overflow

How to pass int parameters in Sql commandText

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related