locked
Help with inserting from one table to another in C# RRS feed

  • Question

  • User2129900979 posted

    Hello,

    I'm new to programming and I'm stuck on trying to get some code to work.  I keep getting an error invalid column name.  I'm trying to insert data from one table into another table in the same database.  The info being pulled is based off what the user types in. This is the code I have written:

            private void addCourseButton_Click(object sender, EventArgs e)
            {
                string Course_id = null;
                string Course_name = null;
                string Course_TimeBegin = null;
                string Course_TimeEnd = null;
                string Course_Days = null;
                string Teacher_Name = null;
    
    
                SqlConnection stud = new SqlConnection(@"Data Source=GAMING\MSSQLSERVER1;Initial Catalog=Schedules;Integrated Security=True");
                stud.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM COURSES WHERE COURSE_ID = @courseEntered",stud);
                SqlParameter courseEntered = new SqlParameter();
                courseEntered.ParameterName = "@courseEntered";
                courseEntered.Value = courseIDText.Text;
                cmd.Parameters.Add(courseEntered);
                stud.Close();
    
                stud.Open();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = stud;
    
                query = cmd.ExecuteReader();
                while (query.Read())
                {                
                    Course_id = (string) query ["COURSE_ID"];
                    Course_name = (string)query ["COURSE_NAME"];
                    Course_TimeBegin = (string)query ["TIME_BEGIN"];
                    Course_TimeEnd = (string)query["TIME_END"];
                    Course_Days = (string)query ["COURSE_DAYS"];
                    Teacher_Name = (string)query ["PROFESSOR"];
                }
                stud.Close();
    
    
                con = new SqlConnection(@"Data Source = GAMING\MSSQLSERVER1; Initial Catalog = Schedules; Integrated Security = True");
                con.Open();
                cmd = new SqlCommand("INSERT INTO STUDENT_CLASSES (STUDENT_ID,STUDENT_FNAME,STUDENT_LNAME,COURSE_ID,COURSE_NAME,TIME_BEGIN,TIME_END,COURSE_DAYS, TEACHER_NAME) VALUES(studentID2, fnamePassed, lnamePassed, Course_ID, Course_name, Course_TimeBegin, Course_TimeEnd, Course_Days, Teacher_Name)", con);
                
                cmd.ExecuteNonQuery();
                con.Close();                       
            } 

    I know it has something to do with my select statement, but not sure how to fix it.  Any help will be greatly appreciated.

    Thanks. 

    Sunday, March 26, 2017 2:19 AM

Answers

  • User-1509636757 posted

    There are multiple issues in your code that may causing issue. Main issue is, you are not passing values as parameters in your Insert query.

    Also, I would highly recommend to use one stored procedure that do both stuff of selecting and inserting data, instead of writing two inline queries. Check below modified code, with few corrections, you may required to do some modifications based on your code scenario:

    private void addCourseButton_Click(object sender, EventArgs e)
    {
        string Course_id = null;
        string Course_name = null;
        string Course_TimeBegin = null;
        string Course_TimeEnd = null;
        string Course_Days = null;
        string Teacher_Name = null;
    
        using (SqlConnection stud = new SqlConnection(@"Data Source=GAMING\MSSQLSERVER1;Initial Catalog=Schedules;Integrated Security=True"))
        {
            stud.Open();
    
            SqlCommand cmd = new SqlCommand("SELECT * FROM COURSES WHERE COURSE_ID = @courseEntered", stud);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@courseEntered", courseIDText.Text));
    
            SqlDataReader query = cmd.ExecuteReader();
            if (query.HasRows)
            {
                while (query.Read())
                {
                    Course_id = (string)query["COURSE_ID"];
                    Course_name = (string)query["COURSE_NAME"];
                    Course_TimeBegin = (string)query["TIME_BEGIN"];
                    Course_TimeEnd = (string)query["TIME_END"];
                    Course_Days = (string)query["COURSE_DAYS"];
                    Teacher_Name = (string)query["PROFESSOR"];
                }
                query.Close();
            }
    
            string insertQuery = @"INSERT INTO STUDENT_CLASSES (STUDENT_ID,STUDENT_FNAME,STUDENT_LNAME,COURSE_ID,COURSE_NAME,TIME_BEGIN,TIME_END,COURSE_DAYS, TEACHER_NAME) VALUES " +
                                    "(@studentID2, @fnamePassed, @lnamePassed, @Course_ID, @Course_name, @Course_TimeBegin, @Course_TimeEnd, @Course_Days, @Teacher_Name)";
            cmd = new SqlCommand(insertQuery, stud);
            cmd.Parameters.Add(new SqlParameter("@studentID2", _____)); //-- pass StudentID2 value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@fnamePassed", _____)); //-- pass fnamePassed value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@lnamePassed", _____)); //-- pass lnamePassed value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@Course_ID", Course_id));
            cmd.Parameters.Add(new SqlParameter("@Course_name", Course_name));
            cmd.Parameters.Add(new SqlParameter("@Course_TimeBegin", Course_TimeBegin));
            cmd.Parameters.Add(new SqlParameter("@Course_TimeEnd", Course_TimeEnd));
            cmd.Parameters.Add(new SqlParameter("@Course_Days", Course_Days));
            cmd.Parameters.Add(new SqlParameter("@Teacher_Name", Teacher_Name));
    
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            stud.Close();
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, March 26, 2017 4:31 AM

All replies

  • User-1509636757 posted

    There are multiple issues in your code that may causing issue. Main issue is, you are not passing values as parameters in your Insert query.

    Also, I would highly recommend to use one stored procedure that do both stuff of selecting and inserting data, instead of writing two inline queries. Check below modified code, with few corrections, you may required to do some modifications based on your code scenario:

    private void addCourseButton_Click(object sender, EventArgs e)
    {
        string Course_id = null;
        string Course_name = null;
        string Course_TimeBegin = null;
        string Course_TimeEnd = null;
        string Course_Days = null;
        string Teacher_Name = null;
    
        using (SqlConnection stud = new SqlConnection(@"Data Source=GAMING\MSSQLSERVER1;Initial Catalog=Schedules;Integrated Security=True"))
        {
            stud.Open();
    
            SqlCommand cmd = new SqlCommand("SELECT * FROM COURSES WHERE COURSE_ID = @courseEntered", stud);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@courseEntered", courseIDText.Text));
    
            SqlDataReader query = cmd.ExecuteReader();
            if (query.HasRows)
            {
                while (query.Read())
                {
                    Course_id = (string)query["COURSE_ID"];
                    Course_name = (string)query["COURSE_NAME"];
                    Course_TimeBegin = (string)query["TIME_BEGIN"];
                    Course_TimeEnd = (string)query["TIME_END"];
                    Course_Days = (string)query["COURSE_DAYS"];
                    Teacher_Name = (string)query["PROFESSOR"];
                }
                query.Close();
            }
    
            string insertQuery = @"INSERT INTO STUDENT_CLASSES (STUDENT_ID,STUDENT_FNAME,STUDENT_LNAME,COURSE_ID,COURSE_NAME,TIME_BEGIN,TIME_END,COURSE_DAYS, TEACHER_NAME) VALUES " +
                                    "(@studentID2, @fnamePassed, @lnamePassed, @Course_ID, @Course_name, @Course_TimeBegin, @Course_TimeEnd, @Course_Days, @Teacher_Name)";
            cmd = new SqlCommand(insertQuery, stud);
            cmd.Parameters.Add(new SqlParameter("@studentID2", _____)); //-- pass StudentID2 value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@fnamePassed", _____)); //-- pass fnamePassed value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@lnamePassed", _____)); //-- pass lnamePassed value here to  insert in database
            cmd.Parameters.Add(new SqlParameter("@Course_ID", Course_id));
            cmd.Parameters.Add(new SqlParameter("@Course_name", Course_name));
            cmd.Parameters.Add(new SqlParameter("@Course_TimeBegin", Course_TimeBegin));
            cmd.Parameters.Add(new SqlParameter("@Course_TimeEnd", Course_TimeEnd));
            cmd.Parameters.Add(new SqlParameter("@Course_Days", Course_Days));
            cmd.Parameters.Add(new SqlParameter("@Teacher_Name", Teacher_Name));
    
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            stud.Close();
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, March 26, 2017 4:31 AM
  • User2129900979 posted

    Hello kaushalparik27,

    Thank you for the response.  It worked. I tried doing parameters, but I received the same error. I must have typed something wrong.  I know my code is probably very inefficient and hopefully, in time I will learn to write better code.  I'm new to coding and have zero knowledge of any programming language.  Any suggestions on where to find good resources on C#? 

    Thanks,

    blakkheartt12

    Sunday, March 26, 2017 12:35 PM
  • User-1509636757 posted

    Here is a good tutorial series, where you can try writing code given fiddle in site itself by Microsoft: .NET - Tutorials

    Another one: C# Tutorial

    Sunday, March 26, 2017 4:04 PM