473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

ADODB Command (Stored Procedure)

Ben
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.
Jun 4 '07 #1
8 11652
Ben (pi******@sbcglobal.net) writes:
.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
Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.
@ItemNumber nvarchar(50),
@TotalInStock int = 0,
@TotalCost money = 0
AS
Judging from the code, the parameters @TotalInStock and @TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.

Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.
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
Rewrite as

SELECT @TotalInStock = Sum([Quantity in Stock]),
@TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber

That will slash the execution time in half.
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.
I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?
Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just
fine.
And it completes in how long time?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 4 '07 #2
Ben
I timed the execution: 1 minute. Then it gives me the overflow error.

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.

Is there an issue between the number of open connections to the database?
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Ben (pi******@sbcglobal.net) writes:
> .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

Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.
> @ItemNumber nvarchar(50),
@TotalInStock int = 0,
@TotalCost money = 0
AS

Judging from the code, the parameters @TotalInStock and @TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.

Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.
>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

Rewrite as

SELECT @TotalInStock = Sum([Quantity in Stock]),
@TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber

That will slash the execution time in half.
>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.

I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?
>Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just
fine.

And it completes in how long time?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #3
Ben (pi******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '07 #4
Ben
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" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Ben (pi******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #5
Ben
I also changed the stored procedure name to "procRecalculate".
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Ben (pi******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #6
Ben (pi******@sbcglobal.net) writes:
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.
Do you still get the overflow error, or does it work alright now?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '07 #7
Ben
This is the current scripts of the application:

Dim com As ADODB.Command
Dim MyItemNumber As String, MyTotalInStock As Long, MyTotalCost As
Currency

Set com = New ADODB.Command

MyItemNumber = ItemNum
MyTotalInStock = 0
MyTotalCost = 0

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "procRecalculate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)
.Execute

End With

Set com = Nothing

If IsNull(MyTotalInStock) Then MyTotalInStock = 0
If IsNull(MyTotalCost) Then MyTotalCost = 0

TotalItems = MyTotalInStock
TotalCost = MyTotalCost

=============================================
CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
SET NOCOUNT ON

SELECT @TotalInStock = Sum(Cast([Quantity in Stock] As int)),
@TotalCost = Sum(Cast([Cost] * [Quantity in Stock] As money))
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber

SET NOCOUNT OFF
GO

Unfortunately, I still get the same error. But this time, I get it in a
second.


"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Ben (pi******@sbcglobal.net) writes:
>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.

Do you still get the overflow error, or does it work alright now?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #8
Ben (pi******@sbcglobal.net) writes:
This is the current scripts of the application:
...
.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)
Here is an error: .CreateParameter takes five parameter of which the
fourth is the size, and the fifth is the value. Thus you need to insert
an extra comma after adParamInput, adParamOutput.
Unfortunately, I still get the same error. But this time, I get it in a
second.
Well, at least some progress. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 6 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Tom | last post by:
I want to open a recordset object on an .asp page. When I open the recordset I would like to use a stored procedure that expects a parameter to be passed for the stored procedure. I will then use...
1
by: Sandie Towers | last post by:
We use a number of similar databases and frequently create a new database using a backup restore of another similar database. We try to keep changes between databases in _Additional tables - like...
3
by: Robert Batt | last post by:
Hello, I am having a problem with ADODB connection class. This was all working fine until I restarted the pc with last known good configuration, which seems to have screwed things up In the code...
4
by: Fresh_Air_Rider | last post by:
Hi In the "good old" Classic ASP days, I used to stream records from a SQL Server database out to the user's browser in CSV format by using a combination of COALESCE and the ADODB.Stream object....
8
by: Ivan | last post by:
Hi I am new in DB2, and I have some problems when I try run stored procedures, and others statements. I made one stored procedure very simple, but this show different messages. I have followed...
7
by: Aleks Kleyn | last post by:
At this time my code use ODBC and adodb to access database. Working with win vista i discovered that way i cannot work with SQL server. it is not clear this is bug or Microsoft suppose to give...
0
by: dmckenna | last post by:
I've been tasked to upgrade an old system and there's many different versions of VB code that uses MDAC to talk to MSSql. Do you know what the difference is between the two code versions? Is there...
1
by: DragonFields | last post by:
I'm hoping someone can help me. I keep receiving the ADODB.Command error '800a0cc1' error (Item cannot be found in the collection corresponding to the requested name or ordinal.) I found a couple...
2
by: db2learner | last post by:
Hi, I am new to DB2 and i just started worked on it a couple of days back. I have created basic EMPLOYEE table from control centre which has 2 fields: EmpNo, EmpName. I am trying to write...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.