Solved

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

Posted on 2010-09-08
11
1,284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

617 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