CODEWITHSUNDEEP
asp.netdatabase
user2110714
user2110714

Reputation:

Why am i getting connection error to database in asp.net?

I am trying the following code:

SqlConnection con = new SqlConnection();
    con.ConnectionString = Userfunctions.GetConnectionString();
    con.Open();
    for (int i = 0; i < studList.Rows.Count; i++)
    {
        //string a = ((DropDownList)studList.Rows[i].FindControl("actionmenu")).SelectedValue;

            string grade = ((DropDownList)studList.Rows[i].FindControl("actionmenu")).SelectedValue;
            string studentID = studList.Rows[i].Cells[1].Text;
            string courseNumber = MyGlobals.selectedCourse.Substring(MyGlobals.selectedCourse.Length-3);
            string courseCode = MyGlobals.selectedCourse.Substring(0, MyGlobals.selectedCourse.Length - 3);
            SqlCommand com = new SqlCommand("update RegisterTable set Grade=@grade where StudentID=@studentID and CourseCode=@courseCode and CourseNumber=@courseNumber", con);
            com.Parameters.AddWithValue("@grade", grade);
            com.Parameters.AddWithValue("@studentID", studentID);
            com.Parameters.AddWithValue("@courseCode", courseCode);
            com.Parameters.AddWithValue("@courseNumber", courseNumber);
            com.ExecuteNonQuery();

            try
            {
                SqlDataAdapter da2 = new SqlDataAdapter(com);

                DataTable dt2 = new DataTable();
                da2.Fill(dt2);
                DataRow dr2 = dt2.Rows[0];

                Course c = new Course(dr2["InstructorID"].ToString(), dr2["CourseCode"].ToString(), dr2["CourseNumber"].ToString(), dr2["CourseName"].ToString(), dr2["Term"].ToString(), dr2["CRN"].ToString(), dr2["Level"].ToString(), dr2["Credit"].ToString(), dr2["Description"].ToString(), dr2["Capacity"].ToString());
                Register reg = new Register(c, MyGlobals.student);

                MyGlobals.student.dropCourse(reg);

            }
            catch (Exception) { }
            con.Close();

Even though i open the database connection, it gives an error saying that ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. Why is that the case can anyone see? Thanks

Upvotes: 1

Views: 56

Answers (2)

Jim
Jim

Reputation: 6881

You're closing the connection in the loop, so it would move on to the second iteration and the connection would be closed.

Upvotes: 0

p.s.w.g
p.s.w.g

Reputation: 149020

You're closing the connection inside the for-loop, so on the second iteration, the connection isn't open. It should look like this:

SqlConnection con = new SqlConnection();
con.ConnectionString = Userfunctions.GetConnectionString();
con.Open();
for (int i = 0; i < studList.Rows.Count; i++)
{
    ...
    SqlCommand com = new SqlCommand(..., con);
    ...
    com.ExecuteNonQuery();

    try
    {
        ...
    }
    catch (Exception) { }
}
con.Close();

Upvotes: 2

Related Questions