|
|
Posted by Ben on 06/04/07 16:52
Hi!
I already sent this to the ACCESS newsgroup. But since I do not know really
which side is really causing the problem, I have decided to send this
inquiry
to this newsgroup also, if I may.
Below is the environment of the application:
a. MS Access 2003 application running on Windows XP
b. SQL Server 2000 - backend running MS Server 2003 OS
Below is the code that is giving me an error:
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 ' This is where it hangs up...
TotalItems = .Parameters("@TotalInStock")
TotalCost = .Parameters("@TotalCost")
End With
Set com = Nothing
and the store procedure is:
CREATE PROCEDURE DBO.sp_Recalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0,
@TotalCost money = 0
AS
BEGIN
SET @TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber)
SET @TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber)
END
When the process goes to the ".Execute" line, it hangs up for a long time
then gives me an error message "Everflow". I have been trying to solve
this issue but do not have an idea for now of the cause.
Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just fine.
I placed a SELECT statement to view the result of the stored procedure.
It gives the correct values.
Can anyone have ideas or similar problems?
Thanks.
Navigation:
[Reply to this message]
|