[thelist] updating a record?

Glen Burnett glen at inception.co.nz
Sat Oct 12 23:50:00 CDT 2002


Jeremy, have you tried using an adLockOptimistic lock type?

Also, you set the RS object to equal an ADODB.Connection, it should be an
ADODB.Recordset.  Your conn variable holds the connection.  I don't think
this is causing your problem though.

You could also do this as a seperate sql statement using the same connection
but no recordset.  See below:


Dim conn
Dim strSql
Dim RS
Dim DBPath

DBPath = Server.MapPath("../../Database/agentszips2.mdb")

Set conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";"

strSql = "SELECT cmacount FROM zip_tbl WHERE zipcode Like '%" &
	Replace(zipcode, "'", "''") & "%' "

Set RS = conn.execute(sqlSql)

'output recordset

RS.Close
Set RS = Nothing

strSql = "Update zip_tbl Set cmacount = (cmacount + 1) Where zipcode Like
'%" & _
	Replace(zipcode, "'", "''") & "%'"

conn.execute(strSql)

conn.close
set conn = nothing


Not sure what the performance difference is between executing two seperate
sql statements versus using the recordset to update but at least this way
you only need a forward only, read only recordset which uses uses the least
resources out of all the recordset types.

Hope this helps,

Glen



-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Jeremy Weiss
Sent: Sunday, 13 October 2002 12:38 p.m.
To: Evolt
Subject: [thelist] updating a record?


I've got a field in one of my tables that I want to serve as a counter.  I'm
trying to increment it each time the corresponding record is accessed.
Here's the code snip that's doing the update.

	Dim SearchStr
	Dim RS
	Dim cmacount

	SearchStr = "SELECT cmacount FROM zip_tbl WHERE zipcode Like '%" &
Replace(zipcode, "'", "''") & "%' "

	DBPath = Server.MapPath("../../Database/agentszips2.mdb")
	Set RS = Server.CreateObject("ADODB.Connection")
	RS.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";",
adOpenKeyset, adLockPessimistic, adCmdText

	Set RS = conn.execute(SearchStr)

	RS.MoveFirst
	ccount = RS.Fields("cmacount").Value
	RS.Fields("cmacount").Value = ccount + 1

	RS.Update
	RS.Close
	Set RS = Nothing

Now, whenever I run this, I'm told that the
	"Current Recordset does not support updating. This may be a limitation of
the provider, or of the selected locktype."
The line number is gives the one that says...
	RS.Fields("cmacount").Value = ccount + 1

Anyone have any ideas?

TIA,
-jeremy
Whatsmyhomevalue.com

--
For unsubscribe and other options, including
the Tip Harvester and archive of thelist go to:
http://lists.evolt.org Workers of the Web, evolt !




More information about the thelist mailing list