Solved

Update as/400 recordset using vb script

Posted on 2003-12-11
7
762 Views
Last Modified: 2012-06-27
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
   
0
Comment
Question by:digdug89
7 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 9923998
Have you checked the security on the AS/400 for thet file?
0
 
LVL 14

Expert Comment

by:daveslater
ID: 9926794
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
 
LVL 14

Accepted Solution

by:
daveslater earned 350 total points
ID: 9928562
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:digdug89
ID: 9949527
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
 

Author Comment

by:digdug89
ID: 9949557
Just fyi, this code will allow you you to update the current recordset...
0
 
LVL 14

Expert Comment

by:daveslater
ID: 9949571
Hi
I like it when we get feed back.

Cheers


Dave
0
 

Expert Comment

by:beauzero
ID: 10402938
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IBM AS400 V6R1M0 ftp upload very slow to window machine 6 265
Jobs on iSeries autoanswer by itselfs. 10 175
DB2 return first match 3 122
iSeries Save via Virtual Tape to Nas 2 50
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question