#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Recordset.CursorType/Recordset.LockType


    My website server was updated from IIS6.0 to IIS7.0, or at least my account was. This action ended my use of MS Access for small database use. I have a chatbox on my site that used Access to store the chat. I have had to create a new table in the forum database to use for the chatbox. It uses MS SQL 2005.

    Site url
    (********)
    Server is:
    Godaddy
    Windows system
    ASP.NET 2.0/2.5/3.0
    MS SQL Version 2005

    My problem is that I can not do a MovePrevious in the dadabase. I can connect to the database and read info from it as coded below:

    <%
    'declare the variables
    Dim Connection
    Dim ConnString
    Dim Recordset
    Dim SQL1
    Dim myMessage
    Dim myName

    'define the connection string, specify database driver
    ConnString="DRIVER={SQL Server};SERVER=****.db.3156765.*****.com;UID=*****;" & _
    "PWD=*****;DATABASE=***** "

    'create an instance of the ADO connection and recordset objects
    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Recordset = Server.CreateObject("ADODB.Recordset")

    'Open the connection to the database
    Connection.Open ConnString

    'declare the SQL statement that will query the database
    SQL1 = "SELECT userName, chatMessage FROM chatData"

    'Set the lock and cursor type
    ' Recordset.CursorLocation = adUseClient
    ' Recordset.CursorType = adOpenDyamic
    ' Recordset.LockType = adLockOptimistic

    Recordset.CursorType = 2
    Recordset.LockType = 3

    'Open the recordset object executing the SQL statement and return records
    Recordset.Open SQL1,Connection
    'Recordset.Open SQL1,Connection,CursorType:=adOpenDyamic,LockType:=adLockOptimistic

    'first of all determine whether there are any records
    If Recordset.EOF Then
    Response.Write("")
    Else
    Do While not Recordset.EOF
    myName = Recordset("userName")
    myMessage = Recordset("chatMessage")
    Response.Write("<font color='red' />")
    Response.Write(myName)
    Response.Write(" - ")
    Response.Write("<font color='black' />")
    Response.Write(myMessage)
    Response.Write("<br>")
    Recordset.MoveNext
    Loop
    End if

    ' Recordset.MoveLast
    ' For i=1 to 25
    ' myName = Recordset("Comments")
    ' myMessage = Recordset("Name")
    ' Response.Write("<font color='red' />")
    ' Response.Write(myName)
    ' Response.Write(" - ")
    ' Response.Write("<font color='black' />")
    ' Response.Write(myMessage)
    ' Response.Write("<br>")
    ' Recordset.MovePrevious
    ' if Recordset.BOF = true then
    ' exit for
    ' end if
    ' Next

    'close the connection and recordset objects to free up resources
    Recordset.Close
    Set Recordset=nothing
    Connection.Close
    Set Connection=nothing
    %>

    Near the bottom there is code (showing as comments) I did use with Access and want to use with MS DQL. As I said I can't make the code work if I try doing a MovePrevious. You can see some things I've tried without success. I'm sure this is a sintax thing, but I can't find it by my knowledge or searching the net.

    You any of you see what I'm doing wrong? Thanks.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Well, Thanks anyway!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    change

    Code:
    Recordset.CursorType = 2
    Recordset.LockType = 3
    to

    Code:
    Recordset.CursorType = 1
    Recordset.LockType = 3

IMN logo majestic logo threadwatch logo seochat tools logo