|
|
Posted by Ben on 06/05/07 11:24
I made th necessary changes. Below are the current scripts for both the
calling method and the
store procedure. Variable "ItemNum" is being passed as a string parameter to
the recalculate
method.
'**************************** This is the script in the calling method
*************************
Dim TotalCost As Currency, TotalItems As Long, AvgCost As Currency
Dim CurDB As Database, Inv As Recordset, InvP As Recordset, SQLStmt As
String, SQLStmt2 As String
Dim com As ADODB.Command
Set com = New ADODB.Command
With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ItemNumber") = ItemNum
.Execute
TotalItems = .Parameters("@TotalInStock")
TotalCost = .Parameters("@TotalCost")
End With
Set com = Nothing
================================================================
'*************************** This is the current script in the stored
procedure *******************
CREATE PROCEDURE DBO.sp_Recalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
SELECT @TotalInStock = Sum([Quantity in Stock]),
@TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber
GO
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns994667A846B71Yazorman@127.0.0.1...
> Ben (pillars4@sbcglobal.net) writes:
>> I timed the execution: 1 minute. Then it gives me the overflow error.
>
> Does it run for one minute in QA as well?
>
>> I double checked the code especially the declarations, and they seem to
>> okay. The "TotalItems" var in the calling method uses "long" as its
>> data type. The "@TotalInStock" OUTPUT var in the stored procedure is
>> declared as "int". They seem to be okay.
>
> And the data type for TotalCost is?
>
>> Is there an issue between the number of open connections to the database?
>
> No, that has nothing to do with it.
>
> There were a couple of more issues with your code that I pointed out,
> but you did not comment these. The code you posted will not work for
> reasons I've already detailed.
>
> It may be that you did not post the actual code, but just scribbled down a
> sketch and introduced a few errors along the way. But in that case, I
> don't
> know what you are doing, so I cannot say more than I've already said.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|