Solved

AddNew or Update with ADO and OLE DB

Posted on 2001-08-27
2
912 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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
Passing Array in RPGLE Free Format 5 1,250
iSeries (AS400) Spool file issue 17 129
adodb sql multi tables 1 1,239
Answering a message that appears  hundred of times in the QSYSOPR queue. 4 86
This article summaries thoughts and ideas from two years of sustained use. It provides good reasoning to make the jump to Windows 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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