CODEWITHSUNDEEP
c#t-sqldao
shikiko
shikiko

Reputation: 162

Update SQL Server database using a list

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.

Upvotes: 2

Views: 3632

Answers (5)

j.f.
j.f.

Reputation: 194

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

Upvotes: 1

user8738888
user8738888

Reputation:

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

Upvotes: 1

shikiko
shikiko

Reputation: 162

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

Upvotes: 0

Ipsit Gaur
Ipsit Gaur

Reputation: 2927

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

  }
}

Upvotes: 2

Rahul
Rahul

Reputation: 77896

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

Upvotes: 3

Related Questions