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
.ActiveConnecti on = "DSN=YES2;DATAB ASE=YES100SQLC; "
.CommandText = "sp_Recalculate "
.CommandType = adCmdStoredProc
.Parameters.Ref resh
.Parameters("@I temNumber") = ItemNum
.Execute ' This is where it hangs up...
TotalItems = .Parameters("@T otalInStock")
TotalCost = .Parameters("@T otalCost")
End With
Set com = Nothing
and the store procedure is:
CREATE PROCEDURE DBO.sp_Recalcul ate
@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.
8  11682 
Ben (pi******@sbcgl obal.net) writes:
.Execute ' This is where it hangs up...
TotalItems = .Parameters("@T otalInStock")
TotalCost = .Parameters("@T otalCost")
End With
Set com = Nothing
and the store procedure is:
CREATE PROCEDURE DBO.sp_Recalcul ate
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****@sommarsk og.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
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 "@TotalInSt ock" 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****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Ben (pi******@sbcgl obal.net) writes:
> .Execute ' This is where it hangs up...
TotalItems = .Parameters("@T otalInStock")
TotalCost = .Parameters("@T otalCost")
End With
Set com = Nothing
and the store procedure is:
CREATE PROCEDURE DBO.sp_Recalcul ate
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****@sommarsk og.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
Ben (pi******@sbcgl obal.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 "@TotalInSt ock" 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****@sommarsk og.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
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
.ActiveConnecti on = "DSN=YES2;DATAB ASE=YES100SQLC; "
.CommandText = "sp_Recalculate "
.CommandType = adCmdStoredProc
.Parameters.Ref resh
.Parameters("@I temNumber") = ItemNum
.Execute
TotalItems = .Parameters("@T otalInStock")
TotalCost = .Parameters("@T otalCost")
End With
Set com = Nothing
=============== =============== =============== =============== ====
'************** ************* This is the current script in the stored
procedure *************** ****
CREATE PROCEDURE DBO.sp_Recalcul ate
@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****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Ben (pi******@sbcgl obal.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 "@TotalInSt ock" 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****@sommarsk og.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
I also changed the stored procedure name to "procRecalculat e".
"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Ben (pi******@sbcgl obal.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 "@TotalInSt ock" 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****@sommarsk og.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
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
.ActiveConnecti on = "DSN=YES2;DATAB ASE=YES100SQLC; "
.CommandText = "procRecalculat e"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("ItemNumber ", adVarChar,
adParamInput, MyItemNumber)
.Parameters.App end .CreateParamete r("TotalInStock ", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.App end .CreateParamete r("TotalCost" , adCurrency,
adParamOutput, MyTotalCost)
.Execute
End With
Set com = Nothing
If IsNull(MyTotalI nStock) Then MyTotalInStock = 0
If IsNull(MyTotalC ost) Then MyTotalCost = 0
TotalItems = MyTotalInStock
TotalCost = MyTotalCost
=============== =============== ===============
CREATE PROCEDURE DBO.procRecalcu late
@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****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Ben (pi******@sbcgl obal.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****@sommarsk og.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
Ben (pi******@sbcgl obal.net) writes:
This is the current scripts of the application:
...
.Parameters.App end .CreateParamete r("ItemNumber ", adVarChar,
adParamInput , MyItemNumber)
.Parameters.App end .CreateParamete r("TotalInStock ", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.App end .CreateParamete r("TotalCost" , adCurrency,
adParamOutput, MyTotalCost)
Here is an error: .CreateParamete r 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****@sommarsk og.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 the recordset to loop
thru the recordset, update values from the recordset and then update the
database by passing parmeters to another stored procedure. I would like to
use the recordset object but can it be used to pass a parameter to a stored...
|
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 Account
Additional, Sale_Additional so most tables stay the same. The latest
restored database (I'll call it DBaseA) is behaving differently in VB6
code and I need help trying to make it work.
I have been using use an ADODB.Command to execute...
|
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 below the conn.open command fails with a cannot find stored procedure error. and system.runtime.interopservices.COMexception. The specified procedure could not be found. at ADODB.ConnectionClass.Open etc.. How can I fix this problem, do I need to...
|
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.
The trouble is that I'm not really sure how to do this in C# 2005.
This was a very useful technique and if anyone could please show me how
to adapt the Classic ASP code to C# 2005, I'd be very grateful indeed.
|
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 the indications that have said me in answer to topics,
but without positive results
This is the stored procedure
CREATE PROCEDURE TESTS ( )
| | |
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 priority to ado.net. The same time
my friend told me that Microsoft does not recomend to use ado.net. It sounds
confusing. I expected that ado.net can help to improve responce time from
database. Also ado.net simplify work with hierarhical recordset...
|
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 problems with re-opening connections all the time?
Version 1 of the program is:
|
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 similar errors on the discussions but am not tracking how to apply the fixes into my code, which of course is slightly different...I'm using Visual Interdev 6.0 with MS SQL Enterprise Mgr 8.0. I'm linking an .asp page to a stored procedure and I've...
|
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 a simple stored proc in db2 command editor to insert values into these fields.
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
