ADODB Command (Stored Procedure)

[Ben]

Hi!


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.

 

[Douglas J. Steele]

What is the data type of the Quantity In Stock field in your table? If, for
example, it's a tinyint (which can only hold values between 0 and 255), and
the sum is greater than 255, you'll get that Overflow Error you're
experiencing.

 

[Ben]

The data type of [Quantity In Stock] column is INT with 4 as its scale.

I also checked the data type of the variable "TotalItems" is long.

What is the data type of the Quantity In Stock field in your table? If,
for example, it's a tinyint (which can only hold values between 0 and
255), and the sum is greater than 255, you'll get that Overflow Error
you're experiencing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi!


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.

 

[RoyVidar]

Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

 

[Ben]

I thought that was the cause but when I made the changes to the stored
procedure and ran the Access app, I still got the same error.

Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

 

[Douglas J. Steele]

Can you run it through SQL Server?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The data type of [Quantity In Stock] column is INT with 4 as its scale.

I also checked the data type of the variable "TotalItems" is long.

What is the data type of the Quantity In Stock field in your table? If,
for example, it's a tinyint (which can only hold values between 0 and
255), and the sum is greater than 255, you'll get that Overflow Error
you're experiencing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi!


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.

 

[Ben]

Yes Doug.

It seems to work fine if I execute the stored procedure in SQL Analyzer.

Can you run it through SQL Server?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The data type of [Quantity In Stock] column is INT with 4 as its scale.

I also checked the data type of the variable "TotalItems" is long.

What is the data type of the Quantity In Stock field in your table? If,
for example, it's a tinyint (which can only hold values between 0 and
255), and the sum is greater than 255, you'll get that Overflow Error
you're experiencing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi!


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.

 

[RoyVidar]

I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same error.

Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think this
error might occur. In your SP try casting at least one of the columns
in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

 

[Ben]

Right now, this is the existing script for the stored procedure:

CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
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

GO

It still hangs for a minute plus and then gives an overflow error.

I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same error.

<[email protected]>:
Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think this
error might occur. In your SP try casting at least one of the columns
in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

 

[Sylvain Lafontaine]

First, make sure that you use the right name. In some of your examples, you
are using sp_Recalculate (BTW, a bad idea to use sp_ as a prefix to a SP
because it has a special meaning to SQL-Server) and in others, you are using
procRecalculate.

Second, to my limited knowledge, the refreshment of parameters is not
available with all providers. By using a DSN, you are either using the ODBC
provider or the strange Microsoft ODBC Provider for OLEDB Provider; I'm not
sure if either of them are able to perform this type of commnand.

Also, I don't remember if you must use the @ prefix for the name of
parameters. This is the piece of code that I was using some years ago to
retrieve the properties of the parameters:

Sub ListOfParameters (NameOfTheSP As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = NameOfTheSP
cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub



For the Connection (CurrentProject.Connection), use the appropriate string
connection but if this doesn't work with the DSN, replace it with the
SQL-Server OLEDB provider. Don't use the dbo. prefixe for the name of the
procedure and finally, using the Refresh command is not the best idea: it
will slow your request because it require more trips to the SQL-Server. You
should instead explicitely create all of your parameters.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Right now, this is the existing script for the stored procedure:

CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
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

GO

It still hangs for a minute plus and then gives an overflow error.

I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same error.


<[email protected]>:
Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think this
error might occur. In your SP try casting at least one of the columns
in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

 

[RoyVidar]

Right now, this is the existing script for the stored procedure:

CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
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

GO

It still hangs for a minute plus and then gives an overflow error.

I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same
error.


<[email protected]>:
Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think
this error might occur. In your SP try casting at least one of the
columns in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

I really don't know, but what about trying one at a time?

(I think cast *one* of the *columns* in the calculation, not the whole
calculation)

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



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

Is Int large enough for your sum Quantity in Stock? If not, cast as
BigInt (both in the assignement and parameter - AND in VB - Variant?)
or some other large enough data type?

 

[Sylvain Lafontaine]

You should also set the SET NOCOUNT ON property at the beginning of all your
SP. Except on rare occasions, this is usually what you want to do.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

First, make sure that you use the right name. In some of your examples,
you are using sp_Recalculate (BTW, a bad idea to use sp_ as a prefix to a
SP because it has a special meaning to SQL-Server) and in others, you are
using procRecalculate.

Second, to my limited knowledge, the refreshment of parameters is not
available with all providers. By using a DSN, you are either using the
ODBC provider or the strange Microsoft ODBC Provider for OLEDB Provider;
I'm not sure if either of them are able to perform this type of commnand.

Also, I don't remember if you must use the @ prefix for the name of
parameters. This is the piece of code that I was using some years ago to
retrieve the properties of the parameters:

Sub ListOfParameters (NameOfTheSP As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = NameOfTheSP
cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub



For the Connection (CurrentProject.Connection), use the appropriate string
connection but if this doesn't work with the DSN, replace it with the
SQL-Server OLEDB provider. Don't use the dbo. prefixe for the name of the
procedure and finally, using the Refresh command is not the best idea: it
will slow your request because it require more trips to the SQL-Server.
You should instead explicitely create all of your parameters.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Right now, this is the existing script for the stored procedure:

CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
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

GO

It still hangs for a minute plus and then gives an overflow error.

<[email protected]>:
I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same error.


<[email protected]>:
Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

--
Roy-Vidar



I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think this
error might occur. In your SP try casting at least one of the columns
in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

 

[Sylvain Lafontaine]

Finally, take a look with the SQL-Server Profiler to see what's sent to it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

You should also set the SET NOCOUNT ON property at the beginning of all
your SP. Except on rare occasions, this is usually what you want to do.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

First, make sure that you use the right name. In some of your examples,
you are using sp_Recalculate (BTW, a bad idea to use sp_ as a prefix to a
SP because it has a special meaning to SQL-Server) and in others, you are
using procRecalculate.

Second, to my limited knowledge, the refreshment of parameters is not
available with all providers. By using a DSN, you are either using the
ODBC provider or the strange Microsoft ODBC Provider for OLEDB Provider;
I'm not sure if either of them are able to perform this type of commnand.

Also, I don't remember if you must use the @ prefix for the name of
parameters. This is the piece of code that I was using some years ago to
retrieve the properties of the parameters:

Sub ListOfParameters (NameOfTheSP As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = NameOfTheSP
cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub



For the Connection (CurrentProject.Connection), use the appropriate
string connection but if this doesn't work with the DSN, replace it with
the SQL-Server OLEDB provider. Don't use the dbo. prefixe for the name
of the procedure and finally, using the Refresh command is not the best
idea: it will slow your request because it require more trips to the
SQL-Server. You should instead explicitely create all of your parameters.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Right now, this is the existing script for the stored procedure:

CREATE PROCEDURE DBO.procRecalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0 OUTPUT,
@TotalCost money = 0 OUTPUT
AS
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

GO

It still hangs for a minute plus and then gives an overflow error.


<[email protected]>:
I thought that was the cause but when I made the changes to the
stored procedure and ran the Access app, I still got the same error.


<[email protected]>:
Hi!


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.

In your SP, try declare the parameters as output parameters

@TotalInStock int = 0 Output,
@TotalCost money = 0 Output

--
Roy-Vidar



I don't know, but I would guess that the overflow has something with
the SP to do. I'm not very versed in SQL server, but I think it does
some typecasting based on the datatypes of the columns used in the
calculation, and if that's not the same as the target type, or the
result is larger than the source column datatype allows, I think this
error might occur. In your SP try casting at least one of the columns
in the calculation to the target data type

SELECT Sum(Cast([Cost] As Money) * [Quantity in Stock])

 

[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.

Hi!


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.

 

[Sylvain Lafontaine]

You have forgot to write the size for the string parameter and its
adVarWChar, not adVarChar. Try with:

.Parameters.Append .CreateParameter("ItemNumber", adVarWChar,
adParamInput, 50, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput)

.Execute ' This is where it hangs up...

MyTotalInStock = .Parameters("TotalInStock")
MyTotalCost = .Parameters("TotalCost")

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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.

Hi!


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.