Selecting and inserting into sql database

[lional]

Hi
I am very new to C# and learning as I go along
I am writing a student app
So what I am trying to accomplish is the following:
I have a table with modules linked to a subject. So when a student registers a subject I want the modules to automatically be written to the StudyHistory table.
I have tried the following script, it is not the best I am sure but I learn more and more each day

Any history will be appreciated

                conn = new SqlConnection(cs.DBConn);
                conn.Open();
                string querySelectedStudentModule = "select ModuleNumber,ModuleName from Modules where fkSubjectNumber='" + studentCourseSubject + "' and Archived = '0'";
                SqlCommand cmdSelectedStudentModule = new SqlCommand(querySelectedStudentModule);
                cmdSelectedStudentModule.Connection = conn;
               
                SqlDataReader rdrSelectedStudentModule = cmdSelectedStudentModule.ExecuteReader();

                ////// Loop through the result set
                while (rdrSelectedStudentModule.Read())
                {
                    studentModule = rdrSelectedStudentModule["ModuleNumber"].ToString();
                    studentModuleName = rdrSelectedStudentModule["ModuleName"].ToString();

                   
                   
                    int incrementStudentNumber = subjectPaymentNumber + 1;
                    string convertedNumber = incrementStudentNumber.ToString().PadLeft(6, '0');
                    string completeHistoryNumber = "HIS" + convertedNumber;

                    string queryInsertStudyHistory = "insert into StudyHistory (StudyHistoryNumber,StudentNumber,CourseNumber,CourseName,SubjectNumber,SubjectName,ModuleNumber,ModuleName) " +
                                "VALUES (@StudyHistoryNumber,@StudentNumber,@CourseNumber,@CourseName,@SubjectNumber,@SubjectName,@ModuleNumber,@ModuleName)";

                    SqlCommand cmdInsertStudyHistory = new SqlCommand(queryInsertStudyHistory);
                    cmdInsertStudyHistory.Connection = conn;

                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@StudyHistoryNumber", System.Data.SqlDbType.NVarChar, 50, "StudyHistoryNumber"));
                    cmdInsertStudyHistory.Parameters["@StudyHistoryNumber"].Value = completeHistoryNumber;
                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@StudentNumber", System.Data.SqlDbType.NVarChar, 50, "StudentNumber"));
                    cmdInsertStudyHistory.Parameters["@StudentNumber"].Value = studentNumberUpdater;
                    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 = studentSelectedCourseName;
                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@SubjectNumber", System.Data.SqlDbType.NVarChar, 50, "SubjectNumber"));
                    cmdInsertStudyHistory.Parameters["@SubjectNumber"].Value = studentCourseSubject;
                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@SubjectName", System.Data.SqlDbType.NVarChar, 250, "SubjectName"));
                    cmdInsertStudyHistory.Parameters["@SubjectName"].Value = studentSelectedSubjectName;
                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@ModuleNumber", System.Data.SqlDbType.NVarChar, 50, "ModuleNumber"));
                    cmdInsertStudyHistory.Parameters["@ModuleNumber"].Value = studentModule;
                    cmdInsertStudyHistory.Parameters.Add(new SqlParameter("@ModuleName", System.Data.SqlDbType.NVarChar, 250, "ModuleName"));
                    cmdInsertStudyHistory.Parameters["@ModuleName"].Value = studentModuleName;

                    cmdInsertStudyHistory.ExecuteReader();

                    // close reader
                   



                }
               
                rdrSelectedStudentModule.Close();
                conn.Close();

 

[jmcilhinney]

For future reference, please don't trim leading whitespace from the first line of your code and no others. It just makes the code harder to read. If you're not going to strip it from the others, don;t strip it from the first either, so the indenting is consistent. Better still, strip it from all lines. Hold the Alt key while selecting code to select an arbitrary rectangular section, enabling you to not select the leading whitespace.

 

[jmcilhinney]

There's no need to retrieve the data first and then insert separately. You can do both in one SQL statement, which you can execute with a single call to ExecuteNonQuery.

SQL INSERT INTO SELECT Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Just like any other SELECT statement, you can use a value instead of a column if you want to get a specific value in each row of the result set. In your case, that would be things like StudentNumber, which you should pas in via a parameter.

 

[lional]

Thanks for the assistance.
Will this work if I have a combination of variables and data from the select statement.
five of the seven come from variables and only ModuleNumber and ModuleName come from the select statement.
I just want to know if both can be done using Insert .... select statement.

Sorry this is new to me, I did a lot of php ... mysql which is not as strict as C# which led to sloppy code which I need to unlearn

I am really enjoying the learning curve of C#

 

[jmcilhinney]

Thanks for the assistance.
Will this work if I have a combination of variables and data from the select statement.
five of the seven come from variables and only ModuleNumber and ModuleName come from the select statement.
I just want to know if both can be done using Insert .... select statement.

Yes. The last two sentences in my previous post address that specifically.