using adOpenDynamic

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")
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

Do While Not objRS.EOF
Response.Write objRS("reqTitle") & " -- " & objRS("requestor") &
"
"
objRS.MoveNext
Loop

Set objCmd = Nothing
objRS.Close
Set objRS = Nothing
cnnSimple.Close
Set cnnSimple = Nothing

%>

any thoughts

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
>

> 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") & _
" -- " & objRS("requestor") & "
"
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") & _
" -- " & objRS("requestor") & "
"
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%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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.

Re: using adOpenDynamic

Access2000
iis5

this code gives me a jet 80004005 error 'invalid operation' on one access
db, but not on another -- the other page opens fine -- and all I do is swap
out db name and query name.

both storedProcs/queries are simple select statements off of one table

<%
strDBPath = Server.MapPath("/xxx/xx/xx/xx.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath &
";"

'Check for errors
If objConn.Errors.Count > 0 Then
'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error")
'Declare boolean flag for critical errors
Dim blnCriticalError
'Write all errors to the page
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "

"
Response.Write "Description: " & objErr.Description & "

"
Response.Write "Source: " & objErr.Source & "

"
Response.Write "SQLState: " & objErr.SQLState & "

"
Response.Write "NativeError: " & objErr.NativeError & "

"
blnCriticalError = True
End If
Next
'Dereference all objects
Set objErr = Nothing
If blnCriticalError Then
Response.End
End If
End If


dim adCmdStoredProc, adOpenKeyset, adLockOptimistic
adCmdStoredProc = 4
adOpenKeyset = 1
adLockOptimistic = 3


Set objCmd = Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn
objCmd.CommandText = "qryRequests"
objCmd.CommandType = adCmdStoredProc

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open objCmd,,adOpenKeyset,adLockOptimistic

'Check for errors
If objConn.Errors.Count > 0 Then
'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error")
'Write all errors to the page
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "

"
Response.Write "Description: " & objErr.Description & "

"
Response.Write "Source: " & objErr.Source & "

"
Response.Write "SQLState: " & objErr.SQLState & "

"
Response.Write "NativeError: " & objErr.NativeError & "

"
blnCriticalError = True
End If
Next
'Dereference all objects
Set objErr = Nothing
If blnCriticalError Then
Response.End
End If
End If

Do While Not objRS.EOF
Response.Write objRS("reqTitle") & "
"
objRS.MoveNext
Loop

Set objCmd = Nothing
bjRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

%>


"Kevin" wrote in message
news:Om4Ow0YgFHA.2156@TK2MSFTNGP14.phx.gbl...
> 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")
> 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
>
> Do While Not objRS.EOF
> Response.Write objRS("reqTitle") & " -- " & objRS("requestor") &
> "
"
> objRS.MoveNext
> Loop
>
> Set objCmd = Nothing
> objRS.Close
> Set objRS = Nothing
> cnnSimple.Close
> Set cnnSimple = Nothing
>
> %>
>
> any thoughts
>
>

Re: using adOpenDynamic

well, the db in which this code works is an access97 -- and it fails on the
2000 -- so atleast that helps.

"Kevin" wrote in message
news:eWtDctZgFHA.3204@TK2MSFTNGP10.phx.gbl...
> Access2000
> iis5
>
> this code gives me a jet 80004005 error 'invalid operation' on one access
> db, but not on another -- the other page opens fine -- and all I do is
swap
> out db name and query name.
>
> both storedProcs/queries are simple select statements off of one table
>
> <%
> strDBPath = Server.MapPath("/xxx/xx/xx/xx.mdb")
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath &
> ";"
>
> 'Check for errors
> If objConn.Errors.Count > 0 Then
> 'Create an error object to access the ADO errors collection
> Set objErr = Server.CreateObject("ADODB.Error")
> 'Declare boolean flag for critical errors
> Dim blnCriticalError
> 'Write all errors to the page
> For Each objErr In objConn.Errors
> If objErr.Number <> 0 Then
> Response.Write "Number: " & objErr.Number & "

"
> Response.Write "Description: " & objErr.Description & "

"
> Response.Write "Source: " & objErr.Source & "

"
> Response.Write "SQLState: " & objErr.SQLState & "

"
> Response.Write "NativeError: " & objErr.NativeError & "

"
> blnCriticalError = True
> End If
> Next
> 'Dereference all objects
> Set objErr = Nothing
> If blnCriticalError Then
> Response.End
> End If
> End If
>
>
> dim adCmdStoredProc, adOpenKeyset, adLockOptimistic
> adCmdStoredProc = 4
> adOpenKeyset = 1
> adLockOptimistic = 3
>
>
> Set objCmd = Server.CreateObject("ADODB.Command")
> set objCmd.ActiveConnection = objConn
> objCmd.CommandText = "qryRequests"
> objCmd.CommandType = adCmdStoredProc
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.open objCmd,,adOpenKeyset,adLockOptimistic
>
> 'Check for errors
> If objConn.Errors.Count > 0 Then
> 'Create an error object to access the ADO errors collection
> Set objErr = Server.CreateObject("ADODB.Error")
> 'Write all errors to the page
> For Each objErr In objConn.Errors
> If objErr.Number <> 0 Then
> Response.Write "Number: " & objErr.Number & "

"
> Response.Write "Description: " & objErr.Description & "

"
> Response.Write "Source: " & objErr.Source & "

"
> Response.Write "SQLState: " & objErr.SQLState & "

"
> Response.Write "NativeError: " & objErr.NativeError & "

"
> blnCriticalError = True
> End If
> Next
> 'Dereference all objects
> Set objErr = Nothing
> If blnCriticalError Then
> Response.End
> End If
> End If
>
> Do While Not objRS.EOF
> Response.Write objRS("reqTitle") & "
"
> objRS.MoveNext
> Loop
>
> Set objCmd = Nothing
> bjRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing
>
> %>
>
>
> "Kevin" wrote in message
> news:Om4Ow0YgFHA.2156@TK2MSFTNGP14.phx.gbl...
> > 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")
> > 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
> >
> > Do While Not objRS.EOF
> > Response.Write objRS("reqTitle") & " -- " & objRS("requestor") &
> > "
"
> > objRS.MoveNext
> > Loop
> >
> > Set objCmd = Nothing
> > objRS.Close
> > Set objRS = Nothing
> > cnnSimple.Close
> > Set cnnSimple = Nothing
> >
> > %>
> >
> > any thoughts
> >
> >
>
>

Re: using adOpenDynamic

Jet does not support adOpenDynamic cursors. See:
http://www.adopenstatic.com/faq/jetcursortypes.asp
for available cursor types.

Cheers
Ken

--
IIS Blog: www.adopenstatic.com/cs/blogs/ken/
Web: www.adopenstatic.com

"Bob Barrows [MVP]" wrote in message
news:%23o3$plZgFHA.1204@TK2MSFTNGP12.phx.gbl...
:
: If you really need an expensive dynamic cursor for some reason, do this:
:
: Set objRS = CreateObject("ADODB.Recordset")
: objRS.cursortype=adOpenDynamic
: cnnSimple.qryRequests objRS

Re: using adOpenDynamic

Yes, I forgot about this, but it has nothing to do with his problem. Asking
for a more expensive cursor than the provider supports merely results in a
less expensive cursor, not an error message.

I don't know if Kevin just has not seen my response, but he has ignored it,
carrying on a conversation with himself. Hopefully he will try my
suggestions.

Bob Barrows

Ken Schaefer wrote:
> Jet does not support adOpenDynamic cursors. See:
> http://www.adopenstatic.com/faq/jetcursortypes.asp
> for available cursor types.
>
> Cheers
> Ken
>
>
> "Bob Barrows [MVP]" wrote in message
> news:%23o3$plZgFHA.1204@TK2MSFTNGP12.phx.gbl...
>>
>> If you really need an expensive dynamic cursor for some reason, do
>> this:
>>
>> Set objRS = CreateObject("ADODB.Recordset")
>> objRS.cursortype=adOpenDynamic
>> cnnSimple.qryRequests objRS

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"