Re: ADO CursorType Problem

From: Val Mazur (group51a_at_hotmail.com)
Date: 09/09/04 

Date: Wed, 8 Sep 2004 21:55:10 -0400

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" <wwells@gmri.net> wrote in message 
news:d456627e.0409081214.6e6fefd0@posting.google.com...
>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:office:office"
> 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>

Relevant Pages

  • Re: ADO CursorType Problem
    ... how is ADO supposed to manage this server-side cursor if you cut off the ... > here is the connection string from the udl file: ... > Sub Application_OnStart ... > If idxStart = 0 Then Exit Sub ...
    (microsoft.public.frontpage)
  • Re: ADO CursorType Problem
    ... how is ADO supposed to manage this server-side cursor if you cut off the ... > here is the connection string from the udl file: ... > Sub Application_OnStart ... > If idxStart = 0 Then Exit Sub ...
    (microsoft.public.data.ado)
  • Re: ADO CursorType Problem
    ... how is ADO supposed to manage this server-side cursor if you cut off the ... > here is the connection string from the udl file: ... > Sub Application_OnStart ... > If idxStart = 0 Then Exit Sub ...
    (microsoft.public.data.oledb)
  • Re: global.asa question (asp pages)
    ... APPLICATION ROOT ROOT). ... > Sub Application_OnStart ... > Vroot = Request.ServerVariables ... > If idxStart = 0 Then Exit Sub ...
    (microsoft.public.frontpage.programming)
  • Re: ADO CursorType Problem
    ... this case recordset should inherit settings for the cursor type from this ... > Sub Application_OnStart ... > ' walk back up VPath until we match VRoot ... > If idxStart = 0 Then Exit Sub ...
    (microsoft.public.data.ado)
Loading