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
– marc_s, Commented Feb 5, 2014 at 19:26

Iqbal · Accepted Answer · 2014-02-05 19:14:14Z

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?
Thanks, Accept my answer and vote up to show your appreciation.
ok, but as this is your question, you can accept the answer even if you don't have reputation.

Community · Accepted Answer · 2017-05-23 10:25:57Z

0

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

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Feb 5, 2014 at 19:07

Display Name is missing

6,2573 gold badges38 silver badges49 bronze badges

Collectives™ on Stack Overflow

asp SQL Statement to insert multiple rows of record into Table

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related