Solved

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

Posted on 2010-09-08
11
1,270 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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