|
 |
asp_databases thread: Re: Multiple Recorsets
Message #1 by "Ken Schaefer" <ken@a...> on Fri, 17 May 2002 12:35:09 +1000
|
|
The sproc *is* returning a record - the record just contains two NULL
values. ADO can't differentiate between a "valid" record that contains
non-NULL values and an "invalid" record that contains NULL values because
that's really something determined by your business logic.
You probably need some additional logic in your sproc if you want to
suppress the return of NULL values (eg an IF EXISTS BEGIN END type
construct): see Books Online.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Martin Lee" <access@o...>
Subject: [asp_databases] Re: Multiple Recorsets
: Did some tests on the query and if no records are returned then the
: recordset returns two NULL fields, which are passed into the array if I
use:
:
: If not objRS.EOF then
: arrBilling = objRS.GetRows()
: End If
:
: Martin
:
: At 12:39 PM 5/16/2002 +1000, you wrote:
: >In your case it would be (you are only returning one recordset as far as
I
: >can see), I would change the sproc to be:
: >
: >ALTER PROC "sp_Billing"
: >
: > @Username varchar(50),
: > @StartDate datetime,
: > @CarriedBalance money OUTPUT
: >
: >AS
: >
: > SELECT
: > PaymentDate
: > , Credit
: > , Debit
: > FROM
: > table
: > WHERE
: > (UserName=@USERNAME)
: > AND
: > (PaymentDate >= @StartDate)
: > COMPUTE
: > SUM(Credit)
: > , SUM(Debit)
: >
: > SELECT
: > @CarriedBalance=COALESCE(Sum(Credit-Debit),0)
: > FROM
: > table
: > WHERE
: > (UserName=@USERNAME)
: > AND
: > (PaymentDate < @StartDate)
: >
: >then you could do:
: >
: ><%
: >Set objRS = objCommand.Execute
: >
: >If not objRS.EOF then
: > arrBilling = objRS.GetRows()
: >End If
: >
: >' Close recordset to access parameters!
: >objRS.Close
: >Set objRS = Nothing
: >
: >dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
: >%>
: >
: >Cheers
: >Ken
: >
: >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >From: "Martin Lee" <access@o...>
: >Subject: [asp_databases] Multiple Recorsets
: >
: >
: >: Hi:
: >:
: >: could someone please help with the correct way to check multiple
: >: recordsets for empty recordsets and then move to the next recordset.
: >:
: >: The stored procedure and code are below.
: >:
: >: Is there another way to check if a recorset is empty besides this:
: >:
: >: If Not IsNull(objrst(0)) Then
: >:
: >: arrBilling = objrst.GetRows()
: >:
: >: End If
: >:
: >: Have tried IF Not objrst.EOF but this still populates the array with 1
row
: >: of null values and creates problems when need to use:
: >:
: >: If Not IsArray(arrBilling) Then
: >: Else
: >: End If
: >:
: >: Since am new to SQL Server would also appreciate any comments on how to
: >: improve the below code.
: >:
: >: Thanks
: >:
: >: Martin
: >:
: >: CREATE Procedure "sp_Billing"
: >:
: >: @USERNAME varchar(50),
: >: @StartDate datetime,
: >: @CarriedBalance money OUTPUT
: >:
: >: As
: >: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From
table
: >: Where (UserName=@USERNAME)
: >: And (PaymentDate < @StartDate);
: >:
: >: Select PaymentDate, Credit, Debit
: >: From table
: >: Where (UserName=@USERNAME)
: >: And (PaymentDate >= @StartDate)
: >: Compute Sum(Credit), Sum(Debit)
: >:
: >:
: >: With objCommand
: >:
: >: .ActiveConnection = objconn
: >: .CommandType = adCmdStoredProc
: >: .CommandText = "sp_Billing"
: >: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
: >: adVarChar, adParamInput, 50, strUserName)
: >: .Parameters.Append = objCommand.CreateParameter("@StartDate",
: >: adDate, adParamInput, , strStartDate)
: >: .Parameters.Append
: >objCommand.CreateParameter("@CarriedBalance",
: >: adCurrency, adParamOutput)
: >:
: >: End With
: >:
: >: Set objrst = objCommand.Execute
: >:
: >: If Not IsNull(objrst(0)) Then
: >:
: >: arrBilling = objrst.GetRows()
: >:
: >: End If
: >:
: >: Set objrst = objrst.NextRecordSet()
: >:
: >:
: >: If Not objrst Is Nothing Then
: >:
: >: curCredit = objrst(0)
: >: curDebit = objrst(1)
: >:
: >: Else
: >:
: >: curCredit = 0
: >: curDebit =0
: >:
: >: End If
: >:
: >: Call CloseAll(objrst)
: >:
: >: curCarriedBalance = objCommand("@CarriedBalance")
: >:
: >: Call CloseAll(objCommand)
: >: Call CloseAll(objconn)
: >:
: >:
: >
: >
:
:
Message #2 by Martin Lee <access@o...> on Thu, 16 May 2002 22:22:47 +0800
|
|
Did some tests on the query and if no records are returned then the
recordset returns two NULL fields, which are passed into the array if I use:
If not objRS.EOF then
arrBilling = objRS.GetRows()
End If
Martin
At 12:39 PM 5/16/2002 +1000, you wrote:
>In your case it would be (you are only returning one recordset as far as I
>can see), I would change the sproc to be:
>
>ALTER PROC "sp_Billing"
>
> @Username varchar(50),
> @StartDate datetime,
> @CarriedBalance money OUTPUT
>
>AS
>
> SELECT
> PaymentDate
> , Credit
> , Debit
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate >= @StartDate)
> COMPUTE
> SUM(Credit)
> , SUM(Debit)
>
> SELECT
> @CarriedBalance=COALESCE(Sum(Credit-Debit),0)
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate < @StartDate)
>
>then you could do:
>
><%
>Set objRS = objCommand.Execute
>
>If not objRS.EOF then
> arrBilling = objRS.GetRows()
>End If
>
>' Close recordset to access parameters!
>objRS.Close
>Set objRS = Nothing
>
>dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
>%>
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Martin Lee" <access@o...>
>Subject: [asp_databases] Multiple Recorsets
>
>
>: Hi:
>:
>: could someone please help with the correct way to check multiple
>: recordsets for empty recordsets and then move to the next recordset.
>:
>: The stored procedure and code are below.
>:
>: Is there another way to check if a recorset is empty besides this:
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Have tried IF Not objrst.EOF but this still populates the array with 1 row
>: of null values and creates problems when need to use:
>:
>: If Not IsArray(arrBilling) Then
>: Else
>: End If
>:
>: Since am new to SQL Server would also appreciate any comments on how to
>: improve the below code.
>:
>: Thanks
>:
>: Martin
>:
>: CREATE Procedure "sp_Billing"
>:
>: @USERNAME varchar(50),
>: @StartDate datetime,
>: @CarriedBalance money OUTPUT
>:
>: As
>: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate < @StartDate);
>:
>: Select PaymentDate, Credit, Debit
>: From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate >= @StartDate)
>: Compute Sum(Credit), Sum(Debit)
>:
>:
>: With objCommand
>:
>: .ActiveConnection = objconn
>: .CommandType = adCmdStoredProc
>: .CommandText = "sp_Billing"
>: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
>: adVarChar, adParamInput, 50, strUserName)
>: .Parameters.Append = objCommand.CreateParameter("@StartDate",
>: adDate, adParamInput, , strStartDate)
>: .Parameters.Append
>objCommand.CreateParameter("@CarriedBalance",
>: adCurrency, adParamOutput)
>:
>: End With
>:
>: Set objrst = objCommand.Execute
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Set objrst = objrst.NextRecordSet()
>:
>:
>: If Not objrst Is Nothing Then
>:
>: curCredit = objrst(0)
>: curDebit = objrst(1)
>:
>: Else
>:
>: curCredit = 0
>: curDebit =0
>:
>: End If
>:
>: Call CloseAll(objrst)
>:
>: curCarriedBalance = objCommand("@CarriedBalance")
>:
>: Call CloseAll(objCommand)
>: Call CloseAll(objconn)
>:
>:
>
>
Message #3 by Martin Lee <access@o...> on Thu, 16 May 2002 20:53:43 +0800
|
|
If I use two separate Select queries in the stored procedure:
SELECT
PaymentDate
, Credit
, Debit
FROM
table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
SELECT Sum(Credit) 'credit', Sum(Debit) 'Debit'
FROM table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
this will return the multiple recordsets.
With multiple recordsets, what happens if the first query returns no
records? Are results of the second query returned?
Thanks,
Martin
At 08:57 AM 5/16/2002 -0400, you wrote:
>SELECT Sum(Credit) 'credit', Sum(Debit) 'Debit'
>FROM table
>WHERE
>(UserName=@USERNAME)
>AND
>(PaymentDate >= @StartDate)
>
>Debit = objrs("Debit")
>Credit = objrs("Credit")
>
>-----Original Message-----
>From: Martin Lee [mailto:access@o...]
>Sent: Thursday, May 16, 2002 8:32 AM
>To: ASP Databases
>Subject: [asp_databases] Re: Multiple Recorsets
>
>
>Thank you Ken,
>
>though doesn't the below query return two recordsets?
>
>SELECT
>PaymentDate
>, Credit
>, Debit
>FROM
>table
>WHERE
>(UserName=@USERNAME)
>AND
>(PaymentDate >= @StartDate)
>COMPUTE
>SUM(Credit)
>, SUM(Debit)
>
>After passing into an array, how can I access the result of COMPUTE
>SUM(Credit), SUM(Debit)?
>
>Thanks,
>
>Martin
>At 12:39 PM 5/16/2002 +1000, you wrote:
> >In your case it would be (you are only returning one recordset as far as I
> >can see), I would change the sproc to be:
> >
> >ALTER PROC "sp_Billing"
> >
> > @Username varchar(50),
> > @StartDate datetime,
> > @CarriedBalance money OUTPUT
> >
> >AS
> >
> > SELECT
> > PaymentDate
> > , Credit
> > , Debit
> > FROM
> > table
> > WHERE
> > (UserName=@USERNAME)
> > AND
> > (PaymentDate >= @StartDate)
> > COMPUTE
> > SUM(Credit)
> > , SUM(Debit)
> >
> > SELECT
> > @CarriedBalance=COALESCE(Sum(Credit-Debit),0)
> > FROM
> > table
> > WHERE
> > (UserName=@USERNAME)
> > AND
> > (PaymentDate < @StartDate)
> >
> >then you could do:
> >
> ><%
> >Set objRS = objCommand.Execute
> >
> >If not objRS.EOF then
> > arrBilling = objRS.GetRows()
> >End If
> >
> >' Close recordset to access parameters!
> >objRS.Close
> >Set objRS = Nothing
> >
> >dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
> >%>
> >
> >Cheers
> >Ken
> >
> >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >From: "Martin Lee" <access@o...>
> >Subject: [asp_databases] Multiple Recorsets
> >
> >
> >: Hi:
> >:
> >: could someone please help with the correct way to check multiple
> >: recordsets for empty recordsets and then move to the next recordset.
> >:
> >: The stored procedure and code are below.
> >:
> >: Is there another way to check if a recorset is empty besides this:
> >:
> >: If Not IsNull(objrst(0)) Then
> >:
> >: arrBilling = objrst.GetRows()
> >:
> >: End If
> >:
> >: Have tried IF Not objrst.EOF but this still populates the array with 1
>row
> >: of null values and creates problems when need to use:
> >:
> >: If Not IsArray(arrBilling) Then
> >: Else
> >: End If
> >:
> >: Since am new to SQL Server would also appreciate any comments on how to
> >: improve the below code.
> >:
> >: Thanks
> >:
> >: Martin
> >:
> >: CREATE Procedure "sp_Billing"
> >:
> >: @USERNAME varchar(50),
> >: @StartDate datetime,
> >: @CarriedBalance money OUTPUT
> >:
> >: As
> >: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
> >: Where (UserName=@USERNAME)
> >: And (PaymentDate < @StartDate);
> >:
> >: Select PaymentDate, Credit, Debit
> >: From table
> >: Where (UserName=@USERNAME)
> >: And (PaymentDate >= @StartDate)
> >: Compute Sum(Credit), Sum(Debit)
> >:
> >:
> >: With objCommand
> >:
> >: .ActiveConnection = objconn
> >: .CommandType = adCmdStoredProc
> >: .CommandText = "sp_Billing"
> >: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
> >: adVarChar, adParamInput, 50, strUserName)
> >: .Parameters.Append = objCommand.CreateParameter("@StartDate",
> >: adDate, adParamInput, , strStartDate)
> >: .Parameters.Append
> >objCommand.CreateParameter("@CarriedBalance",
> >: adCurrency, adParamOutput)
> >:
> >: End With
> >:
> >: Set objrst = objCommand.Execute
> >:
> >: If Not IsNull(objrst(0)) Then
> >:
> >: arrBilling = objrst.GetRows()
> >:
> >: End If
> >:
> >: Set objrst = objrst.NextRecordSet()
> >:
> >:
> >: If Not objrst Is Nothing Then
> >:
> >: curCredit = objrst(0)
> >: curDebit = objrst(1)
> >:
> >: Else
> >:
> >: curCredit = 0
> >: curDebit =0
> >:
> >: End If
> >:
> >: Call CloseAll(objrst)
> >:
> >: curCarriedBalance = objCommand("@CarriedBalance")
> >:
> >: Call CloseAll(objCommand)
> >: Call CloseAll(objconn)
> >:
> >:
> >
> >
>
>
>
Message #4 by James Kenney <JEK@m...> on Thu, 16 May 2002 08:57:18 -0400
|
|
SELECT Sum(Credit) 'credit', Sum(Debit) 'Debit'
FROM table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
Debit = objrs("Debit")
Credit = objrs("Credit")
-----Original Message-----
From: Martin Lee [mailto:access@o...]
Sent: Thursday, May 16, 2002 8:32 AM
To: ASP Databases
Subject: [asp_databases] Re: Multiple Recorsets
Thank you Ken,
though doesn't the below query return two recordsets?
SELECT
PaymentDate
, Credit
, Debit
FROM
table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
COMPUTE
SUM(Credit)
, SUM(Debit)
After passing into an array, how can I access the result of COMPUTE
SUM(Credit), SUM(Debit)?
Thanks,
Martin
At 12:39 PM 5/16/2002 +1000, you wrote:
>In your case it would be (you are only returning one recordset as far as I
>can see), I would change the sproc to be:
>
>ALTER PROC "sp_Billing"
>
> @Username varchar(50),
> @StartDate datetime,
> @CarriedBalance money OUTPUT
>
>AS
>
> SELECT
> PaymentDate
> , Credit
> , Debit
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate >= @StartDate)
> COMPUTE
> SUM(Credit)
> , SUM(Debit)
>
> SELECT
> @CarriedBalance=COALESCE(Sum(Credit-Debit),0)
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate < @StartDate)
>
>then you could do:
>
><%
>Set objRS = objCommand.Execute
>
>If not objRS.EOF then
> arrBilling = objRS.GetRows()
>End If
>
>' Close recordset to access parameters!
>objRS.Close
>Set objRS = Nothing
>
>dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
>%>
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Martin Lee" <access@o...>
>Subject: [asp_databases] Multiple Recorsets
>
>
>: Hi:
>:
>: could someone please help with the correct way to check multiple
>: recordsets for empty recordsets and then move to the next recordset.
>:
>: The stored procedure and code are below.
>:
>: Is there another way to check if a recorset is empty besides this:
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Have tried IF Not objrst.EOF but this still populates the array with 1
row
>: of null values and creates problems when need to use:
>:
>: If Not IsArray(arrBilling) Then
>: Else
>: End If
>:
>: Since am new to SQL Server would also appreciate any comments on how to
>: improve the below code.
>:
>: Thanks
>:
>: Martin
>:
>: CREATE Procedure "sp_Billing"
>:
>: @USERNAME varchar(50),
>: @StartDate datetime,
>: @CarriedBalance money OUTPUT
>:
>: As
>: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate < @StartDate);
>:
>: Select PaymentDate, Credit, Debit
>: From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate >= @StartDate)
>: Compute Sum(Credit), Sum(Debit)
>:
>:
>: With objCommand
>:
>: .ActiveConnection = objconn
>: .CommandType = adCmdStoredProc
>: .CommandText = "sp_Billing"
>: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
>: adVarChar, adParamInput, 50, strUserName)
>: .Parameters.Append = objCommand.CreateParameter("@StartDate",
>: adDate, adParamInput, , strStartDate)
>: .Parameters.Append
>objCommand.CreateParameter("@CarriedBalance",
>: adCurrency, adParamOutput)
>:
>: End With
>:
>: Set objrst = objCommand.Execute
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Set objrst = objrst.NextRecordSet()
>:
>:
>: If Not objrst Is Nothing Then
>:
>: curCredit = objrst(0)
>: curDebit = objrst(1)
>:
>: Else
>:
>: curCredit = 0
>: curDebit =0
>:
>: End If
>:
>: Call CloseAll(objrst)
>:
>: curCarriedBalance = objCommand("@CarriedBalance")
>:
>: Call CloseAll(objCommand)
>: Call CloseAll(objconn)
>:
>:
>
>
Message #5 by Martin Lee <access@o...> on Thu, 16 May 2002 20:32:22 +0800
|
|
Thank you Ken,
though doesn't the below query return two recordsets?
SELECT
PaymentDate
, Credit
, Debit
FROM
table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
COMPUTE
SUM(Credit)
, SUM(Debit)
After passing into an array, how can I access the result of COMPUTE
SUM(Credit), SUM(Debit)?
Thanks,
Martin
At 12:39 PM 5/16/2002 +1000, you wrote:
>In your case it would be (you are only returning one recordset as far as I
>can see), I would change the sproc to be:
>
>ALTER PROC "sp_Billing"
>
> @Username varchar(50),
> @StartDate datetime,
> @CarriedBalance money OUTPUT
>
>AS
>
> SELECT
> PaymentDate
> , Credit
> , Debit
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate >= @StartDate)
> COMPUTE
> SUM(Credit)
> , SUM(Debit)
>
> SELECT
> @CarriedBalance=COALESCE(Sum(Credit-Debit),0)
> FROM
> table
> WHERE
> (UserName=@USERNAME)
> AND
> (PaymentDate < @StartDate)
>
>then you could do:
>
><%
>Set objRS = objCommand.Execute
>
>If not objRS.EOF then
> arrBilling = objRS.GetRows()
>End If
>
>' Close recordset to access parameters!
>objRS.Close
>Set objRS = Nothing
>
>dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
>%>
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Martin Lee" <access@o...>
>Subject: [asp_databases] Multiple Recorsets
>
>
>: Hi:
>:
>: could someone please help with the correct way to check multiple
>: recordsets for empty recordsets and then move to the next recordset.
>:
>: The stored procedure and code are below.
>:
>: Is there another way to check if a recorset is empty besides this:
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Have tried IF Not objrst.EOF but this still populates the array with 1 row
>: of null values and creates problems when need to use:
>:
>: If Not IsArray(arrBilling) Then
>: Else
>: End If
>:
>: Since am new to SQL Server would also appreciate any comments on how to
>: improve the below code.
>:
>: Thanks
>:
>: Martin
>:
>: CREATE Procedure "sp_Billing"
>:
>: @USERNAME varchar(50),
>: @StartDate datetime,
>: @CarriedBalance money OUTPUT
>:
>: As
>: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate < @StartDate);
>:
>: Select PaymentDate, Credit, Debit
>: From table
>: Where (UserName=@USERNAME)
>: And (PaymentDate >= @StartDate)
>: Compute Sum(Credit), Sum(Debit)
>:
>:
>: With objCommand
>:
>: .ActiveConnection = objconn
>: .CommandType = adCmdStoredProc
>: .CommandText = "sp_Billing"
>: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
>: adVarChar, adParamInput, 50, strUserName)
>: .Parameters.Append = objCommand.CreateParameter("@StartDate",
>: adDate, adParamInput, , strStartDate)
>: .Parameters.Append
>objCommand.CreateParameter("@CarriedBalance",
>: adCurrency, adParamOutput)
>:
>: End With
>:
>: Set objrst = objCommand.Execute
>:
>: If Not IsNull(objrst(0)) Then
>:
>: arrBilling = objrst.GetRows()
>:
>: End If
>:
>: Set objrst = objrst.NextRecordSet()
>:
>:
>: If Not objrst Is Nothing Then
>:
>: curCredit = objrst(0)
>: curDebit = objrst(1)
>:
>: Else
>:
>: curCredit = 0
>: curDebit =0
>:
>: End If
>:
>: Call CloseAll(objrst)
>:
>: curCarriedBalance = objCommand("@CarriedBalance")
>:
>: Call CloseAll(objCommand)
>: Call CloseAll(objconn)
>:
>:
>
>
Message #6 by "Ken Schaefer" <ken@a...> on Thu, 16 May 2002 12:39:54 +1000
|
|
In your case it would be (you are only returning one recordset as far as I
can see), I would change the sproc to be:
ALTER PROC "sp_Billing"
@Username varchar(50),
@StartDate datetime,
@CarriedBalance money OUTPUT
AS
SELECT
PaymentDate
, Credit
, Debit
FROM
table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate >= @StartDate)
COMPUTE
SUM(Credit)
, SUM(Debit)
SELECT
@CarriedBalance=COALESCE(Sum(Credit-Debit),0)
FROM
table
WHERE
(UserName=@USERNAME)
AND
(PaymentDate < @StartDate)
then you could do:
<%
Set objRS = objCommand.Execute
If not objRS.EOF then
arrBilling = objRS.GetRows()
End If
' Close recordset to access parameters!
objRS.Close
Set objRS = Nothing
dblCarriedBalance = objCommand.Parameters("@CarriedBalance").Value
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Martin Lee" <access@o...>
Subject: [asp_databases] Multiple Recorsets
: Hi:
:
: could someone please help with the correct way to check multiple
: recordsets for empty recordsets and then move to the next recordset.
:
: The stored procedure and code are below.
:
: Is there another way to check if a recorset is empty besides this:
:
: If Not IsNull(objrst(0)) Then
:
: arrBilling = objrst.GetRows()
:
: End If
:
: Have tried IF Not objrst.EOF but this still populates the array with 1 row
: of null values and creates problems when need to use:
:
: If Not IsArray(arrBilling) Then
: Else
: End If
:
: Since am new to SQL Server would also appreciate any comments on how to
: improve the below code.
:
: Thanks
:
: Martin
:
: CREATE Procedure "sp_Billing"
:
: @USERNAME varchar(50),
: @StartDate datetime,
: @CarriedBalance money OUTPUT
:
: As
: Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
: Where (UserName=@USERNAME)
: And (PaymentDate < @StartDate);
:
: Select PaymentDate, Credit, Debit
: From table
: Where (UserName=@USERNAME)
: And (PaymentDate >= @StartDate)
: Compute Sum(Credit), Sum(Debit)
:
:
: With objCommand
:
: .ActiveConnection = objconn
: .CommandType = adCmdStoredProc
: .CommandText = "sp_Billing"
: .Parameters.Append = objCommand.CreateParameter("@USERNAME",
: adVarChar, adParamInput, 50, strUserName)
: .Parameters.Append = objCommand.CreateParameter("@StartDate",
: adDate, adParamInput, , strStartDate)
: .Parameters.Append
objCommand.CreateParameter("@CarriedBalance",
: adCurrency, adParamOutput)
:
: End With
:
: Set objrst = objCommand.Execute
:
: If Not IsNull(objrst(0)) Then
:
: arrBilling = objrst.GetRows()
:
: End If
:
: Set objrst = objrst.NextRecordSet()
:
:
: If Not objrst Is Nothing Then
:
: curCredit = objrst(0)
: curDebit = objrst(1)
:
: Else
:
: curCredit = 0
: curDebit =0
:
: End If
:
: Call CloseAll(objrst)
:
: curCarriedBalance = objCommand("@CarriedBalance")
:
: Call CloseAll(objCommand)
: Call CloseAll(objconn)
:
:
Message #7 by Martin Lee <access@o...> on Wed, 15 May 2002 21:15:14 +0800
|
|
Hi:
could someone please help with the correct way to check multiple
recordsets for empty recordsets and then move to the next recordset.
The stored procedure and code are below.
Is there another way to check if a recorset is empty besides this:
If Not IsNull(objrst(0)) Then
arrBilling = objrst.GetRows()
End If
Have tried IF Not objrst.EOF but this still populates the array with 1 row
of null values and creates problems when need to use:
If Not IsArray(arrBilling) Then
Else
End If
Since am new to SQL Server would also appreciate any comments on how to
improve the below code.
Thanks
Martin
CREATE Procedure "sp_Billing"
@USERNAME varchar(50),
@StartDate datetime,
@CarriedBalance money OUTPUT
As
Select @CarriedBalance=COALESCE(Sum(Credit-Debit),0) From table
Where (UserName=@USERNAME)
And (PaymentDate < @StartDate);
Select PaymentDate, Credit, Debit
From table
Where (UserName=@USERNAME)
And (PaymentDate >= @StartDate)
Compute Sum(Credit), Sum(Debit)
With objCommand
.ActiveConnection = objconn
.CommandType = adCmdStoredProc
.CommandText = "sp_Billing"
.Parameters.Append = objCommand.CreateParameter("@USERNAME",
adVarChar, adParamInput, 50, strUserName)
.Parameters.Append = objCommand.CreateParameter("@StartDate",
adDate, adParamInput, , strStartDate)
.Parameters.Append = objCommand.CreateParameter("@CarriedBalance",
adCurrency, adParamOutput)
End With
Set objrst = objCommand.Execute
If Not IsNull(objrst(0)) Then
arrBilling = objrst.GetRows()
End If
Set objrst = objrst.NextRecordSet()
If Not objrst Is Nothing Then
curCredit = objrst(0)
curDebit = objrst(1)
Else
curCredit = 0
curDebit =0
End If
Call CloseAll(objrst)
curCarriedBalance = objCommand("@CarriedBalance")
Call CloseAll(objCommand)
Call CloseAll(objconn)
|
|
|
View Cart
