How to return a non-read only recordset from AS400.

ASKER

Yes, ineffect create a disconnected recordset with the data from the as400.

bob_online

after executing, try

Set GetRecordSet.ActiveConnection = Nothing

You probably need to switch the cursor to the client before opening the recordset.

ASKER

Thanks, Bob_online but not yet.

I tried both:

GetRecordSet.CursorLocation = adUseClient
and
GetRecordset.curSorLocation = adUseServer

Along with:

GetRecordSet.LockType = adLockOptimistic
and
GetRecordSet.LockType = adLockBatchOptimistic

I do not get any error executing through the code. But using all of the above examples I get the same error when I try to change any value in the recordset.

[
Runtime error "3251"

Current Recordset doe snot support updating. This may be a limitiation of the Provider, or the selected locktype.
]

Note: V5R1 is suppose to now support the disconnected recordset.

ASKER

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

A recordset returned by a stored proc can only be read-only. The SP is not an updatable data source.

>>A recordset returned by a stored proc can only be read-only<<

That is true only if you use the Execute method. If you use the Recordset's Open method then you can get an updateable recordset (providing the provider supports it)

Anthony

but how can a SP know which table to update?

As an example, I will use the NorthWind database in SQL Server and the sample Stored Procedure CustOrdersOrders. In case you don;t have it installed this is what the sp looks like:

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

The following code, will give you an updateable query:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Password="""";Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=HOME-SERVER"
cn.Open

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "CustOrdersOrders"
.CommandType = adCmdStoredProc
.Parameters.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append cmd.CreateParameter("@CustomerID", adVarWChar, adParamInput, 5, "FRANK")
End With

Set rs = New ADODB.Recordset
With rs
Set .Source = cmd
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Options:=adCmdStoredProc
Debug.Print rs.Supports(adUpdate)
.Close
End With
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing

Again, the SQL Server provider supports this, quite frankly I do not know if Client Access Express provider does as well.

Anthony

Your SP is simply a SELECT statement. What if the SP is a cursor and do some process in it?

It does not make any difference, it was simply an example. Try it for yourself and you will see what I mean.

Anthony

Eric,

Here is an article from MSDN that makes your point:
PRB: Multiple Statements in Stored Procedure Causes Forward-Only/Read-Only Recordsets (Q246636)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246636&SD=MSKB&

Anthony

So to sumarize, if your Stored Procedure consists of a "simple" Select statement and (IMHO this is the biggest hurdle you face) your provider supports it, you should be able to get an updateable query.

Posting your SP from the AS400 may help.

For those reading this and wondering why you would implement a Stored Procedure with a select statement instead of doing a simple select, the answer is simple and can be summarize in one word: Permissions. Most shops do not (and should not) give you direct Select permission against the table (in fact users should not have any permissions of any type directly on the tables), but rather all actions should be implemented through Stored Procedures and User Defined Functions.

Anthony

From the link you provided:

--Start--
CAUSE
SQL Server cannot provide schema information necessary for updates if the stored procedure contains more than just a SELECT statement.

RESOLUTION
You can use client-side cursors in order to get a read-only Static cursor, which allows scrolling and bookmarks. You will have to manage updates yourself.
--End--

It is written that you will have to manage updates yourself if the SP contains more than a SELECT. I have oftem see RPG400 code called as SP (which are more than simple SELECT) from VB. This is why I told that it is not an updatable datasource.

darold_rudolph, what kind of SP do you have?

ASKER

Thanks for the great discussion.

Here is the results

I replaced my:
Set GetRecordSet = ADOcmd.Execute
statement with:
GetRecordSet.Open ADOcmd, ADOcn
with no other changes.

I got an error message:
Runtime error '3707'
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

The SP is much more complicated than a simple SELECT, it contains full RPG code with if/thens.

I am currently copying the GetRecordset, field by field, record by record to new blank recordset, which allows me to edit the data in the recordset. It is a slow process.

Also, the rumors were that a new feature of V5R1 would allow disconnected recordsets.

Thanks,
Darold

ASKER

You are absolutely right on both counts, this line:
GetRecordSet.Open ADOcmd, ADOcn

Should have been, just:
GetRecordSet.Open ADOcmd

But also as you have discovered, this still may not be feasable in your case.

Anthony

ASKER

Getting close?

I replaced:
Set GetRecordSet = New ADODB.Recordset
GetRecordSet.CursorLocation = adUseServer
GetRecordSet.CursorType = adOpenKeyset
GetRecordSet.LockType = adLockBatchOptimistic
Set GetRecordSet = ADOcmd.Execute

with:

Set GetRecordSet = New ADODB.Recordset
GetRecordSet.CursorLocation = adUseClient
GetRecordSet.CursorType = adOpenStatic
GetRecordSet.LockType = adLockOptimistic
GetRecordset.Open ADOcmd

I get the recordset and when I use the command:
Call GetRecordset.collect("FIELD1") = "TEST"
"FIELD1" is the named field and is 4 characters
it passes but the next statement:
GetRecordset.movenext
does not. I get the error:
Runtime error '-2147467259 (80004005)'
Insufficient base table information for updating or refreshing.

The statement:
Call GetRecordset.Supports(adUpdate)
returns a true

Therefore it seems to let me write to the buffer of the recordset, but will not let me actually update the recordset.

A clarification on the SP. The RPG program does use if/thens but it also creates a temporary table and uses a SELECT to return the recordsets to VB.

Also, sorry about the double comments. I hit refresh on my screen.

Should this not be:
GetRecordset.("FIELD1").Value = "TEST"
GetRecordset.Update 'Although this is probably implicit in the MoveNext
GetRecordset.MoveNext

In any case, I believe the error "Insufficient base table information for updating or refreshing", is related to the CursorLocation = adUseClient

So I believe you may be in a catch-22 situation.

Anthony

ASKER

Again, thanks for your reponse.

The two are the same since .value is the default
GetRecordset.("FIELD1") = "TEST"
GetRecordset.("FIELD1").value = "TEST"

also, the update is implicit in the .movenext and I do get the same error with the .update as I do with the .movenext.

Having the cursor as adUseServer, returns a blank recordset, but it does have the correct fieldnames.

I would think at V5R1 should have an easy way of creating a disconnected recordsets!

Useless information:
I you watched one of the Discovery channels you would know that it should be catch-18. The publisher already had another book out that year with an "18", therefore the book catch-18 was renamed to catch-22.