probleme de connexion

bonjour a tous, bon j'ai un prob qui me bloque depuis une journée, le probleme est que je veux Inserer des données que j'ai dans ma base données quand j'ajoute un champs dans une autre table,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
connect.Open();
            string data = "SELECT id_matiere FROM[Matiere] WHERE nom_matiere='"+matiere.Text+"'";
            SqlCommand command1 = new SqlCommand(data, connect);
            SqlDataReader read1 = command1.ExecuteReader();
            read1.Read();
            int id_matiere = (int) read1["id_matiere"];
            connect.Close();
            //////////
            connect.Open();
            String Request = "SELECT * FROM[Etudiant] WHERE id_dep='" + dep.SelectedValue + "' AND id_section='" + section.SelectedValue + "'";
            //SqlCommand command = new SqlCommand(Request, connect);
SqlCommand command = new SqlCommand(Request, connect);
            SqlDataReader read = command.ExecuteReader();
            connect.Close();
            connect.Open();
            while (read1.Read())
            {
                string requete = "INSERT INTO[Note](id_etd,id_matiere,id_section,id_dep)";
                requete += "VALUES('" + read["id_etd"] + "','" + id_matiere + "','" + read["id_section"] + "','" + read["id_dep"] + "')";
                //connect.Open();
                SqlCommand Mycommand = new SqlCommand(Request, connect);
                Mycommand.ExecuteNonQuery();
            }
            connect.Close();

mais le probleme c'est que achaque fois il me gene par un message de fermeture et ouverture de la base de données.
merci pour vos aides.

public void AjoutDansTableNote(TextBox matiere , DropDownList dep, DropDownList section, SqlConnection connect) { //////////// connect.Open(); string data = "SELECT id_matiere FROM[Matiere] WHERE nom_matiere='"+matiere.Text+"'"; SqlCommand command1 = new SqlCommand(data, connect); SqlDataReader read1 = command1.ExecuteReader(); read1.Read(); int id_matiere = (int) read1["id_matiere"]; connect.Close(); ////////// SqlCommand cmd = connect.CreateCommand(); cmd.CommandText = "SELECT * FROM[Etudiant] WHERE id_dep='" + dep.SelectedValue + "' AND id_section='" + section.SelectedValue + "'"; cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); //connect.Dispose(); connect.Close(); //////////////// connect.Open(); foreach (DataRow read in ds.Tables[0].Rows) { string requete = "INSERT INTO[Note](id_etd,id_matiere,id_section,id_dep)"; requete += "VALUES('" + read["id_etd"] + "','" + id_matiere + "','" + read["id_section"] + "','" + read["id_dep"] + "')"; SqlCommand Mycommand = new SqlCommand(requete, connect); Mycommand.ExecuteNonQuery(); } connect.Close(); }