[edwardel]

Hi,

Im new to ASP and i have been getting errors when trying to get the client to get data from an sql text file. Here is my code. I have taken over some code from a former programmer. And he left this comment in the notes. So im switching to a RDB now and it says this must be modified. Please Help

My error occurs whError Type:ere it says:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Oracle][ODBC][Rdb]%RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime -SQL-F-DATCONERR, Data conversion error for string '1112-00-50' -COSI-F-IVTIME, invalid date or time
/PICKLER/common/connect.asp, line 201

'We want the information for the whole month until the report date so our first parameter should be the first of the month.
'We do not need to prepend a "#" because the cold mill is currently in an SQL database. When it switches to the RDB this must be modified.
ParamList(0) = Month(reportDate) & "/1/" & Year(reportDate)
ParamList(1) = reportDate
InfoHandle = RunQuery("ColdMillProduction", "C:\Inetpub\wwwroot\pickler\SQL\cmproTurnInfoQuery.SQL", ParamList)





<%@ LANGUAGE="VBScript" %>
<% Option Explicit %>
<%Response.Buffer = True %>
<%
'**********
'Page name: daily_summary_cm.asp and daily_summary_cm_code.asp
'Date Last Modified: 2/14/2005
'Purpose: This page displays production statistics for the cold mill and both pickler lines for each turn in the report day, the day total, the
' month through previous day, and the month up to and including the report date
'Parent frame: index.asp
'Directly Includes: connect.asp
'Required Page Input: none
'Optional Page Input: cookie SEL_DATE: holds the value of the desired report date. If empty, the page defaults to the current day
'**********
%>
<!--#include file="../common/connect.asp"-->

<%

Dim ProductionStartTime
Dim ProductionEndTime
Dim DayStartTime

'**********
'Purpose: Takes a cDate type object and returns just the time without the date
'Inputs: myDate - the cDate object whose time will be extracted
'Returns: the time in HH:MM AMPM format
'**********
Public Function dateToTime(myDate)

Dim myHour
Dim myMin
Dim myAMORPM
myHour = DatePart("h", myDate)
myMin = DatePart("n", myDate)
myAMORPM = "AM"
If myHour > 12 Then
myHour = myHour - 12
myAMORPM = "PM"
End If

If myHour = 12 Then
myAMORPM = "PM"
End If

If myHour = 0 Then
myHour = 12
End If

If myMin < 10 Then
myMin = "0" & myMin
End If

dateToTime = myHour & ":" & myMin & " " & myAMORPM

End Function

'**********
'Purpose: Determines the actual start and end times for a given production date
'Inputs: Line - the line you want the times for
' ProductionStartDate - the date of the first day in the period you want the actual production times for
' productionEndDate - the date of the last day in the period you want the actal production times for.
'Returns: None
'**********
Public Function getProductionTimes(Line, productionStartDate, productionEndDate)
Dim TurnTimeHandle
Dim TempDate
Dim ParamList(3)
ParamList(0) = "#" & productionStartDate
ParamList(1) = "#" & productionEndDate
ParamList(2) = Line


TurnTimeHandle = RunQuery("PicklerProduction", "C:\Inetpub\wwwroot\pickler\SQL\pklrptTurnTimeQuery.sql", ParamList)

If RecSet(TurnTimeHandle).EOF And RecSet(TurnTimeHandle).BOF Then

ProductionStartTime = productionStartDate & " 12:00:00 AM"
ProductionEndTime = productionEndDate & " 11:59:59 PM"
DayStartTime = productionEndDate & " 12:00:00 AM"

Else
While Not RecSet(TurnTimeHandle).EOF
If RecSet(TurnTimeHandle)("TURN") = 1 And ProductionStartTime = "" And cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME")) > cDate(RecSet(TurnTimeHandle)("ENDING_PRODUCTION_DATE_TIME")) Then
TempDate = cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME"))

ProductionStartTime = cDate(DateAdd("n", -1, DateAdd("d", -1, cDate(Month(TempDate) & "/" & Day(TempDate) & "/" & Year(TempDate) & " " & dateToTime(TempDate)))))
Else
If ProductionStartTime = "" Then
TempDate = cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME"))
ProductionStartTime = DateAdd("n", -1, cDate(Month(TempDate) & "/" & Day(TempDate) & "/" & Year(TempDate) & " " & dateToTime(TempDate)))
End If
End If

If RecSet(TurnTimeHandle)("TURN") = 1 And cDate(RecSet(TurnTimeHandle)("PRODUCTION_DATE")) = cDate(productionEndDate) And DayStartTime = "" And cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME")) > cDate(RecSet(TurnTimeHandle)("ENDING_PRODUCTION_DATE_TIME")) Then
TempDate = cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME"))

DayStartTime = cDate(DateAdd("n", -1, DateAdd("d", -1, cDate(Month(TempDate) & "/" & Day(TempDate) & "/" & Year(TempDate) & " " & dateToTime(TempDate)))))
Else
If DayStartTime = "" And cDate(RecSet(TurnTimeHandle)("PRODUCTION_DATE")) = cDate(productionEndDate) Then
TempDate = cDate(RecSet(TurnTimeHandle)("STARTING_PRODUCTION_DATE_TIME"))
DayStartTime = DateAdd("n", -1, cDate(Month(TempDate) & "/" & Day(TempDate) & "/" & Year(TempDate) & " " & dateToTime(TempDate)))
End If
End If

TempDate = cDate(RecSet(TurnTimeHandle)("ENDING_PRODUCTION_DATE_TIME"))
If TempDate < ProductionStartTime Then
TempDate = Now
End If
ProductionEndTime = DateAdd("n", 1, cDate(Month(TempDate) & "/" & Day(TempDate) & "/" & Year(TempDate) & " " & dateToTime(TempDate)))
RecSet(TurnTimeHandle).MoveNext
Wend
'Response.Write("<script language=""javascript"">window.alert(" & ProductionStartTime & " " & ProductionEndTime & ")</script>")
'Response.Flush
End If

CloseRS(TurnTimeHandle)
End Function

'**********
'Purpose: takes the doubly subscripted array storing all the pickler data and generates the code for one row of an html table, displaying one
' of the array's row's data in that row.
'Inputs: array: the doubly subscripted array holding all the pickler data. rows represent line 4 and 5, columsn represent different periods.
' row: the number of the row you want to generate the html row for, 0 for 4 line, 1 for 5 line
' name: the name of the data type to be displayed in the html row
'Returns: one row in an html table dispalying the name of the data type and all data of that type for one line.
'**********
Function genRowP(array, row, name)
Dim tempStr
tempStr = "<tr><td><b>" & name & "</td>"
Dim i
For i = 0 to 5
tempStr = tempStr & "<td>" & FormatNumber(array(row, i), 1)
If Right(name, 1) = "%" Then tempStr = tempStr & "%"
tempStr = tempStr & "</td>"
Next
tempStr = tempStr & "</tr>"
genRowP = tempStr
End Function


'**********
'Ppurpose: takes a single subscripted array storing cold mill information and generates the code for one row of an html table
'Inputs: array: the array holding the cold mill data
' name: the name of the data type to be displayed
'Returns: one row of an html table displaying the name of the data type and all data of that type
'**********
Function genRow(array, name)
Dim tempStr
tempStr = "<tr><td><b>" & name & "</td>"
Dim i
For i = 0 to 5
tempStr = tempStr & "<td>" & FormatNumber(array(i), 1)
If Right(name, 1) = "%" Then tempStr = tempStr & "%"
tempStr = tempStr & "</td>"
Next
tempStr = tempStr & "</tr>"
genRow = tempStr
End Function


'Each statistic will have 6 elements in an array. 0 is used for the month through the report day, 1, 2, and 3 are used for the 11-7, 7-3, and 3-11\
'turns, 4 is used for the sum for the whole day, and 5 is used for month to date including the report date.

Dim Hours(6)
Dim DelayMinutes(6)
Dim CoilsIn(6)
Dim CoilsOut(6)
Dim Tons(6)
Dim LinearFeet(6)
Dim AvgWidth(6)
Dim Turns(6)

Dim TonsPerHour(6)
Dim FeetPerRollHourEff(6)
Dim TonsPerCoil(6)
Dim FeetPerCoil(6)
Dim PoundsPerFoot(6)
Dim AverageGauge(6)
Dim CSA(6)
Dim Availability(6)

Dim reportDate

If Request.Cookies("SEL_DATE") <> "" And IsDate(Request.Cookies("SEL_DATE")) Then
reportDate = Request.Cookies("SEL_DATE")
Else
reportDate = Date
End If

If cDate(reportDate) >= Date Then Response.Write("Warning: You have chosen to generate a report for a day which still has turns remaining.<br>The information in the report may not yet be finalized.")

Dim ParamList(2)
Dim InfoHandle
'We want the information for the whole month until the report date so our first parameter should be the first of the month.
'We do not need to prepend a "#" because the cold mill is currently in an SQL database. When it switches to the RDB this must be modified.
ParamList(0) = Month(reportDate) & "/1/" & Year(reportDate)
ParamList(1) = reportDate
InfoHandle = RunQuery("ColdMillProduction", "C:\Inetpub\wwwroot\pickler\SQL\cmproTurnInfoQuery.SQL", ParamList)

Dim Turn
While Not RecSet(InfoHandle).EOF
'If the crew number is 0, the cm was on a down turn.
If RecSet(InfoHandle)("CREW") <> 0 Then
'If the date of the turn in the recset is the date of the report, store the appropriate data in column 1-3 (depending on turn) and 4 for the whole day
If cDate(RecSet(InfoHandle)("PRODUCTION_DATE")) = cDate(reportDate) Then
Turn = RecSet(InfoHandle)("TURN")
Hours(Turn) = RecSet(InfoHandle)("TURN_LENGTH_HOURS")
Hours(4) = Hours(4) + RecSet(InfoHandle)("TURN_LENGTH_HOURS")
DelayMinutes(Turn) = RecSet(InfoHandle)("ACTUAL_DELAY_TIME_HOUR") * 60 + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_MIN") + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_SEC") / 60
DelayMinutes(4) = DelayMinutes(4) + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_HOUR") * 60 + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_MIN") + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_SEC") / 60
CoilsIn(Turn) = RecSet(InfoHandle)("NUM_COILS_ON")
CoilsIn(4) = CoilsIn(4) + RecSet(InfoHandle)("NUM_COILS_ON")
CoilsOut(Turn) = RecSet(InfoHandle)("NUM_COILS_OFF")
CoilsOut(4) = CoilsOut(4) + RecSet(InfoHandle)("NUM_COILS_OFF")
Tons(Turn) = RecSet(InfoHandle)("TURN_POUNDS") / 2000
Tons(4) = Tons(4) + RecSet(InfoHandle)("TURN_POUNDS") / 2000
LinearFeet(Turn) = RecSet(InfoHandle)("TURN_FOOTAGE") / 1000
LinearFeet(4) = LinearFeet(4) + RecSet(InfoHandle)("TURN_FOOTAGE") / 1000
Turns(Turn) = 1
Turns(4) = Turns(4) + 1
Else
'If the date of the turn in the recset is not the report date, store the data only in column 0 for month through previous day
Hours(0) = Hours(0) + RecSet(InfoHandle)("TURN_LENGTH_HOURS")
DelayMinutes(0) = DelayMinutes(0) + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_HOUR") * 60 + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_MIN") + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_SEC") / 60
CoilsIn(0) = CoilsIn(0) + RecSet(InfoHandle)("NUM_COILS_ON")
CoilsOut(0) = CoilsOut(0) + RecSet(InfoHandle)("NUM_COILS_OFF")
Tons(0) = Tons(0) + RecSet(InfoHandle)("TURN_POUNDS") / 2000
LinearFeet(0) = LinearFeet(0) + RecSet(InfoHandle)("TURN_FOOTAGE") / 1000
Turns(0) = Turns(0) + 1
End If
'Always store the data in column 5 for month to date.
Hours(5) = Hours(5) + RecSet(InfoHandle)("TURN_LENGTH_HOURS")
DelayMinutes(5) = DelayMinutes(5) + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_HOUR") * 60 + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_MIN") + RecSet(InfoHandle)("ACTUAL_DELAY_TIME_SEC") / 60
CoilsIn(5) = CoilsIn(5) + RecSet(InfoHandle)("NUM_COILS_ON")
CoilsOut(5) = CoilsOut(5) + RecSet(InfoHandle)("NUM_COILS_OFF")
Tons(5) = Tons(5) + RecSet(InfoHandle)("TURN_POUNDS") / 2000
LinearFeet(5) = LinearFeet(5) + RecSet(InfoHandle)("TURN_FOOTAGE") / 1000
Turns(5) = Turns(5) + 1
End If

RecSet(InfoHandle).MoveNext
Wend

'Calculate all the formula dependant information for each column
Dim ActFtPerRHR
Dim PlanFtPerRHR
For Turn = 0 to 5
If CoilsIn(Turn) <> 0 Then
FeetPerCoil(Turn) = LinearFeet(Turn) * 1000 / CoilsIn(Turn)
TonsPerCoil(Turn) = Tons(Turn) / CoilsIn(Turn)
End If

If Hours(Turn) <> 0 Then
TonsPerHour(Turn) = Tons(Turn) / Hours(Turn)
Availability(Turn) = (Hours(Turn) - DelayMinutes(Turn)/60) / Hours(Turn) * 100

ActFtPerRHR = (LinearFeet(Turn) * 1000 /(Hours(Turn) - (DelayMinutes(Turn) + ((CoilsOut(Turn)-CoilsIn(Turn))*.75))/60))
If FeetPerCoil(Turn) > 12000 Then
PlanFtPerRHR = 5 * FeetPerCoil(Turn) + 129500
Else
PlanFtPerRHR = 12 * FeetPerCoil(Turn) + 46000
End If

FeetPerRollHourEff(Turn) = ActFtPerRHR / PlanFtPerRHR * 100
End If

If LinearFeet(Turn) <> 0 Then
PoundsPerFoot(Turn) = Tons(Turn) * 2000 / (LinearFeet(Turn) * 1000)
CSA(Turn) = PoundsPerFoot(Turn) / 12 / .273
End If



Next

CloseRS(InfoHandle)

Dim tableHead
tableHead = "<table border=8><tr height=""30"" bgcolor=808080><td valign=""center"" width=160><b>#3 ColdMill</b></td><td width=80><b>Through Previous Day</b></td><td width=80><b>11-7</b></td><td width=80><b>7-3</b></td><td width=80><b>3-11</b></td><td width=80><b>Day</b></td><td width=80><b>MTD</b></td></tr>"

'These doubly subscripted arrays are divided into columns the same way the first set of arrays were, but they now contain 2 rows, 0 for 4 line and 1 for 5 line
Dim pklTons(2,6)
Dim pklHours(2,6)
Dim pklDelayMinutes(2,6)
Dim pklTurns(2,6)
Dim pklTonsPerHour(2,6)
Dim pklAvailability(2,6)

Dim pklParamList(3)
Dim pklDelayParamList(3)
pklParamList(0) = "#" & ParamList(0)
pklParamList(1) = "#" & ParamList(1)
Dim line
Dim pklInfoHandle
Dim pklDelayHandle
For line = 0 to 1
pklParamList(2) = "40" & (line + 4)
pklInfoHandle = RunQuery("PicklerProduction", "C:\Inetpub\wwwroot\pickler\SQL\pklrptTurnsQuery.sql", pklParamList)

'Because the production date field of the pickler pro database does not update correctly for delays, we must figure out the exact start and end
'time of the turn and search based on that.
getProductionTimes pklParamList(2), ParamList(0), ParamList(1)
'Response.Write(pklParamList(0) & " " & pklParamList(1) & " " & pklParamList(2) & "<br>")
'Response.Write(ProductionStartTime & " " & DayStartTime & " " & ProductionEndTime & "<br>")
pklDelayParamList(0) = "#" & ProductionStartTime
pklDelayParamList(1) = "#" & ProductionEndTime
pklDelayParamList(2) = pklParamList(2)
pklDelayHandle = RunQuery("PicklerProduction", "C:\Inetpub\wwwroot\pickler\SQL\pklrptDelayQuery.sql", pklDelayParamList)


While Not RecSet(pklInfoHandle).EOF
If cDate(RecSet(pklInfoHandle)("PRODUCTION_DATE")) = cDate(reportDate) Then
Turn = RecSet(pklInfoHandle)("TURN")
pklTons(line, Turn) = RecSet(pklInfoHandle)("EXIT_WEIGHT") / 2000 + RecSet(pklInfoHandle)("EXIT_SCRAP_COIL_WEIGHT") / 2000
pklTons(line, 4) = pklTons(line, 4) + RecSet(pklInfoHandle)("EXIT_WEIGHT") / 2000 + RecSet(pklInfoHandle)("EXIT_SCRAP_COIL_WEIGHT") / 2000
pklHours(line, Turn) = RecSet(pklInfoHandle)("TURN_DURATION_HOURS")
pklHours(line, 4) = pklHours(line, 4) + RecSet(pklInfoHandle)("TURN_DURATION_HOURS")
'pklDelayMinutes(line, Turn) = RecSet(pklInfoHandle)("TOTAL_DELAY_MINUTES")
'pklDelayMinutes(line, 4) = pklDelayMinutes(line, 4) + RecSet(pklInfoHandle)("TOTAL_DELAY_MINUTES")
pklTurns(line, Turn) = 1
pklTurns(line, 4) = pklTurns(line, 4) + 1
Else
pklTons(line, 0) = pklTons(line, 0) + RecSet(pklInfoHandle)("EXIT_WEIGHT") / 2000 + RecSet(pklInfoHandle)("EXIT_SCRAP_COIL_WEIGHT") / 2000
pklHours(line, 0) = pklHours(line, 0) + RecSet(pklInfoHandle)("TURN_DURATION_HOURS")
'pklDelayMinutes(line, 0) = pklDelayMinutes(line, 0) + RecSet(pklInfoHandle)("TOTAL_DELAY_MINUTES")
pklTurns(line, 0) = pklTurns(line, 0) + 1
End If
pklTons(line, 5) = pklTons(line, 5) + RecSet(pklInfoHandle)("EXIT_WEIGHT") / 2000 + RecSet(pklInfoHandle)("EXIT_SCRAP_COIL_WEIGHT") / 2000
pklHours(line, 5) = pklHours(line, 5) + RecSet(pklInfoHandle)("TURN_DURATION_HOURS")
'pklDelayMinutes(line, 5) = pklDelayMinutes(line, 5) + RecSet(pklInfoHandle)("TOTAL_DELAY_MINUTES")
pklTurns(line, 5) = pklTurns(line, 5) + 1
RecSet(pklInfoHandle).MoveNext
Wend

While Not RecSet(pklDelayHandle).EOF
Turn = RecSet(pklDelayHandle)("TURN")
If cDate(RecSet(pklDelayHandle)("DELAY_STARTING_DATE_TIME")) > cDate(DayStartTime) Then
pklDelayMinutes(line, Turn) = pklDelayMinutes(line, Turn) + RecSet(pklDelayHandle)("DELAY_DURATION_MINUTES")
pklDelayMinutes(line, 4) = pklDelayMinutes(line, 4) + RecSet(pklDelayHandle)("DELAY_DURATION_MINUTES")
Else
pklDelayMinutes(line, 0) = pklDelayMinutes(line, 0) + RecSet(pklDelayHandle)("DELAY_DURATION_MINUTES")
End If
pklDelayMinutes(line, 5) = pklDelayMinutes(line, 5) + RecSet(pklDelayHandle)("DELAY_DURATION_MINUTES")
RecSet(pklDelayHandle).MoveNext
Wend
CloseRS(pklInfoHandle)
Next

For line = 0 to 1
For Turn = 0 to 5
If pklHours(line, Turn) <> 0 Then
pklTonsPerHour(line, Turn) = pklTons(line, Turn) / pklHours(line, Turn)
pklAvailability(line, Turn) = (pklHours(line, Turn) - pklDelayMinutes(line, Turn)/60) / pklHours(line, Turn) * 100
End If
Next
Next

Dim pklTableStart


pklTableStart = "<br><table border=8><tr height=""30"" bgcolor=808080><td valign=""center"" width=160><b>#4 & #5 Pickler</b></td><td width=80><b>Through Previous Day</b></td><td width=80><b>11-7</b></td><td width=80><b>7-3</b></td><td width=80><b>3-11</b></td><td width=80><b>Day</b></td><td width=80><b>MTD</b></td></tr></br>"
%>