Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 30th, 2004, 02:25 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default Arithmetic overflow error converting expression to

hi everyone
I have this particular error. Now I have a textbox where I am required to enter the year. Till 2004, it works fine, but if my input is above that year, its returning ''Arithmetic overflow error converting expression to data type datetime''.

Can anyone pls tell me the reason. I tried to put an condition,
if Session("rptyear") > Year(now()) then Session("rptyear") = Year(now()), but its hardcoding the year at that time and returning 2004 instead of the supplied year.

Any help would be appreciated

Regards

sinapra
__________________
sinapra
  #2 (permalink)  
Old August 30th, 2004, 07:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

Try CInt with both values, to ensure that you are using the same data types to make the comparison.

Brian
  #3 (permalink)  
Old August 31st, 2004, 01:24 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

ya I mentioned the reason at the bottom :

If Not (CInt(Session("rptyear")) > 1000 And CInt(Session("rptyear")) < 9000) Then
............

Still it gives the same error.
It in fact shows error in two ways, one when I am entering a number say 45454565, the error comes as
"Microsoft VBScript runtime (0x800A0006)
Overflow: 'CInt'"
The other error it shows is
"Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime."

Both are errors are in different lines.

Any help

sinapra
  #4 (permalink)  
Old August 31st, 2004, 11:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

The first is because the number is too big, use cdbl() instead, and use doubles. If you are passing in a manual value, set the maxlength on the textbox so you can't enter more than 4 digits, and then you won't have to convert to a double.

In looking at the second error, that is a problem with your query, and not a session problem. What are you doing with the database?

Brian
  #5 (permalink)  
Old September 1st, 2004, 03:00 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

ok my query is

SELECT Register.SR_Num, Register.CSC_Project_Code AS [CSC Project Code], Register.Project_Common_Name
AS [Project Name], People.Name AS [Project Manager], BusinessUnit.Name AS [Business Unit], COP.COP_Num_Text
AS [C O P], IMACType.IType AS [IMAC Type], IMAC.ServiceDate AS [Service Date], IMAC.Units AS Total
FROM (((Register INNER JOIN People ON Register.CSC_PMLOS_Lead_Assigned = People.People) INNER JOIN BusinessUnit
ON Register.Business_Unit = BusinessUnit.BusinessUnit) INNER JOIN (IMAC INNER JOIN COP ON IMAC.COP = COP.COP)
ON Register.Register = COP.Register) INNER JOIN IMACType ON IMAC.IMACType = IMACType.IMACType
WHERE IMAC.ServiceDate >=1-1-Convert(Datetime, '4445-01-01 00:00:00', 102)
AND IMAC.ServiceDate < Convert(Datetime, '4446-01-01 00:00:00', 102)
AND (IMAC.Estimate)=0


Its returning the same error i mentioned above :

Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.

Regards

sinapra
  #6 (permalink)  
Old September 1st, 2004, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

Well, it's probably somewhere in here:

IMAC.ServiceDate >=1-1-Convert(Datetime, '4445-01-01 00:00:00', 102)
AND IMAC.ServiceDate < Convert(Datetime, '4446-01-01 00:00:00', 102)

What are you trying to do with this:

IMAC.ServiceDate >=1-1-Convert(Datetime, '4445-01-01 00:00:00', 102)

What is the 1-1-; is that jan 1? What are you trying to limit based on?

Brian
  #7 (permalink)  
Old September 1st, 2004, 11:26 PM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

ok here is the code which depicts which one relates to what and how it is put in the query.

StartDate = "1/1/" & Session("rptyear")
EndDate = DateAdd("yyyy", 1, "1/1/" & Session("rptyear"))
Where first '1' is month.

The query is written in MSAccess version and I am finding it difficult to convert it in SQL version. Can you provide me the solution. The error is at the same zone where you mentioned, near convert, though it shows this error :

Arithmetic overflow error converting expression to data type datetime.

Thanks and regards

sinapra
  #8 (permalink)  
Old September 2nd, 2004, 07:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

This is how SQL would query a date:

where ServiceDate >= '1/1/2004'

You probably can do 1-1-2004 too. But you can't do it the way you were doing. Try:

IMAC.ServiceDate >= '1/1/' + Convert(varchar, <date value in param or value in dynamic query>)
AND IMAC.ServiceDate < '1/1/' + Convert(varchar, <end date>)

Something like that. Again, the date's may or can be formatted differently.

Brian
  #9 (permalink)  
Old September 3rd, 2004, 12:24 AM
Authorized User
 
Join Date: Aug 2004
Location: Seunderabad, Andhra Pradesh, India.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to sinapra Send a message via MSN to sinapra Send a message via Yahoo to sinapra
Default

Brian, that is ok but its something different, I am not sure why it would return
"Arithmetic overflow error converting expression to data type datetime".
Since I already coded and have writted the condition, still it shows the following error, is it anywhere related to the date format I have shown or is it MSSQL error.

Regarsds

sinapra


Similar Threads
Thread Thread Starter Forum Replies Last Post
simple arithmetic overflow vbscript handy Classic ASP Basics 32 March 31st, 2009 04:25 PM
Arithmetic overflow error gregalb Reporting Services 1 June 24th, 2008 02:42 PM
Odd arithmetic overflow error.... 7racer SQL Server 2000 4 May 12th, 2006 09:17 AM
arithmetic overflow problem flyfish SQL Server 2000 4 July 12th, 2005 04:37 PM
Arithmetic overflow error converting expression to spikey SQL Server 2000 1 June 24th, 2003 07:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.