ActiveX does not support updating???

[EZEason]

I'm running a vbscript inside DTS that recalculates 200k records in a table. I get an error once I get to the update part that says "Current Recordset does not support updating. May be limitaion of provider, or selected locktype."

Here is my code:

myConn.Open = DB_CONNECT_STRING
strPDP = "SELECT PDP.* FROM PDP WHERE AWord ='12001' ORDER BY IID, RID, AWord, Year"
rstPDP.Open strPDP, myConn, , 3
rstPDP.movefirst

(calculation is done)...

rstPDP.Fields("RawValue") = goRawValue  (errors here)
rstPDP.Fields("AValue") = goAValue
rstPDP.Fields("StdValue").value = goStdValue
rstPDP.update

I can run an update query from a different DTS package. What am I missing?


What doesn't kill you makes you stronger.

 

[gradley]

I believe the default CursorType (after connection parameter) for recordset.Open is forward only. I don't believe this allows updates. Try using a 1 (KeySet) or 2 (Dynamic).

If it is at all possible, try using a SQL statement to perform this update since ADO.Update statements are one of the most memory intense commands you can use.

 

[EZEason]

Thanks for the reply. I had the CursorType set as 2 (Dynamic) at first. I still get the same error. I took it out to tested it, but did not add it back before I posted my code. I have also tried 1. Same error.

If I run a sql statement to update a recored that I'm currently lock to, will I get an error on the update? I have tried an update sql statement and it error occured after about 150k records.

What doesn't kill you makes you stronger.

 

[gradley]

I would suggest you don't lock the record when reading it if you don't have to. Is the table you are updating a SQL Server table? IF so, use an Execute SQL task instead of an Active X script.

I'm not sure what the complexity is for your calculations but you should still be able to handle this with a single UPDATE statement instead of reading 200,000 records and updating them 1 at a time.

Can you give us more details for what you are trying to accomplish?

 

[EZEason]

The calculation is very complex, and used resultes from prior records calculation with a few loops. So it has to be done in VB. The table is a SQL table in the same database, but it is not as simple as using an single update execute.

I have been running it on my PC but want to run it on the Server to save time and free up my computer.

What doesn't kill you makes you stronger.

 

[gradley]

Ok - I haven't given up yet!!!

Your ADO code looks fine from what I can see. Are you saying that the error happens on the first field you are trying to modify? I would have expected it to error out on the rstPDP.update statement.

The only other thing I can think of is the CursorLocation for the recordset. Try setting this to 2 for UserServer prior to opening the recordset.

Ex: rstPDP.CursorLocation = 2

There is nothing else I can come up with at this time.

Good Luck!

 

[yksvaan]

Why are yo doing it inside a DTS package, not so that it would matter. Or .. ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]