Solved

Microsoft OLE DB Provider for SQL Server error '80040e57'

Posted on 2006-07-24
6
1,565 Views
Last Modified: 2008-01-09
Microsoft OLE DB Provider for SQL Server error '80040e57'

Arithmetic overflow error converting numeric to data type numeric.

/USERSELECT.asp, line 98

*********************************
<%
Option Explicit
DIM btnVAL
DIM SRC
DIM sConnStr
DIM OBJdbConn
DIM ACInfo        'ALL CUSTOMER INFO
DIM AOInfo            'ALL ORDER INFO
DIM VLastName
DIM VFirstName
DIM VAddress
DIM VCity
DIM VState
DIM VZip
DIM VPhone
DIM VEmailadd
DIM VITEM1
DIM VQTY1
DIM VPRICE1
DIM VTAX
DIM VSUB
DIM VDISC
DIM VTOTALITEMS
DIM VDATEEXP
DIM VDATERET
DIM VDATEORD
DIM VCLERKID
DIM VCASHRET
DIM VCASHREC
DIM VLOCATION
DIM VSTATUS
DIM VORDERNUM
DIM SQLQuery
DIM RsCustomerList

' LINE 36
%>


<HTML>
<HEAD>
<title>FIND CUSTOMER INFO</title>
</HEAD>
<BODY>


<H3>FIND CUSTOMER INFO - START</H3>
<TABLE WIDTH=600 BORDER=0>
<TR>
<TD VALIGN=TOP COLSPAN=3>
<FONT SIZE=2>
<BODY BGCOLOR="#FFFFFF">
<FORM METHOD="POST" ACTION="USERSELECT.asp">

Phone# <INPUT TYPE="text" NAME="Phone2" SIZE=15 MAXLENGTH=15 VALUE="937"> <BR> <BR> <br>


<%
IF Request.form("btnVAL") <>"" THEN
 SRC = 0
 sConnStr = 0
 OBJdbConn = 0
 ACInfo = 0       'ALL CUSTOMER INFO
 AOInfo      = 0      'ALL ORDER INFO
 VLastName = 0
 VFirstName = 0
 VAddress = 0
 VCity = 0
 VState = 0
 VZip = 0
 set VPHONE      = request.form("PHONE2")
 VEmailadd = 0
 VITEM1 = 0
 VQTY1 = 0
 VPRICE1 = 0
 VTAX = 0
 VSUB = 0
 VDISC = 0
 VTOTALITEMS = 0
 VDATEEXP = 0
 VDATERET  = 0
 VDATEORD = 0
 VCLERKID = 0
 VCASHRET = 0
 VCASHREC = 0
 VLOCATION = 0
 VSTATUS = 0
 VORDERNUM = 0
 SQLQuery = 0
'LINE 91
 src = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\PHILLIPS_data.mdf"
 sConnStr = "Provider=SQLOLEDB;Server=pdcserver;Database=PHILLIPS;" & _
 "UID=sa;PWD=;"
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open  sConnStr
  ' SQLQuery = "SELECT * FROM CUSTINFO WHERE PHONE LIKE '93727490%'"
    SQLQuery = "SELECT * FROM CUSTINFO WHERE (PHONE = "& vphone &")"
Set RsCustomerList = OBJdbConn.Execute(SQLQuery)  
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=1>
<!-- BEGIN column header row for Customer Table-->
<TR CLASS=thead>
   <TD>FIRST NAME</TD>
   <TD>LAST NAME</TD>
   <TD>ADDRESS</TD>
   <TD>CITY</TD>
   <TD>STATE</TD>
   <TD>ZIP</TD>
   <TD>PHONE</TD>
   <TD>EMAIL ADDRESS</TD>
</TR>

<!--Display ADO Data from Customer Table-->


<% IF RSCUSTOMERLIST.EOF THEN
      RESPONSE.WRITE "THE RECORD DOES NOT EXIST. WOULD YOU LIKE TO ADD A NEW RECORD?" %>
      <A HREF="/addrecord.asp">YES</A><BR><BR>  
 
<% ELSE
      RESPONSE.WRITE "CUSTOMER FOUND. PROCEED TO PLACE NEW ORDER?"%>
      <A HREF="/NEWORDER.ASP">YES</A><BR><BR>  

 <%  END IF
      Do While Not RScustomerList.EOF
   %>
<TR CLASS=tbody>
  <TD> <%= RSCustomerList("FNAME")%> </TD>
  <TD> <%= RScustomerList("LNAME")%></TD>
  <TD> <%= RScustomerList("ADDRESS")%> </TD>
  <TD> <%= RScustomerList("CITY")%> </TD>
  <TD> <%= RScustomerList("STATE")%> </TD>
  <TD> <%= RScustomerList("ZIP")%> </TD>
  <TD> <%= RScustomerList("PHONE")%> </TD>
  <TD> <%= RScustomerList("EMAILADD")%> </TD>
</TR>
<!--Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
Set RScustomerList = Nothing
OBJdbConn.Close
Set OBJdbConn = Nothing
end if
%>
<INPUT TYPE="SUBMIT" NAME="btnval" VALUE="FIND RECORD">      

</TABLE>
</FORM>
</BODY>
</HTML>



0
Comment
Question by:Nmagsaysay
  • 3
  • 3
6 Comments
 

Author Comment

by:Nmagsaysay
ID: 17168059
Help any ideas??  
0
 
LVL 23

Accepted Solution

by:
apresto earned 500 total points
ID: 17168080
Hi Nmagsaysay,

Could you point out line 98

Apresto
0
 

Author Comment

by:Nmagsaysay
ID: 17168575
I'm a noob to ASP/SQL databases.  I've inherited this project.  The original developer is nowhere to be found.  The DB was originally run on a win2k workstation with SQL 2000.  The system/server developed hardware problems and I have the task of moving the DB to a windows XP pro box with SQL 2000.  (This DB runs on an intranet & it is not accessed publicly)

When I launch the asp page to perform a query I get this error.  I’m stuck & I don’t know where to look.  


Microsoft OLE DB Provider for SQL Server error '80040e57'

Arithmetic overflow error converting numeric to data type numeric.

/USERSELECT.asp, line 98



I belive that the code in question is below

'LINE 91

'LINE 92
src = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\PHILLIPS_data.mdf"
'LINE 93
sConnStr = "Provider=SQLOLEDB;Server=pdcserver;Database=PHILLIPS;" & _
'LINE 94
"UID=sa;PWD=;"
'LINE 95
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
'LINE 96
OBJdbConn.Open  sConnStr
'LINE 97  
' SQLQuery = "SELECT * FROM CUSTINFO WHERE PHONE LIKE '93727490%'"
'LINE 98
SQLQuery = "SELECT * FROM CUSTINFO WHERE (PHONE = "& vphone &")"
'LINE 99
Set RsCustomerList = OBJdbConn.Execute(SQLQuery)  
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 23

Expert Comment

by:apresto
ID: 17168593
ok change it to this:

SQLQuery = "SELECT * FROM CUSTINFO WHERE (PHONE = '"& vphone &"')"

and see if it makes a difference
0
 

Author Comment

by:Nmagsaysay
ID: 17169300
That was the trick!! Thanks
0
 
LVL 23

Expert Comment

by:apresto
ID: 17169352
No problem, it was because you were'nt using single quotes in the SQL statement around the variable.  With single quotes means its a text value, without means its numeric - something worth remembering.

Good luck and thanks

Apresto
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now