Dynamic Recordsets

[JackD4ME]

I have a simple script that I have used more times than I can count to connect to a database. I now need a recordset that is dynamic instead of forward only. I have tried many different syntaxes to no avail. Does anyone have an idea of how to configure this code to be a dynamic set?

Set xConn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("db.mdb")
xConn.Open DSNtemp

 

[Sheco]

Check out the ADO Recordset object instead of the Connection object.

The Open method of the Recordset has optional input parameters that allow you to set the CursorType and LockType.

Linky:

http://www.w3schools.com/ado/met_rs_open.asp

 

[JackD4ME]

Ok, I have set this up to open a recordset but it still returns -1 for a recordcount. I tried a number of syntaxes on this thing too. What do you think?

set rs = Server.CreateObject("ADODB.recordset")
rs.Open sTr,xConn,,2
cnt=rs.recordcount

 

[Sheco]

It looks like the LockType is set to Pessimistic but that the default CursorType (ie: ForwardOnly) is still used.

CursorType is the 3rd parameter, LockType is the 4th.

Removing one comma would set the CursorType to Dynamic and use the default LockType:[tt]
rs.Open sTr,xConn,2[/tt]

 

[JackD4ME]

That doesnt work either. Still returns -1.

 

[Sheco]

Did you try setting the CursorLocation property to client?

See this:

http://www.w3schools.com/ado/prop_cursorlocation.asp

 

[PaulBricker]

I don't belive this is a dynamic cursor, but it will return a recordcount.

rs.Open sTr,xConn,3,3

Paul

 

[PaulBricker]

Just a little follow-up, ms has this kb article that indicates if you are using ADO 2.0 the Dynamic cursor type will not return the recordcount. Apparently that was not the case with ADO 1.5
Here is a link to that article

http://support.microsoft.com/kb/194973

Paul

 

[damber]

An easy (and more efficient) alternative would be to use GetRows, and then measure the array size using Ubound.

set oRS... etc...

aDataSet = oRS.GetRows()
iRecordCount = ubound(aDataSet,2) + 1

response.write("Total No. of Records = " & iRecordCount & "<br />")

for iRow=lbound(aDataSet,2) to ubound(aDataSet,2)
   for iField=lbound(aDataSet,1) to ubound(aDataset,1)
      response.write("Row " & iRow & ", Column " & iField & " = " & aDataSet(iField, iRow) & "<br />"
   next
next

A smile is worth a thousand kind words. So smile, it's easy!

 

[Tarwn]

I've never understood why you would need a dynamic recordset from a web application. Dynamic recordsets are designed to only query the database i chunks and pull back the data a little at a time, basically waiting until the absolute last second to give you data in case it changed. With a web application all your processing is happening in one go, unlike an application, so unless you database is changing very, very quickly there is almost no use to a dynamic recordset (in my opinion). Add in the case where you use GetRows and you basically could have just used a static recordset.

On recordcount: Dynamic and Forward-Only recordsets do not support record counts. Forward-Only because it is forward-only and doesn't pre-scan the data. Dynamic because the system doesn't know how many rcords it will have until after you have asked for the last one in a loop (since it is pulling them back in chunks). Static and Key (er..Keyset, key-something) recordsets do have record counts because either all of the records are pulled back (static) or all of the keys are pulled back (key-one). The key-one that I obviously cannot remember the name of is a cross between static and dynamic. It pulls back just the indexes or keys for a query then goes back and selects chunks at a time in case anything changed. Again, this is in case the data has changed between your initial query and using the recordset (and you don't care about new records) and to me is fairly useless for a web application.

-T

 

[JackD4ME]

What I need to do is create a page that reads and displays hundreds of records. I dont want a page that is too long to scroll so I am going to use a recordcount to display 50 (or whatever) at a time and page through the recordset.

Rather than continually looping through it with a movenext I want to jump to a specific record number in the count.

 

[PaulBricker]

The static cursor should be able to do that for you.

rs.Open sTr,xConn,3,3


I use it to view from one to hundreds of records on a page depending on input. It will return the recordcount as well.


Paul

 

[damber]

1. If you're iterating over lots of rows, then use getrows (or even getstring if you have simple formatting needs), it will be much faster. You can also make it get rows from a starting position for a set number of rows (i.e. paging):

http://www.w3schools.com/ado/met_rs_getrows.asp

2. If you want to do paging and decide server side paging is more appropriate for your needs, then I'd recommend doing the paging in the database server - i.e. ask the database for page x of your dataset. it is after all a data management task rather than a presentation task.

for example:

http://www.sqlteam.com/item.asp?ItemID=26455

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

There's no point moving data about where it isn't needed, and usually no need to read through it more than once.

just try the different options, you should soon see the efficiency benefits..

A smile is worth a thousand kind words. So smile, it's easy!