ADO CursorType Problem

W

William

I am having some trouble opening a keyset or static cursor on the
server side.

Here is the ADO code

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType=adLockReadOnly
.ActiveConnection=Nothing
end with
objRS.Open objCmd

this consistently returns an adForwardOnly cursor.

I can return a keyset on the client side, but am trying to avoid that.

I've read about how you will only get the closest cursortype that your
provider supports. We are currently using SQL Server 7 as the
database, SQLOLEDB provider, ADO version 2.7

I checked both the IIS server and the database with the MDAC Component
checker tool and the MDAC version is correct.

I don't really know what to look at next... I am suspecting that
either SQLOLEDB is somehow corrupt, or that my global.asa file is
screwing things up. we have some developers here who like Frontpage,
and it seems to write strange things to the global.asa file.

Thanks!
William

here is the connection string from the udl file:
Provider=SQLOLEDB.1;Password=mypassword;Persist Security
Info=True;User ID=myuserid;Initial Catalog=PK4_test;Data
Source=1.1.1.1;Network Library=DBMSSOCN

Here is global.asa:

<!-- METADATA TYPE="typelib" FILE="C:\program files\common
files\system\ado\msado26.tlb"-->
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("PK4_ConnectionString") = "File
Name=URL=_private/PK4.udl"
FrontPage_UrlVars(0) = "PK4_ConnectionString"
Application("PK4_ConnectionTimeout") = 15
Application("PK4_CommandTimeout") = 30
Application("PK4_CursorLocation") = 3
Application("PK4_RuntimeUserName") = ""
Application("PK4_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
Application.Contents("numOnline") = 0
application("ServerStart") = now
End Sub
Sub Session_OnStart()
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function


sub session_onEnd()
on error resume next
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open Application("PK4_ConnectionString")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
SQL="DELETE FROM WhosOnline WHERE SessionID="&Session.SessionID
objCmd.CommandText = SQL
objCmd.Execute
objConn.Close
Set objConn=Nothing
Set objCmd=Nothing
Set SQL=Nothing
end sub

Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName &
"_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and
not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER")
and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") &
";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" &
oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" &
oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Settings for Active Server Pages</title><html
xmlns:mso="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">PK4=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
 
W

William \(Bill\) Vaughn

Ok, how is ADO supposed to manage this server-side cursor if you cut off the
connection? Setting the ActiveConnection to nothing tells ADO you want a
client-side detached rowset cursor.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

William said:
I am having some trouble opening a keyset or static cursor on the
server side.

Here is the ADO code

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType=adLockReadOnly
.ActiveConnection=Nothing
end with
objRS.Open objCmd

this consistently returns an adForwardOnly cursor.

I can return a keyset on the client side, but am trying to avoid that.

I've read about how you will only get the closest cursortype that your
provider supports. We are currently using SQL Server 7 as the
database, SQLOLEDB provider, ADO version 2.7

I checked both the IIS server and the database with the MDAC Component
checker tool and the MDAC version is correct.

I don't really know what to look at next... I am suspecting that
either SQLOLEDB is somehow corrupt, or that my global.asa file is
screwing things up. we have some developers here who like Frontpage,
and it seems to write strange things to the global.asa file.

Thanks!
William

here is the connection string from the udl file:
Provider=SQLOLEDB.1;Password=mypassword;Persist Security
Info=True;User ID=myuserid;Initial Catalog=PK4_test;Data
Source=1.1.1.1;Network Library=DBMSSOCN

Here is global.asa:

<!-- METADATA TYPE="typelib" FILE="C:\program files\common
files\system\ado\msado26.tlb"-->
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("PK4_ConnectionString") = "File
Name=URL=_private/PK4.udl"
FrontPage_UrlVars(0) = "PK4_ConnectionString"
Application("PK4_ConnectionTimeout") = 15
Application("PK4_CommandTimeout") = 30
Application("PK4_CursorLocation") = 3
Application("PK4_RuntimeUserName") = ""
Application("PK4_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
Application.Contents("numOnline") = 0
application("ServerStart") = now
End Sub
Sub Session_OnStart()
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function


sub session_onEnd()
on error resume next
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open Application("PK4_ConnectionString")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
SQL="DELETE FROM WhosOnline WHERE SessionID="&Session.SessionID
objCmd.CommandText = SQL
objCmd.Execute
objConn.Close
Set objConn=Nothing
Set objCmd=Nothing
Set SQL=Nothing
end sub

Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName &
"_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and
not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER")
and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") &
";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" &
oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" &
oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Settings for Active Server Pages</title><html
xmlns:mso="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">PK4=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
Click to expand...
 
V

Val Mazur

Hi,

I think it happens because you are using Command to open recordset and in
this case recordset should inherit settings for the cursor type from this
command. By default it should be read-only, forward only recordset. For the
test purposes try to specify actual query string instead of command

--
Val Mazur
Microsoft MVP


William said:
I am having some trouble opening a keyset or static cursor on the
server side.

Here is the ADO code

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType=adLockReadOnly
.ActiveConnection=Nothing
end with
objRS.Open objCmd

this consistently returns an adForwardOnly cursor.

I can return a keyset on the client side, but am trying to avoid that.

I've read about how you will only get the closest cursortype that your
provider supports. We are currently using SQL Server 7 as the
database, SQLOLEDB provider, ADO version 2.7

I checked both the IIS server and the database with the MDAC Component
checker tool and the MDAC version is correct.

I don't really know what to look at next... I am suspecting that
either SQLOLEDB is somehow corrupt, or that my global.asa file is
screwing things up. we have some developers here who like Frontpage,
and it seems to write strange things to the global.asa file.

Thanks!
William

here is the connection string from the udl file:
Provider=SQLOLEDB.1;Password=mypassword;Persist Security
Info=True;User ID=myuserid;Initial Catalog=PK4_test;Data
Source=1.1.1.1;Network Library=DBMSSOCN

Here is global.asa:

<!-- METADATA TYPE="typelib" FILE="C:\program files\common
files\system\ado\msado26.tlb"-->
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("PK4_ConnectionString") = "File
Name=URL=_private/PK4.udl"
FrontPage_UrlVars(0) = "PK4_ConnectionString"
Application("PK4_ConnectionTimeout") = 15
Application("PK4_CommandTimeout") = 30
Application("PK4_CursorLocation") = 3
Application("PK4_RuntimeUserName") = ""
Application("PK4_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
Application.Contents("numOnline") = 0
application("ServerStart") = now
End Sub
Sub Session_OnStart()
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function


sub session_onEnd()
on error resume next
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open Application("PK4_ConnectionString")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
SQL="DELETE FROM WhosOnline WHERE SessionID="&Session.SessionID
objCmd.CommandText = SQL
objCmd.Execute
objConn.Close
Set objConn=Nothing
Set objCmd=Nothing
Set SQL=Nothing
end sub

Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName &
"_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and
not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER")
and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") &
";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" &
oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" &
oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Settings for Active Server Pages</title><html
xmlns:mso="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">PK4=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
Click to expand...
 
W

William

OK... here is the code with no command object. I commented out the
..ActiveConnection = Nothing as well since I can't inherit the
connection from the command object now.

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer 'adUseClient
.LockType=adLockReadOnly
'.ActiveConnection=Nothing
end with
objRS.Open "spGetNew " & Session("AccountNumber"),objConn


It's still returning a cursor of type 0 (adForwardOnly).

I am getting pretty stumped on this one...

William



Val Mazur said:
Hi,

I think it happens because you are using Command to open recordset and in
this case recordset should inherit settings for the cursor type from this
command. By default it should be read-only, forward only recordset. For the
test purposes try to specify actual query string instead of command

--
Val Mazur
Microsoft MVP


William said:
I am having some trouble opening a keyset or static cursor on the
server side.

Here is the ADO code

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType=adLockReadOnly
.ActiveConnection=Nothing
end with
objRS.Open objCmd

this consistently returns an adForwardOnly cursor.

I can return a keyset on the client side, but am trying to avoid that.

I've read about how you will only get the closest cursortype that your
provider supports. We are currently using SQL Server 7 as the
database, SQLOLEDB provider, ADO version 2.7

I checked both the IIS server and the database with the MDAC Component
checker tool and the MDAC version is correct.

I don't really know what to look at next... I am suspecting that
either SQLOLEDB is somehow corrupt, or that my global.asa file is
screwing things up. we have some developers here who like Frontpage,
and it seems to write strange things to the global.asa file.

Thanks!
William

here is the connection string from the udl file:
Provider=SQLOLEDB.1;Password=mypassword;Persist Security
Info=True;User ID=myuserid;Initial Catalog=PK4_test;Data
Source=1.1.1.1;Network Library=DBMSSOCN

Here is global.asa:

<!-- METADATA TYPE="typelib" FILE="C:\program files\common
files\system\ado\msado26.tlb"-->
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("PK4_ConnectionString") = "File
Name=URL=_private/PK4.udl"
FrontPage_UrlVars(0) = "PK4_ConnectionString"
Application("PK4_ConnectionTimeout") = 15
Application("PK4_CommandTimeout") = 30
Application("PK4_CursorLocation") = 3
Application("PK4_RuntimeUserName") = ""
Application("PK4_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
Application.Contents("numOnline") = 0
application("ServerStart") = now
End Sub
Sub Session_OnStart()
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function


sub session_onEnd()
on error resume next
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open Application("PK4_ConnectionString")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
SQL="DELETE FROM WhosOnline WHERE SessionID="&Session.SessionID
objCmd.CommandText = SQL
objCmd.Execute
objConn.Close
Set objConn=Nothing
Set objCmd=Nothing
Set SQL=Nothing
end sub

Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName &
"_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and
not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER")
and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") &
";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" &
oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" &
oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Settings for Active Server Pages</title><html
xmlns:mso="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">PK4=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
Click to expand...
Click to expand...
 
W

William \(Bill\) Vaughn

Okay, let's step back a second and try to figure out what you want to do.
First, when you're working with an ASP application, it can't (shouldn't)
maintain server state such as a server-side cursor. That's because when you
close the connection the connection is returned to the pool and flushed
before the next "client" uses it--this destroys the server-side cursor and
any other server state. In order to build a keyset cursor, ADO has to be
able to identify the primary key and that might be your problem, but again,
I don't think you should be trying to create a server-side cursor in the
first place.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

William said:
OK... here is the code with no command object. I commented out the
.ActiveConnection = Nothing as well since I can't inherit the
connection from the command object now.

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer 'adUseClient
.LockType=adLockReadOnly
'.ActiveConnection=Nothing
end with
objRS.Open "spGetNew " & Session("AccountNumber"),objConn


It's still returning a cursor of type 0 (adForwardOnly).

I am getting pretty stumped on this one...

William



Val Mazur said:
Hi,

I think it happens because you are using Command to open recordset and in
this case recordset should inherit settings for the cursor type from this
command. By default it should be read-only, forward only recordset. For
the
test purposes try to specify actual query string instead of command

--
Val Mazur
Microsoft MVP


William said:
I am having some trouble opening a keyset or static cursor on the
server side.

Here is the ADO code

set objRS=Server.CreateObject("ADODB.Recordset")
with objRS
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType=adLockReadOnly
.ActiveConnection=Nothing
end with
objRS.Open objCmd

this consistently returns an adForwardOnly cursor.

I can return a keyset on the client side, but am trying to avoid that.

I've read about how you will only get the closest cursortype that your
provider supports. We are currently using SQL Server 7 as the
database, SQLOLEDB provider, ADO version 2.7

I checked both the IIS server and the database with the MDAC Component
checker tool and the MDAC version is correct.

I don't really know what to look at next... I am suspecting that
either SQLOLEDB is somehow corrupt, or that my global.asa file is
screwing things up. we have some developers here who like Frontpage,
and it seems to write strange things to the global.asa file.

Thanks!
William

here is the connection string from the udl file:
Provider=SQLOLEDB.1;Password=mypassword;Persist Security
Info=True;User ID=myuserid;Initial Catalog=PK4_test;Data
Source=1.1.1.1;Network Library=DBMSSOCN

Here is global.asa:

<!-- METADATA TYPE="typelib" FILE="C:\program files\common
files\system\ado\msado26.tlb"-->
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("PK4_ConnectionString") = "File
Name=URL=_private/PK4.udl"
FrontPage_UrlVars(0) = "PK4_ConnectionString"
Application("PK4_ConnectionTimeout") = 15
Application("PK4_CommandTimeout") = 30
Application("PK4_CursorLocation") = 3
Application("PK4_RuntimeUserName") = ""
Application("PK4_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
Application.Contents("numOnline") = 0
application("ServerStart") = now
End Sub
Sub Session_OnStart()
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function


sub session_onEnd()
on error resume next
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open Application("PK4_ConnectionString")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText
SQL="DELETE FROM WhosOnline WHERE SessionID="&Session.SessionID
objCmd.CommandText = SQL
objCmd.Execute
objConn.Close
Set objConn=Nothing
Set objCmd=Nothing
Set SQL=Nothing
end sub

Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName &
"_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and
not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER")
and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") &
";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" &
oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" &
oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Settings for Active Server Pages</title><html
xmlns:mso="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">PK4=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
Click to expand...
Click to expand...
Click to expand...
 
W

William

What I want to do is fairly simple. I have a fairly large (2000+ row)
recordset that I need to page through 25 records at a time. each new
page will only get the next 25 records from the recordset. I would
like to do this server side using the .AbsolutePage property. My
thinking is that this minimizes the total number of records going
across to the web server.
I don't really need a keyset, I could do this with a static as well,
unfortunately neither of these will work for me server side currently.
The only cursor type I seem to be able to get server side is the
default forwardonly/readonly cursor. My understanding is that this is
usually due to some issue with the provider. My database is SQL Server
2000, so I know that the database supports this. My only other
thinking is that something must be wrong with the SQLOLDB layer that I
am using to access it.

Of course, I could be completely wrong, and that's why I am here.

Let me know what your idea is. Thanks in advance.

William



William \(Bill\) Vaughn said:
Okay, let's step back a second and try to figure out what you want to do.
First, when you're working with an ASP application, it can't (shouldn't)
maintain server state such as a server-side cursor. That's because when you
close the connection the connection is returned to the pool and flushed
before the next "client" uses it--this destroys the server-side cursor and
any other server state. In order to build a keyset cursor, ADO has to be
able to identify the primary key and that might be your problem, but again,
I don't think you should be trying to create a server-side cursor in the
first place.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

news:[email protected]...
Click to expand...
<snip>
 
E

Erland Sommarskog

William said:
What I want to do is fairly simple. I have a fairly large (2000+ row)
recordset that I need to page through 25 records at a time. each new
page will only get the next 25 records from the recordset. I would
like to do this server side using the .AbsolutePage property. My
thinking is that this minimizes the total number of records going
across to the web server.
Click to expand...

Which means that everytime the user presses Next, you need to make a
roundtrip to the server, and in the meanwhile the user can go for
another cup of coffee. Get 2000 rows the web server, and then page
locally. It is not likely that user want to see more than 2000 rows
anyway.

By the way, I hate web sites that gives me only 25 rows at a time.
I don't really need a keyset, I could do this with a static as well,
unfortunately neither of these will work for me server side currently.
The only cursor type I seem to be able to get server side is the
default forwardonly/readonly cursor. My understanding is that this is
usually due to some issue with the provider. My database is SQL Server
2000, so I know that the database supports this. My only other
thinking is that something must be wrong with the SQLOLDB layer that I
am using to access it.
Click to expand...

Did we actually see the query? For a cursor to be a keyset cursor, the
query has to have an identifiable key, or else SQL Server will convert
it to something it can handle. You can forget about the problem being in
SQLOLEDB.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

global.asa 4
VBA Coding Help for Beginner 0
Working outlook via vba code with xlsx - error 0
global.asa question (asp pages) 4
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA Export to PDF 0
Make VBA ComboBox_Change work in 3 sheets 1
Adding table into existing access database via ADO 0

Top