Problem with record paging an Access DB...

Results 1 to 2 of 2

Thread: Problem with record paging an Access DB...

  1. #1
    Join Date
    Dec 1969

    Default Problem with record paging an Access DB...

    The code works find when I am working w/ SQL DB, but I am getting an error when I used the same code w/ an Access DB. The error is:<BR><BR>"ADODB.Recordset error &#039;800a0cb3&#039; <BR>The operation requested by the application is not supported by the provider. <BR>/jobs/queryResults2.asp, line 150 "<BR><BR>line 150 of my code is the following line:<BR><BR>RsOpsApps.absolutepage=mypage<BR><BR> Here is my code:<BR><BR>...<BR>Set CM = Server.createObject("ADODB.command")<BR>CM.ActiveC onnection = "jobs"<BR>rsOpsApps.cursorlocation=aduseclient<BR> rsOpsApps.cachesize=5<BR>SQL= "Select DISTINCT * FROM TABLE"<BR>CM.CommandText = SQL<BR>response.write cm.commandtext<BR>CM.CommandType = 1 Set RsOpsApps = Cm.Execute<BR><BR>mypage=request("whichpage")<BR>I f mypage="" then<BR>mypage=1<BR>end if<BR>mypagesize=request("pagesize")<BR>If mypagesize="" then<BR>mypagesize=20<BR>end if<BR>mySQL=request("SQLquery")<BR>IF mySQL="" THEN<BR>mySQL=CM.CommandText<BR>END IF<BR><BR>If RsOpsApps.BOF or RsOpsApps.EOF Then<BR>...No records message<BR>Else<BR>RsOpsApps.movefirst<BR>RsOpsApp s.pagesize=mypagesize<BR>maxpages=cint(RsOpsApps.p agecount)<BR>maxrecs=cint(RsOpsApps.pagesize)<BR>R sOpsApps.absolutepage=mypage<BR>howmanyrecs=0<BR>h owmanyfields=RsOpsApps.fields.count -1<BR><BR>Start table and iterate through records...<BR><BR>Thanks.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Wrong cursor type...

    When you use<BR> Set RsOpsApps = Cm.Execute <BR>you automatically get adOpenForwardOnly as your cursor type.<BR><BR>And, incidentally, you get a *NEW* RecordSet object, so any changes you made to RsOpsApps before that point are history. Gone. Lost. Along with the object that RsOpsApps *was* pointing to.<BR><BR>If you want to random access into the records, you need to use a cursor type that supports that.<BR><BR>So learn the simple and easy way to do this.<BR><BR>Dump the command object, completely, and do:<BR><BR> SQL= "Select DISTINCT * FROM TABLE" &#039; See below<BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> conn.Open "jobs" &#039; I hope and assume that&#039;s a DSN name<BR><BR> Set RsOpsApps = Server.CreateObject("ADODB.Recordset")<BR> rsOpsApps.cursorlocation = aduseclient<BR> rsOpsApps.cachesize = 5 &#039; I don&#039;t think this actually does anything<BR> rsOpsApps.PageSize = MyPageSize<BR> rsOpsApps.MaxRecords = MyPageSize &#039; I think these 2 do what you are after<BR> rsOpsApps.Open SQL, conn, adOpenStatic<BR><BR>And *please* don&#039;t use MoveFirst right after opening a recordset. It is *GUARANTEED* that you are already there.<BR><BR>Oh, okay, it&#039;s harmless. But it&#039;s silly.<BR><BR>And *now* you can play your paging games.<BR><BR>********************<BR><BR>Do you understand what SELECT DISTINCT * does?<BR><BR>It *only* eliminates duplicate records if EACH AND EVERY FIELD in the pair of records is identical. It&#039;s an expensive operation to ask for if that&#039;s not what you need! <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts