Click here to Skip to main content
15,896,730 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i am getting this error when trying to view a selected request.
ALTER PROC [dbo].[Proc_View_Exp_Req_Detail] --934
@Exp_Requisition_Id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT D.Exp_Detail_id
,CASE(D.Working_Type)
WHEN 1 THEN 'Working In HQ'
WHEN 2 THEN 'Working In Ex-HQ'
End AS 'Working_Type'
,ER.EXP_REQUISITION_ID
,D.Expense_Head_Id
,EH.EXPENSE_HEAD AS 'ExpenseHead'
,D.With_Bill AS 'WithBill'

,C1.City_Name AS 'FromLocation'
,C2.City_Name AS 'ToLocation'
,CASE(D.Expense_Head_Id) WHEN 12 THEN (CONVERT(VARCHAR(11),D.From_Date,103)+' '+COALESCE(Start_Time,'') +' - ' + COALESCE(End_Time,''))
ELSE (CONVERT(VARCHAR(11),D.From_Date,103)+' '+COALESCE(Start_Time,''))
END AS 'FromDate'

,(CONVERT(VARCHAR(11),D.To_Date,103)+' '+COALESCE(End_Time,'')) AS 'ToDate'
,TM.MODE_DETAIL AS 'ModeOfTravel',TC.CLASS_DETAIL AS'ClassOfTravel'
,D.KM,D.Bill_Number AS 'BillNo'
,D.Total_Amount AS 'Actual_Amount'
,D.Cleared_Amount AS 'Approved_Amount'
,D.OPE,
D.Remarks
,(SELECT SUM(A.OPE) FROM EXPENSE_REQUISITION_DETAIL A WHERE A.Exp_Requisition_Id = ER.EXP_REQUISITION_ID) AS 'TOTAL_OPE'
,(SELECT SUM(A.Cleared_OPE) FROM EXPENSE_REQUISITION_DETAIL A WHERE A.Exp_Requisition_Id = ER.EXP_REQUISITION_ID) AS 'TOTAL_APPROVED_OPE'
,CASE(D.Is_Canceled)
WHEN 'TRUE' THEN 'Yes'
ELSE 'No'
END AS 'Is_Canceled'
,D.Cleared_Amount
,D.Cleared_OPE AS 'APPROVED_OPE'
,(SELECT SUM(Cleared_Amount) FROM EXPENSE_REQUISITION_DETAIL
WHERE Exp_Requisition_Id = @Exp_Requisition_Id) AS 'APPROVED_AMOUNT_SUM'
,(SELECT SUM(Total_Amount) FROM EXPENSE_REQUISITION_DETAIL
WHERE Exp_Requisition_Id = @Exp_Requisition_Id) AS 'ACTUAL_AMOUNT_SUM'
,D.IsSpecialRequisition
,(SELECT TOP 1 'Y' FROM Exp_Requistion_Amount_Tracking WHERE exp_requisition_id = ER.EXP_REQUISITION_ID AND Exp_Detail_id = D.Exp_Detail_id AND LEN(Remarks)>0) AS 'IsHaveRemarks'
FROM EXPENSE_REQUISITION ER JOIN EXPENSE_REQUISITION_DETAIL D
ON ER.EXP_REQUISITION_ID = D.Exp_Requisition_Id
JOIN EXPENSE_HEAD EH
ON EH.EXPENSE_HEAD_ID = D.Expense_Head_Id
JOIN City_Master C1
ON C1.City_Id = D.From_Location_Id
LEFT JOIN City_Master C2
ON C2.City_Id = D.To_Location_Id

LEFT JOIN TRANSPORT_MODE TM
ON TM.MODE_ID = D.Mode_Id
LEFT JOIN TRAVEL_CLASS TC
ON TC.CLASS_ID = D.Class_Id
WHERE ER.Exp_Requisition_Id = @Exp_Requisition_Id
ORDER BY D.From_Date ,convert(char(5),cast(Start_Time as datetime),108)
-- ORDER BY FromDate
END

i am getting this error
"
C#
[SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.]
   Requisition_ViewExpenseRequisition.ReadExpenseHeadDetail(String expRequisitionId) in d:\\Requisition\ViewExpenseRequisition.aspx.cs:170
   Requisition_ViewExpenseRequisition.Page_Load(Object sender, EventArgs e) in d:\\Requisition\ViewExpenseRequisition.aspx.cs:28
   System.Web.UI.Control.LoadRecursive() +71
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178

"
Posted
Updated 26-Dec-15 0:03am
v2
Comments
cigwork 26-Dec-15 6:25am    
GIGO.
1. You have a table that is holding date/time information as varchar rather than datetime. This is generally considered to be a less than sensible decision.
2. You (probably) have a UI that doesn't validate input dates adequately.

This has allowed someone to enter a "date" value that isn't a date, a date outside the min/max range for a SQL date or a valid date in a format for some locale other than that of the DB server.

The only solution at this point is to identify the offending row(s) and attempt to convert the "bad" data to "good". You then need to go back and see if whether or not it is possible to update the application to prevent the problem happening again.

It's probably going belly-up on the EXPENSE_REQUISITION_DETAIL.From_Date or To_Date column(s).

,CASE(D.Expense_Head_Id) WHEN 12 THEN (CONVERT(VARCHAR(11),D.From_Date,103)+' '+COALESCE(Start_Time,'') +' - ' + COALESCE(End_Time,''))
ELSE (CONVERT(VARCHAR(11),D.From_Date,103)+' '+COALESCE(Start_Time,''))
END AS 'FromDate'

,(CONVERT(VARCHAR(11),D.To_Date,103)+' '+COALESCE(End_Time,'')) AS 'ToDate'
,TM.MODE_DETAIL AS 'ModeOfTravel',TC.CLASS_DETAIL AS'ClassOfTravel'
7045Jeegnesh 30-Dec-15 7:33am    
please use IsNull function i.e convert Varchar into date Time Only When There Is value

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900