CODEWITHSUNDEEP
excelvb6
Somebody
Somebody

Reputation: 2779

operation is not allowed when the object is closed using vb6

I have an excel file which brings the data from a stored procedure, it works perfect, now I've changed the stored procedure by another one with the same parameters that the old one, but I'm getting an error: "operation is not allowed when the object is closed", at the loop: Do While Not rsData.EOF

What in the world is happening here: 

Set dbConnection = New ADODB.Connection
 dbConnection.ConnectionString = connStr
 dbConnection.ConnectionTimeout = 60
 dbConnection.Open

 Set Cmd = New ADODB.Command
 Cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
 Cmd.ActiveConnection = dbConnection
 Dim myrealenddate As Date

 'Create 2 output parameters
 Set pm1 = Cmd.CreateParameter("@DateIni", adDBDate, adParamInput, 15, CDate(Sheet1.TextBoxfechainiG))
 Set pm2 = Cmd.CreateParameter("@DateEnd", adDBDate, adParamInput, 15, CDate(Sheet1.TextBoxfechaendG))

 'Append the output parameters to command object
 Cmd.Parameters.Append pm1
 Cmd.Parameters.Append pm2
 Cmd.CommandText = "spProductionReportByDate"
 'Cmd.CommandText = "sp_Report_Recv_Metrics"

 Set rsData = New ADODB.Recordset
 Set rsData.Source = Cmd
 rsData.Open

 I = 4
 Do While Not rsData.EOF
  I = I + 1
  Sheet1.Range("A" & I).Value = rsData(0)
  Sheet1.Range("B" & I).Value = rsData(1)
  Sheet1.Range("C" & I).Value = rsData(2)
  Sheet1.Range("D" & I).Value = rsData(3)
  Sheet1.Range("E" & I).Value = rsData(4)
  Sheet1.Range("F" & I).Value = rsData(5)
  Sheet1.Range("G" & I).Value = rsData(6)
  Sheet1.Range("H" & I).Value = rsData(7)
  rsData.MoveNext
 Loop
 rsData.Close
 dbConnection.Close

Upvotes: 2

Views: 4857

Answers (1)

Somebody
Somebody

Reputation: 2779

After a long headache, I found the problem. Like I said, it was something related with the SPs, the only difference between the good SP and the bad SP was this line:

SET NOCOUNT ON

My god, that was the error's root cause, once added to the bad SP, it worked!

thanks a lot @Shane Wealti

Upvotes: 6

Related Questions