Solved

AddNew or Update with ADO and OLE DB

Posted on 2001-08-27
2
902 Views
Last Modified: 2008-02-20
I'm trying to write records on the AS/400. The code to write to the 400 is at
the end of this message. My problem is that I keep getting the message that the
AddNew method is not supported. I am using Visual Basic 6, Service Pack 5.
Is there something I'm missing?

Project References include:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
IBM AS/400 Client Access Express ActiveX Object Library
Microsoft ActiveX Data Objects 2.5 Library

Components: none beyond the standard set.

CODE: ************************************************************************
Dim strSQL                  As String
Dim cnnAS400                As New ADODB.Connection
Dim cmdAS400                As New ADODB.Command
Dim rsAS400                 As ADODB.Recordset
cnnAS400.Mode = adModeReadWrite
cnnAS400.Open "Provider=IBMDA400;Data Source=AERIS10;"
Set cmdAS400.ActiveConnection = cnnAS400
strSQL = Select * From CO88PX.TIME"
cmdAS400.CommandText = strSQL
cmdAS400.CommandType = adCmdText
Set rsAS400 = cmdAS400.Execute
Dim rsAS400
rsAS400.AddNew              'This is the statement that fails
rsAS400!TCEFYR = Year(Date)
rsAS400!TCEFMN = Month(Date)
rsAS400!TCEFDY = Day(Date)
rsAS400!TCEFHR = Hour(Date)
rsAS400!TCEFMIN = Minute(Date)
rsAS400!TCEFSEC = Second(Date)
rsAS400.Update

END CODE *********************************************************************

PS: I am currently using the following as a work around, but it is quite slow

strSQL = "Insert Into CO88PX.TIME " & _
"(TCEFYR, TCEFMN, TCEFDY, TCEFHR, TCEFMIN, TCEFSEC) " & _
"VALUES (" & _
Year(Date) & ", " & _
Month(Date) & ", " & _
Day(Date) & ", " & _
Hour(Date) & ", " & _
Minute(Date) & ", " & _
Second(Date) & "')"
0
Comment
Question by:bhlang
2 Comments
 
LVL 13

Accepted Solution

by:
samic400 earned 50 total points
ID: 6429716
I may be wrong because you have defined the connection as read/write but I've gotten this type of message before because my recordset is open as read only.

Try this:

Dim cnnAS400                As New ADODB.Connection
Dim rsAS400                 As new ADODB.Recordset

cnnAS400.Open "Provider=IBMDA400;Data Source=AERIS10;"
Set rsAS400.ActiveConnection = cnnAS400
rsAS400.open "time", 3, 3

rsAS400!TCEFYR = Year(Date)
rsAS400!TCEFMN = Month(Date)
rsAS400!TCEFDY = Day(Date)
rsAS400!TCEFHR = Hour(Date)
rsAS400!TCEFMIN = Minute(Date)
rsAS400!TCEFSEC = Second(Date)
rsAS400.addnew

This is the way I do it. I'm sure there are other ways, but this is just a suggestion.

Good luck..... hope it helps out somehow.

0
 

Author Comment

by:bhlang
ID: 6430458
Thanks for your help. It didn't really help me,  but since you were the only one who did help me, I'll give you the points.

The SQL Insert Into method I was using was WAY too slow. So I abandoned it.

Next I tried to use a temporary Microsoft Access Database on the PC and transfer the whole table using an SQL Insert Into statement but it wouldn't work the way I wanted it to.

Finally, I tried this:

I created a TextStream object to let me write to a file. Then I read the records from my source database and wrote them out as a simple string of text (one line per record). That file was copied to the AS/400's QDLS directory where it could be picked up by any AS/400 program. This was significantly faster than the SQL Insert Into method I had been using. I could run the Text File route at least 5 times in the same time that the SQL Insert Into method would transfer the data to the AS/400.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Facing problems with you memory card? Cannot access your memory card? All stored data, images, videos are lost? If these are your questions...than this small article might help you out in retrieving your lost or inaccessible data.
Fine Tune your automatic Updates for Ubuntu / Debian
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

943 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now