asp SQL Statement to insert multiple rows of record into Table

Question

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

That's **NOT** ASP Classic (that would be VBA code only) - I assume you mean `asp.net` instead — marc_s, Feb 05 '14 at 19:26

Iqbal · Accepted Answer · 2014-02-05T19:14:14.980

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

The Attendance table could not receive any record from the code above. Is there any thing I should take note of? — De De De De, Feb 05 '14 at 18:54
Thanks, Accept my answer and vote up to show your appreciation. — Iqbal, Feb 05 '14 at 19:44
ok, but as this is your question, you can accept the answer even if you don't have reputation. — Iqbal, Feb 06 '14 at 14:17

score 0 · Answer 2 · edited May 23 '17 at 10:25

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

asp SQL Statement to insert multiple rows of record into Table

2 Answers2