strange DB Error

All Forums >> [Web Development] >> ASP and Database

Message

Bnugent -> strange DB Error (1/30/2008 20:59:23)

Using DRW on spooky diet and SQL Server...

Here is my code:

<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IV00102.ITEMNMBR, Max(IV00101.ITEMDESC) AS DESCRIPTION, Max(IV00102.PRIMVNDR) AS VENDOR, Max(IV00101.CURRCOST) AS COST, Max(IV00102.QTYONHND) AS QTYONHAND, Max(IV00102.ATYALLOC) AS QTYALLOCATED, Max(IV00102.QTYONORD) AS ONORDER, Sum(SOP30300.QUANTITY/12) AS monthQTYSOLD, Sum(SOP30300.QUANTITY/48) AS weekQTYSOLD, Sum(SOP30300.QUANTITY/360) AS dayQTYSOLD  FROM IV00102 INNER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBR WHERE IV00101.ITMGEDSC = 'report' AND  (SOP30200.DOCDATE > DATEADD(d, - 180, GETDATE())) AND SOP30200.SOPTYPE=3 AND SOP30300.SOPTYPE=3  GROUP BY IV00102.ITEMNMBR ORDER BY IV00102.ITEMNMBR ASC;"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=10 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="SBM"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=9
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%total= FP_FieldVal(fp_rs,"QTYONHAND") - FP_FieldVal(fp_rs,"QTYALLOCATED")%>
<%monthsupply = total / (FP_FieldVal(fp_rs,"monthQTYSOLD"))%>
<tr>
          <td><font size="2">
<%=FP_FieldVal(fp_rs,"ITEMNMBR")%></font> </td>
          <td><font size="2">
<%=FP_FieldVal(fp_rs,"DESCRIPTION")%></font> </td>
          <td><font size="2">
<%=FP_FieldVal(fp_rs,"VENDOR")%></font> </td>
          <td align="center"><font size="2">
<%=FormatNumber(FP_FieldVal(fp_rs,"monthQTYSOLD"),0)%></font> </td>
          <td align="center"><font size="2">
<%=FormatNumber(FP_FieldVal(fp_rs,"weekQTYSOLD"),0)%></font> </td>
          <td align="center"><font size="2">
<%=FormatNumber(FP_FieldVal(fp_rs,"dayQTYSOLD"),0)%></font> </td>
          <td align="center"><font size="2">
<%=(total)%></font> </td>
          <td align="center"><font size="2">
<%=FormatNumber((monthsupply),2)%></font> </td>
          <td align="center"><font size="2">
<%=FP_FieldVal(fp_rs,"ONORDER")%></font> </td>

</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->

The error I get directly above my results says:
Microsoft VBScript runtime error '800a0006'
Overflow
/inventorycheck.asp, line 67

And more importantly, it show me only the first 44 results...It simply stops after that...

When I delete the ASP code:
<%total= FP_FieldVal(fp_rs,"QTYONHAND") - FP_FieldVal(fp_rs,"QTYALLOCATED")%>
<%monthsupply = total / (FP_FieldVal(fp_rs,"monthQTYSOLD"))%>

I dont get this error and I get all the results, of course my fields don't calculate...

I am at a quandry...anyone have any ideas...

by the way, line 67 in my code is:
<%monthsupply= total / (FP_FieldVal(fp_rs,"monthQTYSOLD"))%>

Spooky? Rdouglass? Anyone? Bueller? Bueller? Bueller?

Spooky -> RE: strange DB Error (1/30/2008 21:28:08)
Instead of performing math, I would trouble shoot that line and write real values to see what is occuring eg: <%monthsupply= (FP_Field(fp_rs,"monthQTYSOLD"))%> etc... <td align="center"><font size="2"> <%="sum = "&total &" / "& monthsupply%></font> </td> Then this should show you where the data error occurs. Note you should use "FP_Field" and also ensure nulls are "0"

Bnugent -> RE: strange DB Error (1/30/2008 22:39:03)
quote: <%="sum = "&total &" / "& monthsupply%></ Spooky...Sorry, but I don't understand...I tried the code above and got syntax errors... I really only know enough to be dangerous but it is unusual that it always stops at the same line item/record (the 44th in fact)... If I take out the division field, it shows all results...the issue is, I need to display that field... can you explain exactly wht I should do? Sorry..

rdouglass -> RE: strange DB Error (1/30/2008 22:59:31)
quote: If I take out the division field, it shows all results Does monthQTYSOLD ever equal 0? That would give a 'divide by zero' error. Maybe the 45th result is 0? Just a quick thought. Hope it helps.

Bnugent -> RE: strange DB Error (1/31/2008 9:02:58)
quote: Does monthQTYSOLD ever equal 0? That would give a 'divide by zero' error. Maybe the 45th result is 0? You are exactly right! I discovered this last night. But my laptop battery was dead and a I didnt have a charger and my wife kept yelling at me to go to sleep [:D]. So what are my options, basically, this proucts has initial balance of 0, and 0 allocated and 0 sales history...for now I have deleted, but is there a workaround for future reference in a situation like this?

rdouglass -> RE: strange DB Error (1/31/2008 9:33:07)
quote: <%monthsupply= total / (FP_FieldVal(fp_rs,"monthQTYSOLD"))%> How about something like this: <% IF FP_FieldVal(fp_rs,"monthQTYSOLD") = 0 Then monthsupply = 0 Else monthsupply= total / (FP_FieldVal(fp_rs,"monthQTYSOLD")) End If%> Or something to that effect. That any help?

Bnugent -> RE: strange DB Error (1/31/2008 9:39:20)
your the man!!!! Seems so simple now...

Spooky -> RE: strange DB Error (1/31/2008 14:15:45)
Yep - thats what I was trying to discover by writing the values ;-)

Page: [1]

0.078125