Can't update a DB2 database on an AS400 with VB

BruceSanford
BruceSanford used Ask the Experts™
on
I need to be able to update records in a DB2 database on a remote AS400 using VB code. My ConnectionString is:

"PROVIDER=MSDASQL.1;EXTENDED PROPERTIES=DRIVER={Client Access ODBC Driver (32-bit)}; SYSTEM=10.0.0.5;CMT=0; DBQ=AMERIFILE8; PASSWORD=BRUCE; USER ID=BRUCEOH; NAM=0; DFT=0; DSP=0; TFT=0; TSP=0; DEC=0; XDYNAMIC=0; RECBLOCK=2; BLOCKSIZE=32; SCROLLABLE=0; TRANSLATE=0; LAZYCLOSE=0; LIBVIEW=0; REMARKS=0; CONNTYPE=0; SORTTYPE=0; LANGUAGEID=ENU; SORTWEIGHT=0; PREFETCH=1; DFTPKGLIB=QGPL; MGDSN=0; INITIAL CATALOG=10.0.0.5;".

Connection CursorLocation is adUseServer.

The recordset opening I use is:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
lJobNo = 39547
sSql = "SELECT * FROM NUBOOK WHERE JOBN = " & lJobNo
Set .ActiveConnection = cn
.Source = sSql
.CursorType = adOpenStatic
.CursorLocation = adUseServer 'adUseClient '
.LockType = adLockOptimistic
.Open Options:=adCmdText
!PRICE = 99
.Update
end with

I need to be able to select 1 record (as shown above) and update a field in it. When the Update is excuted, I get this error message: "[Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0802 - Data conversion or data mapping error.".

The record will not update and the update must be cancelled to close the recordset. The Recordcount and AbsolutePosition show "-1". What is the problem with my update?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is what I use


      ' Connect to AS/400.
        Set g_cnAS400 = New ADODB.Connection
       
        g_cnAS400.ConnectionString = "provider=IBMDA400; data source='youras400name';User Id=yourname;Password=yourpassword"
     
You can't update a static recordset, try using a keyset instead.
Seems to me it should be something like this (assuming the second recordset column is the column you want to update):

With rs
lJobNo = 39547
sSql = "SELECT * FROM NUBOOK WHERE JOBN = " & lJobNo
Set .ActiveConnection = cn
.Source = sSql
.CursorType = adOpenKeyset
.CursorLocation = adUseServer 'adUseClient '
.LockType = adLockOptimistic
.Open Options:=adCmdText
end with

rs.open
rs(1) = 99
rs.Update
rs.close
set rs = nothing

Author

Commented:
I have tried various settings for the Connection string, cursor locations and locktypes. I can use the ADO Update method only if I use a clientside cursor. However, if I want to use a server cursor location and select ONE record and update it,I have to use the SQL 'UPDATE' command. The ADO Update method will not work-I get the error message: "Data conversion or data mapping error".

Why would the ADO Update method fail while the SQL UPDATE command works?
Author of the Year 2009

Commented:
Hi BruceSanford,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

BruceSanford, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial