Update as/400 recordset using vb script

I am having a problem getting my recordset to be updateable.  I keep getting an error message say this recordset does not support update, etc.  I have tried using all combinations of the lock cursor type to no prevail. here is a snippet of my code.

set vConn = server.createobject("ADODB.Connection")
 vConn.Open "DSN=mydsn;UID=meuser;PWD=mepwd"
partnumber = "62222"
set vRS = server.createobject("ADODB.Recordset")
set vRS.ActivEConnection = vConn
sql1 = "SELECT * FROM FLELIB.VTPRTM Where PMPRT = '" & partnumber & "'"
    With vRS
         .CursorType = adOpenDynamic
         .locktype = adLockoptimistic
         .CursorLocation = adUseClient
         .CacheSize = 20
         .MaxRecords = 1
           'Open the result
         .Open strSQL, vConn

         'Verify the cursor type used.
         Debug.Print .CursorLocation
         Debug.Print .CursorType
     end with
   
digdug89Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MurpheyApplication ConsultantCommented:
Have you checked the security on the AS/400 for thet file?
0
daveslaterCommented:
Hi
the easiest way is is not to update the record set but to issue an update statement

Set cmd = CreateObject("ADODB.command")
Set cmd.ActiveConnection = vconn
cmd.commandtext = "Update lib.file set field='Update from Pc' where KEY_DB='Value' "
cmd.Execute

Regards

Dave
0
daveslaterCommented:
Hi
just thinking a bit more about how you are tyring to go about this
In RPG if we are using SQL to update we do the following
Declare a cursor
open the cursor for update of felds
UPDATE FILE SET DAVE = 'A' WHERE CURRENT OF CSR    

As you are using ADO this is an SQL interface therefore the above method is the only way you can ahcieve it.

The only other way is to use a static ODBC and open a reccord set using DAO and qualified library / file name

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

digdug89Author Commented:
actually i got the code to work with the following code.  I am showing this to show you. Thanks so much for your response.

sql1 = "SELECT * FROM FLELIB.VTPRTM Where PMPRT = '" & partno & "'"
    With vRS
         .CursorType = 3
         .locktype = 3
         .CacheSize = 20
         .MaxRecords = 2
           'Open the result
         .Open sql1, vConn
       
     End With
0
digdug89Author Commented:
Just fyi, this code will allow you you to update the current recordset...
0
daveslaterCommented:
Hi
I like it when we get feed back.

Cheers


Dave
0
beauzeroCommented:
Go with Dave's suggestion on using the strict "Update" statement and if you can using a stored proc. on the 400 side will greatly (by about a factor of 20) increase the speed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.