CODEWITHSUNDEEP
sqlvb6
Forrobodo
Forrobodo

Reputation: 49

Operation not allowed when the object is closed in VB6

I know it's kind of old, but I am developing an application on VB6. I can't undestand why I am getting this error.

DSN = "DatabaseInfo"
    
Dim Prod As Variant, Pal As Variant, Row As Long

If Grid.DataRowCnt > 0 Then
    For Row = 1 To Grid.DataRowCnt
        Grid.GetText 1, Row, Prod 
        Grid.GetText 11, Row, Pal
            
        Query = "SET NOCOUNT ON UPDATE myTable SET Pal='" & Pal & "'WHERE RTRIM(Prod)='" & Prod & "'"

        rsReg.Open Query, DSN, adOpenStatic
        Set rsReg= Nothing
    Next Row 
End If
rsReg.Close
MsgBox "You've done it!", vbInformation

I even tried to use the SET NOCOUNT ON, but it still doesn't work. Thank you in advance for any advice.

Upvotes: 0

Views: 127

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

There is no need to introduce the overhead of a RecordSet. A better way of implementing this is with a Command object. You should also use Parameters to make the command more simple and to avoid various security issues:

  DSN = "DatabaseInfo"
      
  Dim Prod As Variant, Pal As Variant, Row As Long
  Dim CM As ADODB.Command
  
  If Grid.DataRowCnt > 0 Then
      For Row = 1 To Grid.DataRowCnt
          Grid.GetText 1, Row, Prod
          Grid.GetText 11, Row, Pal
              
          Set CM = New ADODB.Command
          Set CM.ActiveConnection = "your connection"
          CM.CommandType = adCmdText
          CM.CommandText = "UPDATE myTable SET Pal = ? WHERE RTRIM(Prod) = ?"
          CM.Parameters.Append CM.CreateParameter("@Pal", adVarChar, adParamInput, 50, Pal)
          CM.Parameters.Append CM.CreateParameter("@Prod", adVarChar, adParamInput, 50, Prod)
          CM.Execute , , adExecuteNoRecords
      Next Row
  End If
  
  MsgBox "You've done it!", vbInformation

Upvotes: 2

Related Questions