?
Solved

AddNew or Update with ADO and OLE DB

Posted on 2001-08-27
2
Medium Priority
?
925 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 200 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses

762 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