How to insert boundfield values and template field value in gridview to database

0
votes

in the gridview control there are bound field and template field by clicking save button the gridview values should be saved in another table.

enter image description here the gridview control

code tried protected void Button2_Click(object sender, EventArgs e) { foreach (GridViewRow row in GridView1.Rows) {

            String S_ID = row.Cells[0].Text;
            String Sub_Name = row.Cells[1].Text;
            String marks = (row.Cells[2].Text);
            String Semester = DropDownList2.SelectedItem.Text;




            String query = "insert into Marks(S_ID,Sub_Name,Semester,Marks) values(" + S_ID + ",'" + Sub_Name + "','" + Semester + "','" + marks + "')";
            String mycon = "Data Source=DESKTOP-HBMQHKE\\MSSQLSERVER01;Initial Catalog=College;Integrated Security=True";
            SqlConnection con = new SqlConnection(mycon);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = query;
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
        }

error : System.Data.SqlClient.SqlException: 'Incorrect syntax near 'mcl141'.'

1
asp.netsql-server

1 Answers

0
votes

Try to use parameters:

            String query = "insert into Marks(S_ID,Sub_Name,Semester,Marks) values(@S_ID, @Sub_Name, @Semester, @marks)";
            String mycon = "Data Source=DESKTOP-HBMQHKE\\MSSQLSERVER01;Initial Catalog=College;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(mycon))
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@S_ID", S_ID);
                cmd.Parameters.AddWithValue("@Sub_Name", Sub_Name);
                cmd.Parameters.AddWithValue("@Semester", Semester);
                cmd.Parameters.AddWithValue("@marks", marks);
                cmd.ExecuteNonQuery();
            }