CODEWITHSUNDEEP
c#sql.net
ksuProgrammer
ksuProgrammer

Reputation: 105

Use SQL command as a parameter value in C#

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

Upvotes: 0

Views: 144

Answers (3)

Shival
Shival

Reputation: 274

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

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186823

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

Upvotes: 2

Kempeth
Kempeth

Reputation: 1948

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

  1. Combine the two queries when you instantiate the second SqlCommand. This will require adding a second parameter to the second command.
  2. 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.

Upvotes: 1

Related Questions