[dba-SQLServer]Sproc Output to VB?

Hello Guys,

In relation to this, I have asked the list previously, but do you find that
using commands and using OutPut parameters are arkward?

I try to avoid using output params if possible, also, I have been recently
using simple recordsets to run action sp's with nice tidy results.

It means that the parameters are loosely typed, but I am happy enough with
it,

any comments ?


Mark



----- Original Message -----
From: "Mark Rider" <theoden11 at attbi.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Sunday, May 04, 2003 3:35 PM
Subject: RE: [dba-SQLServer]Sproc Output to VB?


> Thanks Jim, that was the missing link!
>
> I finally have what I need to start working on the really big sproc!
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
> (AccessD)
> Sent: Sunday, May 04, 2003 2:12 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer]Sproc Output to VB?
>
>
> Hi Mark:
>
> Have you got the right parameter code to receive the call back
information?
> Something like this:
>
> objCmd.Parameters.Append .CreateParameter("@intCompanyCode", adInteger,
> adParamInput, , lngCompanyCode) objCmd.Parameters.Append
> .CreateParameter("@intRecords", adInteger,
> adParamOutput)
>
> After the SP is executed the information is returned in an array:
>
> varRecordValue = objCmd(1)
>
> HTH
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Mark Rider
> Sent: Saturday, May 03, 2003 7:57 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer]Sproc Output to VB?
>
>
> Thanks for the advice Doris.  I have changed the sproc and it runs in QA -
> although there is no value returned to the grid view.
>
> I still cannot seem to figure out the correct name to read in the
recordset. If
> I look for rs_Fast("@MA") I get a "Item cannot be found in the collection"
error
> and any other name I try comes up with the same.
>
> And I thought datetime storage and retrieval was a bear!
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike and
Doris
> Manning
> Sent: Saturday, May 03, 2003 9:03 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer]Sproc Output to VB?
>
>
> You haven't set @MA to anything so it is returning nothing. Try the
following...
>
> CREATE PROCEDURE [sp_LongMA]
> @inparam datetime,
> @inparam2 datetime,
> @MA money OUTPUT
> AS
> Select @MA = Avg (QQQValue) From tblQQQ
> Where  (QQQTime BETWEEN @inparam AND @inparam2)
> GO
>
> Doris Manning
> Database Administrator
> Hargrove Inc.
> www.hargroveinc.com
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
Rider
> Sent: Saturday, May 03, 2003 9:44 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer]Sproc Output to VB?
>
>
> I have gone stupid looking for the answer to this.  I have a sproc that
> calculates an average of several numbers, and runs fine in QA.  I cannot
figure
> out how to capture the output of the sproc so I can display it in anything
other
> than QA, MSQuery, etc.
>
> The sproc is:
> CREATE PROCEDURE [sp_LongMA]
> @inparam datetime,
> @inparam2 datetime,
> @MA money OUTPUT
> AS
> Select Avg (QQQValue) AS MovingAverage From tblQQQ
> Where  (QQQTime BETWEEN @inparam AND @inparam2)
> GO
>
> I call it from VB6 like so:
>
> ssql_Fast =
> DECLARE @MA1 datetime
> DECLARE @MA2 datetime
> DECLARE @MA money
>
> SET @MA1 = '" & FastTime1 & " '
> SET @MA2='" & FastTime2 & "'
> execute sp_LongMA @MA1, @MA2, @MA = @MA OUTPUT
>
> Again, it runs with no errors, but also with no indication of a returned
value.
>
> How do I write the @MA OUTPUT (the average) to a variable that I can
access thru
> VB6?   I set up an ADODB.Recordset in VB, call the sproc as above and get
no
> errors, but I cannot figure out what to look for in the returned recordset
to
> get the value of @MA.
>
> TIA,
>
> Mark Rider
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>