CODEWITHSUNDEEP
sqldatabasevbscriptado
VBscripter
VBscripter

Reputation: 63

VBscript and ADO - 3704 Operation is not allowed when the object is closed

This function inserts a row into a SQL database and needs to return the identity number created:

Function WriteDatabase(backupTypeID, numImages, folderSize, success, errorMessage, strLogFileName)

    On Error Resume Next
    err.clear
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    objConnection.Open "Provider=SQLOLEDB;Data Source=x.x.x.x;Initial Catalog=DB;User ID=sa;Password=xxxxxx"
    sqlquery = "INSERT INTO tblImageCopies (BackupCopyDate, BackupCopyTypeID, NumImages, ImagesFolderSize, Success, ErrorMessage) VALUES (GETDATE(), " & backupTypeID & ", " & numImages & ", " & folderSize & ", " & success & ", " & errorMessage & "); SELECT scope_identity() AS ImageCopyID;" 
    objRecordSet.Open sqlquery,objConnection
    objRecordSet.MoveFirst
    WriteDatabase = objRecordSet("ImageCopyID")
    objRecordSet.Close
    objConnection.Close
    If err.number <> 0 Then
        WriteLog "Error writing to the EHN database - " & err.number & " " & err.description, strLogFileName
    End If

End Function

It successfully inserts the row, but I get the error message '3704 Operation is not allowed when the object is closed.' when it tries to return the identity number in the record set. When I execute this sql query directly on the server, it works. Anyone able to help?

Upvotes: 3

Views: 19299

Answers (3)

Michael Melio
Michael Melio

Reputation: 11

I received the exact error, and managed to track it down to a PRINT statement that I had left in my SQL Server stored procedure. Once I commented the PRINT statement, the Recordset returned the results without error.

Upvotes: 1

Olya
Olya

Reputation: 71

Use "SET NOCOUNT ON; " at the beginning of your query

Upvotes: 6

Brian
Brian

Reputation: 38025

I do the same thing (very similar anyway). I believe that there are two sets of results coming back, one for the INSERT and then another for the SELECT. Try calling objRecordSet.NextRecordset().

Upvotes: 5

Related Questions