Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: VB Error:Operation is not allowed when object is closed

    I have a program that connects to an oracle database using an adodc, but I am having problems updating records and deleting records in the database. Searching the database works fine. When you run the program and try to make an update it gives the error about the operation not allowed when the object is closed. I am not sure what the problem is. Here is the code for one of the pages, the commands I am having problems with are delete and update


    Code:
    Dim casenum As Integer, sql, find, insert, update, caseno, typeofproblem, desc, err, _
    status, resolution, solvedby, dated, email, opened, closed, progress As String
    
    Private Sub cmdClosed_Click()
    On Error GoTo RefreshError
    closed = "SELECT * FROM ONCAMPUS_REPORTS WHERE OC_STATUS = 'closed'"
    adc1.RecordSource = closed
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdDel_Click()
    On Error GoTo RefreshError
    sql = "DELETE FROM ONCAMPUS_REPORTS WHERE OC_CASENUM = " & casenum & ""
    adc1.RecordSource = sql
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdExit_Click()
    End
    End Sub
    
    Private Sub cmdFind_Click()
    On Error GoTo RefreshError
    find = "SELECT * FROM ONCAMPUS_REPORTS WHERE OC_CASENUM = " & casenum & ""
    adc1.RecordSource = find
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdGoBack_Click()
    frmEdit.Visible = False
    frm1.Visible = True
    End Sub
    
    Private Sub cmdInProgress_Click()
    On Error GoTo RefreshError
    progress = "SELECT * FROM ONCAMPUS_REPORTS WHERE OC_STATUS = 'IN PROGRESS'"
    adc1.RecordSource = progress
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdInsert_Click()
    On Error GoTo RefreshError
    caseno = Text1.Text
    typeofproblem = Text2.Text
    desc = Text3.Text
    err = Text4.Text
    status = Text5.Text
    resolution = Text6.Text
    solvedby = Text7.Text
    dated = Text8.Text
    email = Text9.Text
    
    insert = "INSERT INTO ONCAMPUS_REPORTS (oc_casenum, oc_problemtype," _
    & "oc_description, oc_error, oc_status, oc_resolution, oc_solvedby, oc_date," _
    & "oc_email)" & "VALUES ('" & caseno & "', '" & typeofproblem & "', '" & desc & "', '" & err & "'," _
    & "'" & status & "', '" & resolution & "', '" & solvedby & "', '" & dated & "', '" & email & "')"
    adc1.RecordSource = insert
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdOpen_Click()
    On Error GoTo RefreshError
    opened = "SELECT * FROM ONCAMPUS_REPORTS WHERE OC_STATUS = 'open'"
    adc1.RecordSource = opened
    adc1.Refresh
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    Private Sub cmdUpdate_Click()
    On Error GoTo RefreshError
    status = Text5.Text
    resolution = Text6.Text
    solvedby = Text7.Text
    
    update = "UPDATE ONCAMPUS_REPORTS SET OC_RESOLUTION = '" & resolution & "', OC_STATUS = '" & status & "', OC_SOLVEDBY = '" & solvedby & "' WHERE OC_CASENUM = " & casenum & ""
    adc1.RecordSource = update
    adc1.Refresh
    
    Exit Sub
    RefreshError:
    MsgBox "Error Number: " & err.Number & " Message is: " & err.Description
    End Sub
    
    
    Private Sub txt1_Change()
    casenum = txt1.Text
    End Sub
    Last edited by loquin; 12-15-08 at 17:24.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: VB Error:Operation is not allowed when object is closed

    Try changing the CommandType to notify that you are not reteriving any records. ie.

    adc1.CommandType= adCmdText + adExecuteNoRecords
    sql = "DELETE FROM ONCAMPUS_REPORTS WHERE OC_CASENUM = " & casenum & ""
    adc1.RecordSource = sql
    adc1.Refresh
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Dec 2008
    Posts
    1
    your database connection should be initialized from a Module
    then within that module there should be a state checking.
    I.E
    public sub checkRS()
    if rs.state = 1 then rs.Close
    end sub

    that rs in the "If line" is for the recordset I.E rs.Open <<

    or if you want a much easier thing then

    use form load and put your connection in it. then a form unload to close the connection.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, with VB6, you are aware that this statement
    Code:
    Dim casenum As Integer, sql, find, insert, update, caseno, typeofproblem, desc, err, _
    status, resolution, solvedby, dated, email, opened, closed, progress As String
    declared casenum as an integer, progress as a string, and ALL the other variables as variants ? If you intended for the other variables to be declared as string, you would need to declare them individually...

    Code:
    Dim casenum As Integer, sql as string, find as string ... , closed as string, progress As String
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •