Use SQL command as a parameter value in C#

Question

I have a SQL command I am running that works great however for one of the AddWithValue parameters I want to use another SQL command to get that value... this is what I have but the cmd2 I want to use isn't working. Is it even possible to get data that way in theory it makes sense but it doesn't seem to work..

    cmd2 = new SqlCommand("SELECT acctNum FROM custInfo WHERE customerName = @customerName", cn);
    cmd2.Parameters.AddWithValue("@customerName", customerDropDown.Text);

    cmd = new SqlCommand("UPDATE custInfo SET ctGal = (ctGal - (@contractGallons)) WHERE acctNum = @acctNum", cn);

    cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text)        
    cmd.Parameters.AddWithValue("@acctNum", cmd2);

`update ... where acctNum in (select c.acctNum from custInfo c ...)`? — Dmitry Bychenko, Apr 19 '17 at 13:25
You don't execute `cmd2` to get the `acctNum` value. But @DmitryBychenko will work better since it's only 1 call to the db. — SS_DBA, Apr 19 '17 at 13:27
Or even simpler - `UPDATE custInfo SET ctGal = (ctGal - (@contractGallons)) WHERE customerName = @customerName` - You don't actually need the `acctNum` if you already have the `@customerName`... — Zohar Peled, Apr 19 '17 at 13:29
See this for several other solutions - but @DmitryBychenko is a good solution http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — PaulF, Apr 19 '17 at 13:29
I do need it to work based off account number which is why I want it done this way... we have accounts that have 5-10 tanks each with their own "customer name" and we want them all to be grouped by acct num when the calculation is done.. — ksuProgrammer, Apr 19 '17 at 13:40

Dmitry Bychenko · Answer 1 · 2017-04-19T13:36:50.063

I suggested combining both queries into one:

//DONE: let keep query readable
string sql = 
  @"UPDATE custInfo 
       SET ctGal = (ctGal - (@contractGallons)) 
     WHERE acctNum IN (SELECT c.acctNum
                         FROM custInfo c 
                        WHERE c.customerName = @customerName)";

//DONE: wrap IDisposable into using
using (var cmd = new SqlCommand(sql, cn)) {
  //TODO: get rid of AddWithValue, but specify the actual fields' types
  cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text);
  cmd.Parameters.AddWithValue("@customerName", customerDropDown.Text);

  cmd.ExecuteNonQuery();
}

score 1 · Answer 2 · answered Apr 19 '17 at 13:32

You have two choices if you want to go this route:

Combine the two queries when you instantiate the second SqlCommand. This will require adding a second parameter to the second command.
Or run the first command. Fetch the resulting acctNum and add it as a value for the second command.

Probably better would be to rewrite the two queries into a single joined query.

score 0 · Accepted Answer · edited Apr 19 '17 at 14:43

You must use cmd2.ExecuteReader() to get the acctNum for example

You can try following code

using (SqlDataReader reader = cmd2.ExecuteReader())
{
  if (reader.Read())
  {
    cmd = new SqlCommand(@"UPDATE custInfo SET ctGal = (ctGal - 
     (@contractGallons)) WHERE acctNum = @acctNum", cn);

    cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text)        
    cmd.Parameters.AddWithValue("@acctNum", reader["acctNum"]);

  }
}

Hope this will help..

Use SQL command as a parameter value in C#

3 Answers3