WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
ADO DAO
plus
ADSI LDAP CDO
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Ole Exception ADODB Recordset

Question:

Getting the following error. Ole Exception: ADODB.Recordset Operation isnot allowed when the object is closed. I get this error on the myrs.requery line. Everything works fine except when the program goes to reloop. Any ideas? 
  myConn = ObjectOpen('ADODB.Connection')
  myRS = ObjectOpen('ADODB.Recordset')
  myConn.ConnectionString = "Data Source=Mascom_UPS_Datalink"
  myConn.Open

  SQLText = "Select strCustId from tbl_UPS"
  myRS.Open (SQLText, myConn, 0, 4, 1)

  While 1

		If IsKeyDown(@SHIFT) Then Break         ; Hold SHIFT key to break loop.
		
		If myRS.EOF == @FALSE Then    ; Record Found
		  fld_val           = myRS.Fields("strCustID")
		  CustID            = fld_val.Value     ; Assign Table Value to CustId variable
		
		WinActivate ("UPS")
		WinActivate ("Enter")
		Sendkey(CustID)
		SendKey("~")
		SendKey("{f10}")
		timedelay(15)
		
		filename = "c:\track.txt"
		data = FileGet(filename , "")
		
		item = ItemExtractCsv(1,data,0,",")
		item2 = ItemExtractCsv(2,data,0,",")
		item3 = ItemExtractCsv(3,data,0,",")
		item4 = ItemExtractCsv(3,data,0,",")
		
		;SQLText = "UPDATE tbl_ups SET strSatCharge = '%item4%'"
		;myRS.Open (SQLText, myConn, 0, 4, 1)
		
		myRS.Close
		SQLText = "UPDATE tbl_ups SET strTotSHCharge = '%item3%'"
		myRS.Open (SQLText, myConn, 0, 4, 1)
		SQLText = "UPDATE tbl_ups SET strShipCharge = '%item2%'"
		myRS.Open (SQLText, myConn, 0, 4, 1)
		SQLText = "UPDATE tbl_ups SET strTrackN = '%item%'"
		myRS.Open (SQLText, myConn, 0, 4, 1)
		
		TimeDelay(1)
		
		EndIf
		TimeDelay(1)
		myRS.ReQuery

  EndWhile

  myRS.Close
  myConn.Close
  ObjectClose(myConn)
  ObjectClose(myRS)

Answer:

You are issuing UPDATE's by opening a Recordset object multiple times. Consider issuing Updates from a Connection Object or a Command Object; otherwise combine the 3 Updates into a single SQL.....

Maybe try this revised code: 

  myConn = ObjectOpen('ADODB.Connection')
  myRS = ObjectOpen('ADODB.Recordset')
  myConn.ConnectionString = "Data Source=Mascom_UPS_Datalink"
  myConn.Open

  SQLText = "Select strCustId from tbl_UPS"
  myRS.Open (SQLText, myConn, 0, 4, 1)

  While 1

    If IsKeyDown(@SHIFT) Then Break         ; Hold SHIFT key to break loop.

    If myRS.EOF == @FALSE Then    ; Record Found
      fld_val           = myRS.Fields("strCustID")
      CustID            = fld_val.Value     ; Assign Table Value to CustId variable


      WinActivate ("UPS")
      WinActivate ("Enter")
      Sendkey(CustID)
      SendKey("~")
      SendKey("{f10}")
      timedelay(15)

      filename = "c:\track.txt"
      data = FileGet(filename , "")

      item = ItemExtractCsv(1,data,0,",")
      item2 = ItemExtractCsv(2,data,0,",")
      item3 = ItemExtractCsv(3,data,0,",")
      item4 = ItemExtractCsv(3,data,0,",")


      SQLText = StrCat("UPDATE tbl_ups SET strSatCharge = '",item4,"', strTotSHCharge = '",item3,"', strShipCharge = '",item2,"', strTrackN = '",item,"'")
      myConn.Execute(SQLText)


      TimeDelay(1)
  
    EndIf
    TimeDelay(1)
    myRS.ReQuery

  EndWhile

  myRS.Close
  myConn.Close
  ObjectClose(myConn)
  ObjectClose(myRS)

If that doesn't help:

Add DebugTrace(@ON,"C:\wbdebug.txt") to the top of the script and look at the results. Also take a look in the wwwbatch.ini file (Located in your windows directory) and see what's inside it. 

Article ID:   W16081

File Created: 2004:03:30:15:42:46
Last Updated: 2004:03:30:15:42:46