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 ...)? — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Apr 19, 2017 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
– SS_DBA, Commented Apr 19, 2017 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
– Zohar Peled, Commented Apr 19, 2017 at 13:29
See this for several other solutions - but @DmitryBychenko is a good solution stackoverflow.com/questions/1293330/… — PaulF
– PaulF, Commented Apr 19, 2017 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
– ksuProgrammer, Commented Apr 19, 2017 at 13:40

Dmitrii Bychenko · Accepted Answer · 2017-04-19 13:36:50Z

2

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

edited Apr 19, 2017 at 13:36

answered Apr 19, 2017 at 13:30

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

Comments

Kempeth · Accepted Answer · 2017-04-19 13:32:51Z

1

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.

answered Apr 19, 2017 at 13:32

Kempeth

1,9482 gold badges22 silver badges40 bronze badges

Comments

Dmitrii Bychenko · Accepted Answer · 2017-04-19 14:43:04Z

0

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

edited Apr 19, 2017 at 14:43

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

answered Apr 19, 2017 at 13:32

Shival

2741 gold badge2 silver badges10 bronze badges

Collectives™ on Stack Overflow

Use SQL command as a parameter value in C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related