Solved

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

Posted on 2010-09-08
11
1,268 Views
Last Modified: 2012-05-10
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
  loopthru(Custom)
 end if
 adoRSProducts.Close
 set adoRSProducts = nothing

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

  end if
  adoRSProducts.Close
 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
adoRSProducts.movenext
loop
'adoRSProducts.Close
'set adoRSProducts = nothing
loopthru = iResult
end function

thanks
0
Comment
Question by:lgreally
  • 8
  • 3
11 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634130
you can not use adoRSProducts.movefirst with opendynamic and Openstatic cursor type.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634149
Sorry, above should not be the reason, I mislead the question.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634163
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.

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634170
please check below article and see table that is describing about"What cursors are supported by Jet?"

http://www.adopenstatic.com/faq/jetcursortypes.asp
0
 

Author Comment

by:lgreally
ID: 33634196
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 ?

Cheers

0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634230
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

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount
0
 

Author Comment

by:lgreally
ID: 33634269
Thanks

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
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33634276
Your charnge is not looks good to me. Sorry!

You can take reff. from below url

http://www.w3schools.com/ADO/met_rs_open.asp
0
 

Author Comment

by:lgreally
ID: 33659187
Hi VJ

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?

Cheers
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33659324
you missed the connection here

right syntax is:

objRecordset.Open source,actconn,cursortyp,locktyp,opt  (Reference: http://www.w3schools.com/ADO/met_rs_open.asp)
0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 125 total points
ID: 33659353
Please check below examples, for how to execute ADO Record Set with ADO Command object. see its examples

http://www.freevbcode.com/ShowCode.Asp?ID=3687
http://support.sas.com/documentation/tools/oledb/rr_sqliomshare.htm

-----------------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....
-----------------------------------
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question