Post reply

How do I get updateable disconnected recordsets from a stored procedure?

  • August 4, 2005 at 3:54 am

    #87077

    I am using VB6 with ADO 2.7.  I have a stored procedure which returns an output parameter and two recordsets.

    I am using a command object to execute the procedure, and once this is done, disconnecting the recordsets.  I would like to have these recordsets both updateable, but I can't find any way of setting the cursor type and lock type etc. for the resulting recordsets.

    If I execute the stored procedure using rst.Open("exec ...") I can't get the return parameter, and the multiple result sets won't work.

    I really want to do the whole lot in a single trip to the server.

    I think a similar question was posted before, but there was no solution.

    Any help would be deeply appreciated.

  • August 4, 2005 at 5:08 am

    #579496

    With some further investigation, I have got the cursor location and lock type set on my disconnected recordsets (by supplying my command object to the recordset.open method), and the "Supports" method tells me that they both support updates and batch updates, however when I try to update the data in any of the fields, I get the following error:

    -2147217887 - Multiple-step operation generated errors. Check each status value.

    I can only seem to get the disconnected updates to work if I use a select statement (ad hoc SQL) to populate the recordset, which I really don't want...

  • August 4, 2005 at 6:15 am

    #579516

    Open the profiler and check wha statement is sent on the server. That'll give you a better idea of the problem. Don't forget that you need to have a primary key and that you can't update data from 2 different tables at the same time... nor can you update a constant value (select 3 as ColName).

  • August 4, 2005 at 6:27 am

    #579522

    Doh!

    You're quite right - the results come from several tables.

    To be honest I never actually wanted to update the data using the ADO recordsets anyway, since all our database updates are themselves done through stored procedures.  It was just supposed to be a convenient way to keep the data and all the changes on the client while the user was editing things, before making the updates using the stored procedures. I've given up with this approach and am storing the changed values elsewhere.

    Thanks for your help.

  • August 4, 2005 at 6:42 am

    #579532

    Alright. Always go with sps .

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply