Comparison of several open Recordset methods in ASP

Comparison | recordset

Define conn.asp First
<%
Dim objconn
Dim strconn
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Chr (+) & Server.MapPath ("Data.mdb") & Chr (34)
Set objconn = Server.CreateObject ("Adodb.connection")
objConn.Open strconn
%>

Let's look at several different ways to open recordsets (recordsets)

Mode 1,
Dim sql
Dim objRS

sql = "SELECT * FROM table1"
Set objRS = objConn.Execute (SQL)

This way, by executing SQL, the recordset is returned, and the filtered recordset is relatively simple.
However, the recordset obtained in this way cannot be paged out.

File text1.asp

<% @LANGUAGE = "VBSCRIPT" codepage= "936"%>
<!--#include file= "conn.asp"-->

<%
Dim sql
Dim objRS

sql = "SELECT * FROM table1"
Set objRS = objConn.Execute (SQL)

Objrs.pagesize = 5
Objrs.absolutepage = 1
%>

The result of requesting this page is:

------------------------------------------------------------
Error type:
ADODB. Recordset (0X800A0CB3)
The current recordset does not support bookmarks. This may be a limitation of the provider or the selected cursor type.
/msg/test1.asp, line 12th.

Browser type:
mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1. NET CLR 1.1.4322)

Web page:
Get/msg/test1.asp

Time:
September 21, 2005, 19:58:01
------------------------------------------------------------

Mode 2,
Dim objRS
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.open "Table1", objconn, 1, 2

Records opened in this manner can be paged out.

<%
Dim objRS

Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.open "Table1", objconn, 1, 2

Objrs.pagesize = 5
Objrs.absolutepage = 1
%>

Try it with this, haha, run smoothly.

But in this way open recordsets cannot be sorted

File test2.asp

<% @LANGUAGE = "VBSCRIPT" codepage= "936"%>
<!--#include file= "conn.asp"-->
<%
Dim objRS

Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.open "Table1", objconn, 1, 2

Objrs.sort = "Field1"
%>

The result of requesting this page is:

------------------------------------------------------------
Error type:
ADODB. Recordset (0X800A0CB3)
The current provider does not support the interface required for sorting or filtering.
/msg/test2.asp, line 17th.

Browser type:
mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1. NET CLR 1.1.4322)

Web page:
Get/msg/test2.asp

Time:
September 21, 2005, 20:17:32
------------------------------------------------------------

What do we do?

Ah, that'll do.

<% @LANGUAGE = "VBSCRIPT" codepage= "936"%>
<!--#include file= "conn.asp"-->
<%
Dim objRS

Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.cursorlocation = 3
Objrs.open "Table1", objconn, 1, 2

Objrs.sort = "Field1 desc"
%>

To sum up:
1, Mode 1, you can use SQL statements to easily filter the records you want.
2, Mode 2, the function is more powerful, but more complex.
There are more useful uses in this way. See other reference materials for details.