Update SQL Server database using a list

Question

I'm trying to update a SQL Server database and have a list of strings which are seat numbers

string strQuery = ("Update tblTransactionItems set selected = 'T' Where SeatNumber = @SeatNumber");

using (SqlCommand cmd = new SqlCommand(strQuery, con)) {
    con.Open();

    foreach (var item in list) {
       string test = item.ToString();
       test = test.Replace('"', ' ').Trim();

       cmd.Parameters.AddWithValue("@SeatNumber", test);
       cmd.ExecuteNonQuery();
    }
}

It keeps saying that the variable @SeatNumber has been declared and only the first item in the list is updated. Any ways on how to approach this?

Update :

I just placed the foreach loop outside the using and it works.

because you add @seatnumber each itteration of your foreach loop building the string.. — BugFinder
– BugFinder, Commented Nov 9, 2017 at 8:29

Rahul · Accepted Answer · 2017-11-09 08:29:33Z

3

You are trying to make a inlist but using a equality operator saying Where SeatNumber = @SeatNumber" where as you should using a IN operator like

Where SeatNumber IN (@SeatNumber)

Again this is not the right way, rather you should populate those inlist values to a DataTable and pass that as DbType.Structured instead

answered Nov 9, 2017 at 8:29

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

Ipsit Gaur · Accepted Answer · 2017-11-09 08:29:57Z

2

Its because you have already added CommandParameter to SqlCommand and you are again adding it in loop

string strQuery = ("Update tblTransactionItems set selected = 'T' Where SeatNumber = @SeatNumber");
con.Open();
foreach (var item in list) {
  using (SqlCommand cmd = new SqlCommand(strQuery, con)) {
    string test = item.ToString();
    test = test.Replace('"', ' ').Trim();
    cmd.Parameters.AddWithValue("@SeatNumber", test);
    cmd.ExecuteNonQuery();

  }
}

answered Nov 9, 2017 at 8:29

Ipsit Gaur

2,9472 gold badges26 silver badges38 bronze badges

Comments

score 1 · Accepted Answer · 2017-11-09 08:55:00Z

You need to initiate SqlCommand parameter every time. In your code you are using the SQLCommand to execute the query first time only and it updates your table with first item in the list. During the next iteration since the sqlcommand still holds the last value (query to update previous item from the list), it throws an error. The right way to do this is as follows:

try{
    string strQuery = ("Update tblTransactionItems set selected = 'T' Where SeatNumber = @SeatNumber");
    con.Open();
    foreach (var item in list) {
      using (SqlCommand cmd = new SqlCommand(strQuery, con)) {
    string test = item.ToString();
    test = test.Replace('"', ' ').Trim();
    cmd.Parameters.AddWithValue("@SeatNumber", test);
    cmd.ExecuteNonQuery();

      }
    }
}
finally{ con.Close() }

j.f. · Accepted Answer · 2017-11-09 09:05:24Z

1

You can add one more clear parameter line to make it run.

string strQuery = ("Update tblTransactionItems set selected = 'T' Where SeatNumber = @SeatNumber");

    using (SqlCommand cmd = new SqlCommand(strQuery, con)) {
    con.Open();
    foreach (var item in list) {
        string test = item.ToString();
        test = test.Replace('"', ' ').Trim();
        cmd.Parameters.AddWithValue("@SeatNumber", test);
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
    }

answered Nov 9, 2017 at 9:05

j.f.

1949 bronze badges

Comments

shikiko · Accepted Answer · 2017-11-09 08:32:53Z

0

I just placed the foreach loop outside the using and it works.

answered Nov 9, 2017 at 8:32

shikiko

1621 silver badge7 bronze badges

Collectives™ on Stack Overflow

Update SQL Server database using a list

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related