Re: using adOpenDynamic

Kevin wrote:
> Hi,
>
> I have an Access2000 db, IIS5.
>
> In an attempt to get back to basics, I am going thru a beginning ASP
> databases book by wroX -- and this piece of code is suppose to work
> according to the book -- and I cannot get it to work -- all I get is
> a blank page -- no errors:
>
>
> <%
> strDBPath = Server.MapPath("/xxx/xx/zzz/ddd.mdb")
> Set cnnSimple = Server.CreateObject("ADODB.Connection")

'with IIS 5+ you no longer need to use the Server.CreateObject. Using
vbscript's CreateObject will improve performance:

Set cnnSimple = CreateObject("ADODB.Connection")

> cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> strDBPath & ";"
>
> dim adCmdStoredProc, adOpenDynamic
> adCmdStoredProc = 4
> adOpenDynamic = 2
>
> Set objCmd = Server.CreateObject("ADODB.Command")
> set objCmd.ActiveConnection = cnnSimple
> objCmd.CommandText = "qryRequests"
> objCmd.CommandType = adCmdStoredProc
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.open objCmd, , adOpenDynamic
>
<snip>
> any thoughts

With Jet, you do not need a Command object. Why do you want a dynamic
cursor? Are you really pllanning for this page to be processing long enough
for changes/deletions by other users to matter? If so, you are creating a
non-scaleable application. An asp page should do its job in less than a few
seconds. Are you sure a default forward-only cursor won't do the job for
you?

Try this:

Set objRS = CreateObject("ADODB.Recordset")
cnnSimple.qryRequests objRS

if not objRS.EOF then
Do While Not objRS.EOF
Response.Write objRS("reqTitle") & _
" <b>--</b> " & objRS("requestor") & "<br>"
objRS.MoveNext
Loop
else
response.write "Recordset was empty"
end if

If you really need an expensive dynamic cursor for some reason, do this:

Set objRS = CreateObject("ADODB.Recordset")
objRS.cursortype=adOpenDynamic
cnnSimple.qryRequests objRS

if not objRS.EOF then
Do While Not objRS.EOF
Response.Write objRS("reqTitle") & _
" <b>--</b> " & objRS("requestor") & "<br>"
objRS.MoveNext
Loop
else
response.write "Recordset was empty"
end if

You may want to look into alternatives for recordset loops:
http://www.aspfaq.com/show.asp?id=2467


Here is some more information about executing saved queries/stored
procedures via ADO/ASP (some of these are redundant):
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.


.

Relevant Pages

  • Re: ADO Performance
    ... sections (this will vary a little depending on cursor location): ... Find out how long it takes to run the query and get the recordset ... place some logic on the server side to reduce the execution time. ... loop with oRS.MoveNext command. ...
    (microsoft.public.vb.general.discussion)
  • Re: problem while creating/acessing temp tables through ADO
    ... Because of that declaration, the recordset never got ... de-referenced between passes through the loop, and, since the connection ... using Execute rather than Open: ... If all you want is a server-side, forward-only cursor, then Execute is ...
    (microsoft.public.data.ado)
  • Re: PLSQL exception handling problem
    ... I have some PLSQL that is looping through a recordset and I am ... I must move the error trapping inside a block within the loop, ... CURSOR cursor1 IS ... for update of dentalempcost NOWAIT; ...
    (comp.databases.oracle.server)
  • Re: Watch Dog Timer
    ... I set the cursor to Client Side cursor. ... >> I need to connect to a remove database via VPN. ... >> I open a recordset and use While Not RecordSet.EOF to get the data ... >> endless loop. ...
    (microsoft.public.vb.enterprise)
  • Re: Active Server Pages and ADO
    ... Try to set CursorLocation property of the recordset to adUseClient and see ... >the cursor type parameters and still nothing. ... > Dim objDC, objRS, sqlInsert ... > Set objRS = Server.CreateObject ...
    (microsoft.public.data.ado)