Solved

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

Posted on 2010-09-08
11
1,254 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
Comment Utility
you can not use adoRSProducts.movefirst with opendynamic and Openstatic cursor type.
0
 
LVL 8

Expert Comment

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

Expert Comment

by:Mohit Vijay
Comment Utility
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
 
LVL 8

Expert Comment

by:Mohit Vijay
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 8

Expert Comment

by:Mohit Vijay
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now