Am i doing wrong in cast

See more:

SQL

server

--front end scenario

text box is there. on basis of search of value that i put in text box ,,,i will get that in gridview,,,,

....aspx scenario....

<asp:ObjectDataSource ID="objUserClaimList_Approval" runat="server" SelectMethod="GetAllRecordsForClaimApproval"
                       OnSelecting="objUserClaimList_Approval_Selecting" TypeName="TMS.Business.INT_New_Claim"
                       OnUpdating="objUserClaimList_Approval_Updating" UpdateMethod="UpdateForClaimApproval">

..........




--on search button

 protected void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                    gvClaimApproval.DataSourceID = "objUserClaimList_Approval";
                    objUserClaimList_Approval.Select();
                    gvClaimApproval.DataBind();
                    gvClaimApproval.PageIndex = 0;
             }
            catch (Exception ex)
            {
                LogError.WriteError(ex);
            }
            finally
            {

            }
        }

----debug and reach objUserClaimList_Approval.Select(); f11

protected void objUserClaimList_Approval_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            try
            {
                e.InputParameters["PageNo"] = 1;
                e.InputParameters["PageSize"] = 10000;
                TMS.API.Reimbursement.VO_Reimbursement_SC objClaimListApproval_SC = new    TMS.API.Reimbursement.VO_Reimbursement_SC();
                objClaimListApproval_SC.Hub_ID = ddl_Hub.SelectedItem.Value;
                objClaimListApproval_SC.UserID = txtUserID.Text;
                objClaimListApproval_SC.Claim_amount = Convert.ToDecimal(txt_Claim_Amount.Text);

              ...
              ...
           

---defined property in presentation layer
 public decimal Claim_amount
        {
            get;
            set;
        }

--get the xml and pass it to stored procedure...my concern is claim amount which is 100-<vo_reimbursement_sc><company_id>1</company_id><pagetodisplay>1</pagetodisplay><recordsperpage>10000</recordsperpage><claim_amount>100</claim_amount><hub_id>136</hub_id><userid /><reim_token_id /><firstname /><lastname /><route_id /><status>-1</status></vo_reimbursement_sc>

-----------------------sql-------------------------------------------

ALTER PROCEDURE [dbo].[USP_USER_CLAIM_APPROVAL_LIST]                  
(            
 @XML_PARAM XML,            
 @TotalRecords int Output            
)            
AS  
BEGIN             
SET NOCOUNT ON               
             
 DECLARE @PageToDisplay   int,            
   @RecordsPerPage       int,            
   @Reim_Token_ID    int,            
   @FromDate     datetime,              
   @ToDate      datetime,            
   @UserID Varchar(100),        
   @FirstName varchar(50),        
   @LastName Varchar(50),        
   @Status int,        
   @Company_ID     int,            
   @Hub_ID   int,        
   @Route_ID int,  
   @Own_Side  bit,            
   @Approval_Status varchar(10),  
   @DocHandle            int,            
   @sSQL  varchar(max) ,
   @Claim_amount decimal(18,2)     



EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML_PARAM               
 SELECT  @PageToDisplay       = PageToDisplay,            
   @RecordsPerPage      = RecordsPerPage,            
   @Reim_Token_ID  = Reim_Token_ID,             
   @FromDate          =FromDate,             
   @ToDate  = ToDate,          
@UserID=UserID,        
@FirstName=FirstName,        
@LastName=LastName,        
@Status=Status,        
@Company_ID=Company_ID,        
 @Route_ID=Route_ID,           
   @Hub_ID   = Hub_ID,  
   @Own_Side=Own_Side ,  
   @Approval_Status=Approval_Status ,
   @Claim_amount=Claim_amount            
 FROM OPENXML (@DocHandle, '/VO_Reimbursement_SC', 2)                 
 WITH (            
  PageToDisplay         int,             
  RecordsPerPage        int,            
     Reim_Token_ID    int,            
  FromDate     datetime,              
  ToDate      datetime,            
  UserID varchar(100),        
FirstName varchar(50),        
LastName varchar(50),        
Status int,          
  Company_ID     int,    
Route_ID INT,            
  Hub_ID                int      ,  
  Own_Side bit,  
  Approval_Status varchar(10),
  Claim_amount decimal(18,2) 
   )             
            
CREATE TABLE #tmpUserClaimApprovalList           
(            
Reim_Token_ID     int,        
EmpName varchar(200),        
Facility_Name varchar(500),              
Route_ID int,          
Description varchar(2000),        
ShiftDate varchar(30),        
Reason varchar(100),        
Claim_Amount decimal(18,2),        
Issued_Amount decimal(18,2),    
Approver_Name varchar(250),        
Remarks varchar(5000),    
EMPCLAIMHISTORY varchar(max),  
Print_Status bit ,  
Own_Side bit,  
Approval_Status varchar(10) ,  
Created_By varchar(250),

        
)            
            
CREATE TABLE #User_ClaimApprovalList_Temp            
(            
   Reim_Token_ID     int,         
EmpName varchar(200),        
Facility_Name varchar(500),         
   Route_ID   varchar(20),            
   Description varchar(2000),        
ShiftDate varchar(30),        
Reason varchar(100),        
Claim_Amount decimal(18,2),        
Issued_Amount decimal(18,2),    
Approver_Name varchar(250),        
Remarks Varchar(5000),    
  Print_Status bit,    
  EMPCLAIMHISTORY varchar(max),  
  Own_Side bit,  
  Approval_Status varchar(10),  
   Created_By varchar(250),  
   RowNumber     int,            
   TotalRecords    int       
)             
BEGIN           
BEGIN TRY          
BEGIN TRANSACTION          
--print @Status  
IF (@Status=1 or @Status=2 or @Status=-1)  
BEGIN   
--print 1         
SET @sSQL ='INSERT INTO #tmpUserClaimApprovalList
(Reim_Token_ID,
EmpName,
Facility_Name,
Route_ID,
Description,
ShiftDate,
Reason,
Claim_Amount,
Issued_Amount,
Approver_name,
Remarks,
Print_Status,
EMPCLAIMHISTORY,
Own_Side,
Approval_Status,
Created_By)            
SELECT    
 tbl_Reim_Detail.Reim_Token_ID,
 tbl_User_master.UserID+'+'''<br>'''+'+ tbl_User_master.First_Name+'' '+'''+ tbl_user_master.Last_Name+'''+''' as EmpName ,
 Facility_Name +'+'''<hr><span style="background-color:Gray">'''+'+ isnull((select top 1 addr
ess_detail from tbl_route_Detail where route_id=isnull(tbl_Reim_Token.Route_ID,'''') and userid=isnull(tbl_User_master.UserID,'''')),'''')+'''+''' as Facility_Name,        
   tbl_Reim_Token.Route_ID, tbl_Reim_Token.Description,convert(varchar,convert(datetime,tbl_Reim_Token.Shift_Date,110),103)+'+''':'''+'+ Shift_Time as ShiftDate,
tbl_ReasonForReim.ReasonForReim_Value,        
isnull(tbl_Reim_Detail.Claim_Amount,0),
isnull(tbl_Reim_Detail.Issued_Amount,0) As Issued_Amount,
(select RD.Approved_By+'+'''<br>'''+'+ UM.First_Name+'' ''+ UM.Last_Name from tbl_reim_detail RD inner join tbl_user_master UM on UM.userId=RD.Approved_By wher
e RD.Reim_token_id=tbl_Reim_Detail.Reim_Token_ID) as Approver,        
tbl_reim_detail.Remarks,  
Print_Status, (select [dbo].[fnc_CreateHTML_Employee_Claim_History](isnull(tbl_User_master.UserID,''0''),tbl_reim_token.Route_ID)) as EMPCLAIMHISTORY,Own_Side, case Reim_Status_ID when 1 then ''Pending'' when 2 then ''Approved'' when 3 then ''Inactive'' w
hen 4 then ''Delete'' when 5 then ''Referback'' end,  
(select RT.Created_By+'+'''<br>'''+'+ UM.First_Name+'' ''+ UM.Last_Name from tbl_Reim_Token RT inner join tbl_user_master UM on UM.userId=RT.Created_By where RT.Reim_token_id=tbl_Reim_Detail.Reim_Token_ID) as Created_By   FROM         tbl_ReasonForReim wi
th (nolock) INNER JOIN        
                      tbl_Reim_Detail with (nolock) ON tbl_ReasonForReim.ReasonForReim_ID = tbl_Reim_Detail.Reason_ID         
inner join tbl_Reim_Token with (nolock)on tbl_reim_token.Reim_token_id=tbl_reim_detail.Reim_token_id        
inner join tbl_Route_header RH with (nolock) on RH.Route_id=tbl_reim_token.Route_id  
inner join tbl_facility with (nolock) on tbl_facility.Facility_ID=tbl_Reim_Detail.Facility_ID        
inner join tbl_user_master with (nolock) on tbl_user_master.userid=tbl_reim_detail.userid     
where tbl_Reim_Detail.Inactive= 0 '        
  
if((@FromDate<>'') and (@ToDate<>''))        
BEGIN        
 SET @sSQL=@sSQL+' AND RH.Appointment_Datetime between convert(datetime,'''+CAST(@FromDate AS VARCHAR)+''',101) And convert(datetime,'''+CAST(@ToDate As Varchar)+''',101)'            
    END        
            
    If (@Hub_ID <>-1)            
  BEGIN            
   SET @sSQL = @sSQL + ' AND tbl_Reim_token.Hub_ID = ' + cast( @Hub_ID as varchar)            
                     
 END            
   
 if(@status= -1)  
 BEGIN  
 SET @sSQL=@sSQL + ' AND tbl_Reim_Detail.Reim_Status_id in(1,2,3,4,5)'  
 END  
  else if (@status=2)    --Approved  
BEGIN        
 SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Reim_Status_id  =2 '  --Approved by transport         
END         
else if (@status=1)--Pending  
BEGIN  
SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Reim_Status_id in(1,5) '  --Pending for Approval         
END  
  
 If (@Company_ID <> '')            
  BEGIN            
   SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Company_ID = ' + cast(@Company_ID as varchar)            
                     
 END            
            
 If (@Reim_Token_ID <>'')            
  BEGIN            
   SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.REIM_TOKEN_ID = ' + cast(@Reim_Token_ID as varchar)            
                     
 END            
            
 If ((@Route_ID <> '') and (len(@Route_ID)>0))    
  BEGIN            
   SET @sSQL = @sSQL + ' AND tbl_Reim_Token.ROUTE_ID ='+'''' +  cast(@ROUTE_ID as varchar)+''''          
                     
 END         
 If (@UserID <> '')            
  BEGIN            
   SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.UserID ='+'''' +  cast(@UserID as varchar)+''''          
                     
 END         
If (@FirstName<> '')              
  BEGIN              
   SET @sSQL = @sSQL + ' AND tbl_User_Master.First_Name LIKE ''%' + @FirstName+'%'''           
                    
   END              
 If (@LastName<> '')              
  BEGIN              
   SET @sSQL = @sSQL + ' AND tbl_User_Master.Last_Name LIKE ''%' + @LastName + '%'''            
                    
   END                   
          
          
 If (@Claim_amount<> '' and @Claim_amount<>'0')              
  BEGIN              
   SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Claim_Amount ='+'' +  @Claim_amount +''     
                    
   END   
 


----sp continues,,,,but am i doing right,,,, the above step ------
---- SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Claim_Amount ='+'' +  @Claim_amount +''     

because i am getting error on page...if i debug i get as unexpected database error.,,,
--i m declaring
-- @Claim_amount decimal(18,2) above

-----------------------------------------summary-------------------------------------------------------
1)property

public decimal Claim_amount
       {
           get;
           set;
       }

2)getting the value in front end to pass as xml

TMS.API.Reimbursement.VO_Reimbursement_SC objClaimListApproval_SC = new TMS.API.Reimbursement.VO_Reimbursement_SC();
 objClaimListApproval_SC.Claim_amount = Convert.ToDecimal(txt_Claim_Amount.Text);

3)sql variable declare

@Claim_amount decimal(18,2)  above 

4)sql dynamic sql...claim amount is optional....if its not there then display all records...else

If (@Claim_amount<> '' and @Claim_amount<>'0')
 BEGIN
  SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Claim_Amount ='+'' +  @Claim_amount +''

  END

---------------------------------------help me--------------------------------------------------
--i am getting error here,,,
objClaimListApproval_SC.Claim_amount = Convert.ToDecimal(txt_Claim_Amount.Text);

-----------input string not in correct format...
in presentation layer i made it to string....

and changed here

objClaimListApproval_SC.Claim_amount = txt_Claim_Amount.Text;

----now if i dont put 100 as claim amount,,,i dont get error and records with 100 is not displaying

--if i put nothing in text box...i get error on page...

--i get
Error while fetching data from the database. Unexpected Database Error.