Current Recordset does not support updating

[PaulHInstincticve]

I was using ASP to update a VFP table using ODBC drivers and everything worked fine until I upgrade to use OLE DB VFP drivers instead. Now I receiving the error message at the addnew command

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype

I am writing my updates to a buffered table and then flushing the table to rather than using a simpler INSERT INTO command because I am using a memo field that needs more than 255 characters writing to it. (This was a limitation in ODBC which I hoped had been overcome in OLE DB but this does not seem to be the case from my desks and I have not found anyone on the boards to suggest otherwise yet).

The code I am using is as follows

Set topicsrs = Server.CreateObject( "ADOR.Recordset" )
topicsrs.ActiveConnection = Con
topicsRs.cursortype = 1 'adOpenKeyset
topicsRs.cursorlocation = 2 'adUseServer
topicsRs.locktype = 3 'adLockOptimistic
topicsrs.open "select * from mbtopics where ctopicref = '000000'",con
' there will be no records return for this key!

topicsRs.AddNew

'Looping through 254 characters at a time and add the data to Ado Field buffer
dim i, liworkstring, listartat
FOR i = 1 to len(lmmessage) step 254
liStartAt = i
liWorkString = mid(lmmessage, liStartAt, 254)
topicsRs.Fields("mmessage").AppendChunk(liWorkString)
NEXT
topicsRs.UPDATE 'Update the recordset

Note that my knowledge of ADO etc is minimal, I am predominantly a VFP programmer on the desktop not a web programmer so assume nothing about my web experience! I was using

insert into mbtopics (mmessage) values (lmmessage)

quite happily until I hit the 254 character limit in ODBC and replaced with the code above when I posted my original problem in here and it was suggested to do it like that to overcome the problem. It overcame the problem so I did not worry about questions such as 'what is ADOR?', 'what are static cursors?' etc, it just worked but unfortunately now under OLE DB it fails.

Can anybody help to either get the code above working or even replace it with something better under OLD DB? Many thanks

Paul

 

[Olaf Doschke]

As far as I read an ADOR.Recordset is quite the same as an ADODB.Recordset, but has less options, less properties and methods compared to an ADODB.Recordset. So that might help.

I see a problem with using cursorlocation = 2 'adUseServer and making that work with a record not existing. I'd rather take a look at the examples on using the OLE DB Provider that come with it. These start on a ADODB.Connection and use an AdoDB.command to execute an insert with a parameter (untested code):

' you already have con, I assume that is:
'Set Con = Server.CreateObject("ADODB.Connection")

Set Cmd = Server.CreateObject("ADODB.Command")

Cmd.Activeconnction = Con
Cmd.CommandType = adCmdText
Cmd.CommandText = "INSERT INTO mbtopics (mmessage) values (?)"
Param1 = Cmd.CreateParameter('message', adLongVarChar, adParamInput, , lmmessage)
Cmd.Parameters.Append(Param1)
Cmd.Execute()

Bye, Olaf.

 

[PaulHInstincticve]

Olaf,

Thanks for that. Just the business. Struggled a bit getting the syntax precise but you set me on the right track and I got it to work with the following code

Set Con = Server.CreateObject( "ADODB.Connection" )
con.OPEN("Provider=vfpoledb.1;Data Source=e:\myfolder\;Collating Sequence=general")
Set cmd = Server.CreateObject( "ADODB.Command" )
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = "insert into test (mmessage) values (?)"
set Param1 = cmd.CreateParameter("message", adLongVarChar, adParamInput, len(lmmessage) , lmmessage)
cmd.Parameters.Append(Param1)
cmd.Execute()

A couple of subtle changes to your code in that I was forced to put double quotes on the parameter name, I had to prefix param1 with 'set' and I had to pass the length of the memo field. Much easier than the buffered approach under ODBC though.

Thanks again.

Paul