read from one table and insert in another

[lional]

Hi
I am busy with my student app where one subject consists of one or more modules.
I have a database table that groups modules by the subjects that they belong to so when a student is added and they are registered for a subject it should add all the modules for that subject to their profile.

I start by querying the database to get a list of all the modules for a particular subject

comboBoxDatabaseConnection.Open();
                using (SqlCommand cmdInsertStudyHistory = new SqlCommand("select ModuleNumber,ModuleName,ModuleCode from Modules where fkSubjectNumber=@subjectNumber and Archived = '0'", comboBoxDatabaseConnection))
                {
                    cmdInsertStudyHistory.Parameters.AddWithValue("@subjectNumber", studentCourseSubject);
                    SqlDataReader rdrSelectedStudentModule = cmdInsertStudyHistory.ExecuteReader();

                    if (rdrSelectedStudentModule.HasRows)
                    {
                        while (rdrSelectedStudentModule.Read())
                        {
                            studentModule = rdrSelectedStudentModule["ModuleNumber"].ToString();
                            studentModuleName = rdrSelectedStudentModule["ModuleName"].ToString();
                            studentModuleCode = rdrSelectedStudentModule["ModuleCode"].ToString();
                            InsertStudyHistory(comboBoxDatabaseConnection, studentModule);
                            
                        }
                    }

                    rdrSelectedStudentModule.Close();
                    comboBoxDatabaseConnection.Close();
                }

I am getting an error that says there is already an open data reader. I think it is because when I am calling this line " InsertStudyHistory(comboBoxDatabaseConnection, studentModule);" , the data reader rdrSelectedStudentModule is still open. I am not sure how to solve this
I have code for the InsertStudyHistory

public void InsertStudyHistory(SqlConnection comboBoxDatabaseConnection, string studentModule)
        {
          
            using (SqlCommand cmdInsertStudyHistory = new SqlCommand("insert into StudentModule (StudentNumber,CourseNumber,CourseName,SubjectNumber,SubjectName,ModuleNumber,ModuleCode,ModuleName) " +
                        "VALUES (@StudentNumber,@CourseNumber,@CourseName,@SubjectNumber,@SubjectName,@ModuleNumber,@ModuleCode,@ModuleName)", comboBoxDatabaseConnection))
            {
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@StudentNumber", System.Data.SqlDbType.NVarChar, 50, "StudentNumber"));
                cmdInsertStudyHistory.Parameters["@StudentNumber"].Value = selectedStudentNumber;
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@CourseNumber", System.Data.SqlDbType.NVarChar, 50, "CourseNumber"));
                cmdInsertStudyHistory.Parameters["@CourseNumber"].Value = selectedStudentSubject;
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@CourseName", System.Data.SqlDbType.NVarChar, 250, "CourseName"));
                cmdInsertStudyHistory.Parameters["@CourseName"].Value = editStudentSubjectProgrammeComboBox.Text.ToString();
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@SubjectNumber", System.Data.SqlDbType.NVarChar, 50, "SubjectNumber"));
                cmdInsertStudyHistory.Parameters["@SubjectNumber"].Value = editStudentSubjectComboBox.SelectedValue.ToString(); ;
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@SubjectName", System.Data.SqlDbType.NVarChar, 250, "SubjectName"));
                cmdInsertStudyHistory.Parameters["@SubjectName"].Value = editStudentSubjectComboBox.Text.ToString();
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@ModuleNumber", System.Data.SqlDbType.NVarChar, 50, "ModuleNumber"));
                cmdInsertStudyHistory.Parameters["@ModuleNumber"].Value = studentModule;
                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@ModuleCode", System.Data.SqlDbType.NVarChar, 50, "ModuleCode"));
                cmdInsertStudyHistory.Parameters["@ModuleCode"].Value = studentModuleCode;


                cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@ModuleName", System.Data.SqlDbType.NVarChar, 250, "ModuleName"));
                cmdInsertStudyHistory.Parameters["@ModuleName"].Value = studentModuleName;
                cmdInsertStudyHistory.ExecuteNonQuery();
            }
            comboBoxDatabaseConnection.Close();


        }
            
        }

Any assistance will be appreciated

 

[jmcilhinney]

The quickest and easiest way is to enable MARS (Multiple Active Result Sets) in your connection string. The best way would be to restructure your code so you don't even need to retrieve any data into your app. I don't see why you shouldn't be able to do the whole lot in SQL. Without having looked too closely, instead of using a VALUES clause in an INSERT statement, you can use a query and insert data selected directly from one or more other tables. You should be able to move the lot with a single call to ExecuteNonQuery. If you weren't going to do that, the next best option would be to use a SqlDataAdapter and call Fill to retrieve all the data into a DataTable and then call Update to save it all back again.

 

[Skydiver]

I have a database table that groups modules by the subjects that they belong to so when a student is added and they are registered for a subject it should add all the modules for that subject to their profile.

Having one table per student is a very poor database design. Time to learn about foreign keys and joins.

 

[lional]

Thanks for the advice on SqlDataAdapter. I have used it in other parts of my app so I will look into that. I don't have one table per student. I have a students table that lists all the students personal details and they are assigned a student number. I then have a table that lists all the modules for all the students. It is one table that lists all the students and has the student number as the foreign key to which modules are assigned to them. Thanks for your assistance, much appreciated

 

[jmcilhinney]

Thanks for the advice on SqlDataAdapter. I have used it in other parts of my app so I will look into that.

One thing to keep in mind is that, when you call Fill, it will implicitly call AcceptChanges on the DataTable. That means that there will appear to be no changes to save, which is no good if you then want to insert that data into another table. In that case, set AcceptChangesOnFill to false and all RowStates will remain Added, ready for the rows to be inserted. Note that you can use a single data adapter for both. The SelectCommand and InsertCommand don't have to work on the same tables.