How to update multiple rows in database with different values in c#?

Question

I have a problem. I have a table named "RollNoSlip" which has a column named "AttendanceStatus". I have list variable named "getAttendance" of type "List<string>". I want to assign each values to each record of "RollNoSLip". Attendance column can contain "P" or "A". Problem is its showing only P in all records. Why?

var getAttendance = (from r in db.RollNoSlips
                                 from a in db.Attendances
                                 where r.RollNo == a.RollNo
                                 select a.AttendanceStatus).ToList();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["IQTSConnectionString"].ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand("update RollNoSlip set AttendanceStatus=@Attendance where RollNo between  10001 and 10045", con))
                {
                    cmd.Parameters.Add("@Attendance", SqlDbType.VarChar);
                    for (int i = 0; i < getAttendance.Count; i++)
                    {
                        cmd.Parameters["@Attendance"].Value = getAttendance[i];
                        cmd.ExecuteNonQuery();                                                                           
                    }
                }
            }

You are updating all the records (where rollno between 10001 and 10045) each execution. So they will all have the same AttendanceStatus as the final iteration. — Jacob H
– Jacob H, Commented Mar 6, 2018 at 14:16
the for loop has no meaning in your code. You just keep overwriting the same parameter and keep updating the same records between 10001 and 10045. So in the end they will all have the same value as the last value from your list variable — GuidoG
– GuidoG, Commented Mar 6, 2018 at 14:17

Sergey Kalinichenko · Accepted Answer · 2018-03-06 14:20:32Z

The reason you get all 'P's is that the last record in getAttendance list happens to have a 'P'. Had it been an 'A', all your records would be set to an 'A'.

You need to process items separately, passing their IDs to SQL:

// Include RollNo in getAttendance list
var getAttendance = (from r in db.RollNoSlips
                     from a in db.Attendances
                     where r.RollNo == a.RollNo
                     select new {a.AttendanceStatus, a.RollNo}
).ToList();
...
// Updates getAttendance rows in the database, one row at a time
using (var cmd = new SqlCommand("update RollNoSlip set AttendanceStatus=@Attendance where RollNo=@RollNo", con))
{
    cmd.Parameters.Add("@Attendance", SqlDbType.VarChar);
    cmd.Parameters.Add("@Attendance", SqlDbType.Int);
    for (int i = 0; i < getAttendance.Count; i++)
    {
        cmd.Parameters["@Attendance"].Value = getAttendance[i].AttendanceStatus;
        cmd.Parameters["@RollNo"].Value = getAttendance[i].RollNo;
        cmd.ExecuteNonQuery();                                                                           
    }
}

This is asuming there is a RollNo column, and there are as much records in the table as there are in getAttendance...
@GuidoG a.RollNo is definitely there, because OP shows it. The only assumption here is that his RollNos match the hard-coded values from the update query.

Jonni Chan · Accepted Answer · 2018-03-06 14:48:41Z

0

Assume you use SQL Server, then use the query below to update AttendanceStatus.

UPDATE RollNoSlip SET
  AttendanceStatus = A.AttendanceStatus
FROM RollNoSlip R
INNER JOIN Attendance A ON R.RollNo = A.RollNo
WHERE R.RollNo BETWEEN 10001 AND 10045

answered Mar 6, 2018 at 14:48

Jonni Chan

314 bronze badges

Collectives™ on Stack Overflow

How to update multiple rows in database with different values in c#?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related