Re: Multiple-step operation generated errors. Check each status value.


"Karin" <Karin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E87CEFA-E12E-409D-AEF9-11499D6A34EC@xxxxxxxxxxxxxxxx
> I'm working with an old DNA web project where an asp page calls a VB6 COM+
> component which uses ADO to retireve an empty recordset from SQL Server
> 2000
> via a stored procedure. The proc takes several parameters which some
> should
> be returned directly.
>
> Example:
> CREATE PROC MyProc (
> @Param1 int
> ,@Param2 varchar(40)
> ,@Param3 char(2) = 'SE'
> )
> AS
> SELECT a.col1
> ,a.col2
> ,@Param3 AS 'Param3'
> FROM table a
> GO
>
> Function GetForNew () As Variant
> Dim conn As New Connection, rs As New Recordset
> Dim strSQL As String
>
> conn.CursorLocation = adUseClient
> conn.ConnectionString = msConnectString
> conn.CommandTimeOut = miCommandTimeout
> conn.Open
>
> strSQL = "MyProc "
>
> ' Set properties for the resultset object
> rs.CursorType = adOpenKeyset
> rs.LockType = adLockBatchOptimistic
>
> ' Set the resultset object based on the query object
> rs.ActiveConnection = conn
> rs.Open strSQL
>
> ' Return the resultset object to the calling client
> Set GetForNew = rs
>
> ' Disconnect the resultset for now
> Set rs.ActiveConnection = Nothing
>
> ' Closing query and connection object
> conn.Close
> End Function
>
> When the proc is called and a recordset (adoRs) is created the column
> Param3
> has property Attributes set to 112 (adoRs.Fields("Param3").Attributes)
> which
> don't let me set any value to this column...
>
> Public Property Let Param3(ByVal NewValue As Variant)
> adoRs!Param3 = IIf(CVar(NewValue) = Empty, Null, CStr(NewValue))
> End Property
>
> If I change in the proc and puts @Param3 in a temporary table and JOIN
> this
> new table, the Attributes property is set to 120 and I can set a value to
> the
> column! WHY?

Because it's a derived column, it does not represent a field in a table that
could be updated, it is merely a scalar value rendered as output. If it
isn't updatable according to the underlying database engine, ADO will not
allow you to assign its value.

Dumping to a temp table and returning that, otoh, does return updateable
fields. Why it behaves inconsistently I'm uncertain, but I've never had
great luck using ADO with temp tables, so it doesn't surprise me.

Best reccomendations would depend upon what you intend to do with the
recordset?


-Mark



> SELECT @Param3 AS 'Param3'
> INTO #Temp
>
> SELECT a.col1
> ,a.col2
> ,b.Param3 AS 'Param3'
> FROM table a
> JOIN #Temp b ON Param3 = @Param3
>
> But it doesn't work everytime :-(
>
> Please help!
>
>
> Thanks in advance
>
>


.

Relevant Pages

  • Multiple-step operation generated errors. Check each status value.
    ... The proc takes several parameters which some should ... ,@Param3 AS 'Param3' ... rs As New Recordset ... ' Set the resultset object based on the query object ...
    (microsoft.public.data.ado)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)