Trying to update two access tables and tearing my hair out

lorena

lorena

Well-known member
Joined
Oct 23, 2003
Messages
134
Location
Phoenix, Arizona
I have a little online form that accepts an employees 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:
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
 
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.
 
itagraham said:
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.
Click to expand...

I will try that and see if it helps. Thanks
 
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!
 
Back
Top