Trying to update two access tables and tearing my hair out

[lorena]

I have a little online form that accepts an employee's first and last name and their selection of a class time from a dropdown.

On submit, the form adds a new record to the "employee" table with the employee first and last name and then updates the "classList" table, adding one to the number of students registered to the class selected (since each class can only have 25 students). It is an Access 2003 database.

The problem is the database hangs up. Somewhere in my code, I am not properly closing the connection. Needless to say, when enough people hit the database, it causes big problems on the server.

I really need help!! Thanks in advance.

Here is my code:

If Page.IsValid Then
       Dim objConn As New OleDbConnection(sConnStr)
       Dim strFName, strLName As String
       Dim intID As Integer

       strFName = txtFName.Text
       strLName = txtLName.Text
       intID = ddlClasses.SelectedItem.Value
       Dim clID As New OleDbParameter("@clID", OleDbType.Integer)
       clID.Value = intID

       strSQL = New System.Text.StringBuilder()

       strSQL.Append("INSERT INTO employees (FName, LName, Class_ID) VALUES ('" & txtFName.Text & "','" & txtLName.Text & "', '" & intID & "' )")
       Dim objCommand As New OleDbCommand(strSQL.ToString, objConn)
       objConn.Open()

       objCommand.ExecuteNonQuery()

       strSQL = New System.Text.StringBuilder()
       strSQL.Append("UPDATE classList SET studentCt = studentCt + 1 WHERE Class_ID = " & intID & " ")
       objCommand = New OleDbCommand(strSQL.ToString, objConn)
       lblTest.Text = strSQL.ToString
       objCommand.ExecuteNonQuery()
       objConn.Close()
       objConn = Nothing

       lblThanks.Visible = True
       btnSubmit.Visible = False

     End If

[itagraham]

From my experience of working with asp.net and SQL I personally close the connection immediately after and then execute the next SQL transaction if i have one to execute. In and out in a flash, session per statement.

[lorena]

From my experience of working with asp.net and SQL I personally close the connection immediately after and then execute the next SQL transaction if i have one to execute. In and out in a flash' date=' session per statement.[/quote']

 

I will try that and see if it helps. Thanks

[itagraham]

PS. If you went down this route i would start to thing about creating a simple engine that recieved SQL statents and execute them in Access. Like what you have down in you code only pulled out and out in a public sub!