December 14th, 2012, 09:42 PM
-
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.
December 16th, 2012, 12:26 PM
-
December 21st, 2012, 02:28 PM
-
change
Code:
Recordset.CursorType = 2
Recordset.LockType = 3
to
Code:
Recordset.CursorType = 1
Recordset.LockType = 3