Error: Rowset position cannot be restarted. Using adOpenDynamic or adOpenstatic

getting this error regardless of which type of recordset I use.

If I comment
set adoRSProducts = Server.CreateObject ("ADODB.recordset")
in the function GetProductTotals(iProductId,iDate,iTime), I get
'error Object required'

Here's a sample of my code
any comments welcome!

dim adoRSProducts
set adoRSProducts = Server.CreateObject ("ADODB.recordset")

 if instr(1,lcase(adoRS("vDescription")),"gown") > 0 then
 set adoRSProducts = GetProductTotals(iproductid,iDate,iTime)

if adoRSProducts.eof <> true then
 end if
 set adoRSProducts = nothing

elseif instr(1,lcase(adoRS("vDescription")),"hat") > 0 then
  set adoRSProducts = GetProductTotals(iproductid,iDate,iTime)
  if adoRSProducts.eof <> true then

  end if
 set adoRSProducts = nothing
end if

function GetProductTotals(iProductId,iDate,iTime)

set adoRSProducts = Server.CreateObject ("ADODB.recordset")
set adoCmdCustomer = Server.CreateObject ("ADODB.command") 'another error if i comment
set adoCmdCustomer = adoCommand
   fnClearDBParameters adoCmdCustomer
   set adoCmdCustomer.ActiveConnection = adoConnection

   adoCmdCustomer.CommandText = "aspGetProducts"
   adoCmdCustomer.Parameters.Append adoCmdCustomer.CreateParameter("@iProductId", adInteger, adParamInput, , iProductId)
   adoCmdCustomer.Parameters.Append adoCmdCustomer.CreateParameter("@iDate", adInteger, adParamInput, , iDate)
   adoCmdCustomer.Parameters.Append adoCmdCustomer.CreateParameter("@iTime", adInteger, adParamInput, , iTime)

   adoCmdCustomer.CommandType = adCmdStoredProc
   adoCmdCustomer.Prepared = false

      adoRSProducts.Open adoCmdCustomer, , 2
      set GetProductTotals = adoRSProducts
  ' on error resume next
   set adoCmdCustomer.ActiveConnection = nothing ' Disconnect

 end function

function  loopthru(value)
dim iResult
iResult = 0
adoRSProducts.movefirst ' get the error here
do until adoRSProducts.eof
      if instr(1,adoRSProducts("vCustom1"),value) > 0 then
            iResult = adoRSProducts("Q")
      end if
'set adoRSProducts = nothing
loopthru = iResult
end function

Who is Participating?
Mohit VijayConnect With a Mentor Commented:
Please check below examples, for how to execute ADO Record Set with ADO Command object. see its examples

-----------------something like----------------
Dim cmd

cmd = Server.CreateObject("ADODB.Command")

Dim RS

RS = Server.CreateObject("ADODB.Recordset")

With cmd

.ActiveConnection = Con

.CommandText = "sp_Hello"

.CommandType = 4

.Parameters.Append.CreateParameter("@ID", adInteger, adParamInput, , ID)

RS = .Execute

End With

If Not RS.EOF And Not RS.BOF Then etc....
Mohit VijayCommented:
you can not use adoRSProducts.movefirst with opendynamic and Openstatic cursor type.
Mohit VijayCommented:
Sorry, above should not be the reason, I mislead the question.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Mohit VijayCommented:
Actually you did not set the cursor type when you open the record set, so it took it as "adOpenForwardOnly", that why you are getting error with move first.

Mohit VijayCommented:
please check below article and see table that is describing about"What cursors are supported by Jet?"
lgreallyAuthor Commented:
thanks vjsoft

 adoRSProducts.Open adoCmdCustomer, , 2 and  adoRSProducts.Open adoCmdCustomer, , 4
The 2 and 4 relates to the cursor no?

I understand it as recordset.Open Source, ActiveConnection, CursorType, LockType, Options ?


Mohit VijayCommented:
something like?

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider=SQLOLEDB;Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;Initial Catalog=Northwind;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic


Wscript.Echo objRecordSet.RecordCount
lgreallyAuthor Commented:

I've changed to       adoRSProducts.Open adoCmdCustomer, ,  adOpenStatic, adLockOptimistic
and I go back to the other error on that line:

Microsoft VBScript runtime  error '800a01a8'

Object required
Mohit VijayCommented:
Your charnge is not looks good to me. Sorry!

You can take reff. from below url
lgreallyAuthor Commented:

Have been away for the weekend.

I cant see what the difference is, i.e. why my code is wrong:
 adoRSProducts.Open adoCmdCustomer, ,  adOpenStatic, adLockOptimistic

adoCmdCustomer has the correct stored proc parameters from what I can see.

What do you think is wrong?

Mohit VijayCommented:
you missed the connection here

right syntax is:

objRecordset.Open source,actconn,cursortyp,locktyp,opt  (Reference:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.