CODEWITHSUNDEEP
asp.netsqlsql-serverinsertrows
De De De De
De De De De

Reputation: 426

asp SQL Statement to insert multiple rows of record into Table

I am trying to create an attendance table to record down the activities the students have participated. The code below is triggered when a new activity is created. When a new activity is created, I want to insert all the student record into attendance table and mark the default attend attribute as false. (the attend attribute is to mark attendance, sets as 0)

The problem is that the attendance table cannot be populated. The code will stop proceeding at the line: numofRecordsAffected = command.ExecuteNonQuery();

I am trying to get the all the sigstudentid(s) from the Student table. I do not know if the usage of foreach() is recommended in this case, but any help will be appreciated.

I need a solution!

    public int InsertAttendance(int num)
        {
        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlCommand command = new SqlCommand();
        SqlConnection connect = new SqlConnection();
        DataSet dataset = new DataSet();
        String sqlText = "SELECT * FROM Student";
        connect.ConnectionString = "Data Source=TECKISTTLE; Initial Catalog=Assignment;    Integrated Security=True";
        command.Connection = connect;
        command.CommandText = sqlText;

        int numofRecordsAffected = 0;

        adapter.SelectCommand = command;
        connect.Open();
        adapter.Fill(dataset, "StudentData");
        sqlText = "";

        foreach (DataRow item in dataset.Tables["StudentData"].Rows)
        {
            sqlText += " INSERT INTO Attendance(SIGStudentID, ActivityId, Attendance) VALUES(@SIGStudentID,@ActivityId,@Attendance); ";
            command.Parameters.Clear();
            command.Parameters.Add("@SIGStudentID", SqlDbType.Int);
            command.Parameters["@SIGStudentID"].Value = Convert.ToInt32(item["SIGStudentID"]);
            command.Parameters.Add("@ActivityId", SqlDbType.Int);
            command.Parameters["@ActivityId"].Value = num;
            command.Parameters.Add("@Attendance", SqlDbType.Bit);
            command.Parameters["@Attendance"].Value = 0;



        }
        numofRecordsAffected = command.ExecuteNonQuery();

        connect.Close();

        return numofRecordsAffected;
    }

Upvotes: 0

Views: 2852

Answers (2)

Iqbal
Iqbal

Reputation: 1316

As you put the code numofRecordsAffected = command.ExecuteNonQuery(); out side your foreach. It just execute one time. As this code is actual statement to INSERT data in to the table, it will INSERT only one ROW the last one.

Just try to put your code

numofRecordsAffected = command.ExecuteNonQuery();

inside the foreach block. Then it will INSERT each row.

    public int InsertAttendance(int num)
    {
    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommand command = new SqlCommand();
    SqlConnection connect = new SqlConnection();
    DataSet dataset = new DataSet();
    String sqlText = "SELECT * FROM Student";
    connect.ConnectionString = "Data Source=TECKISTTLE; Initial Catalog=Assignment;    Integrated Security=True";
    command.Connection = connect;
    command.CommandText = sqlText;

    int numofRecordsAffected = 0;

    adapter.SelectCommand = command;
    connect.Open();
    adapter.Fill(dataset, "StudentData");
    sqlText = "";
    numofRecordsAffected = 0;
    sqlText = "INSERT INTO Attendance(SIGStudentID, ActivityId, Attendance) VALUES(@SIGStudentID,@ActivityId,@Attendance); ";
    command.CommandText = sqlText;
    foreach (DataRow item in dataset.Tables["StudentData"].Rows)
    {
        command.Parameters.Clear();
        command.Parameters.AddWithValue("@SIGStudentID", Convert.ToInt32(item["SIGStudentID"]);
        command.Parameters.AddWithValue("@ActivityId", num);
        command.Parameters.AddWithValue("@Attendance", 0);

        numofRecordsAffected += command.ExecuteNonQuery();

    }


    connect.Close();

    return numofRecordsAffected;
}

Upvotes: 2

Display Name is missing
Display Name is missing

Reputation: 6227

You're probably over complicating it a bit by using a loop and could do something like this instead:

"INSERT INTO Attendance(SIGStudentID, ActivityId, Attendance) select SIGStudentID, " + num + " as ActivityId, 0 as Attendance from Student;"

Reference:

insert one column data from a table into other table, but the other column data will be specified dynamically

Insert all values of a table into another table in SQL

Upvotes: 0

Related Questions