我在演员表面做错了吗？ [英] Am i doing wrong in cast
问题描述
- 前端场景
文本框就在那里。在搜索我放入文本框的价值的基础上，我将在gridview中获得,,,,
.... aspx场景...... 。
--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">
...... ....
- 搜索按钮< br $> b $ b
..........
--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 --------------------------- ----------------
-----------------------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 +''
因为我在页面上收到错误...如果我调试我得到了意外的数据库错误。,,,
--im声明
- @Claim_amount十进制（18,2）以上
----------------------- - - - - - - - - - 摘要 - - - - - - - - - - - - - - - - ------------------------
1）财产
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
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
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
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.
---------------------------------------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.
推荐答案
If (@Claim_amount<> '' and @Claim_amount<>'0')
BEGIN
SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Claim_Amount ='+'' + @Claim_amount +''
END
If @Claim_Amount is indeed a number, why are you surrounding it with quotes? Try
If @Claim_Amount is indeed a number, why are you surrounding it with quotes? Try
If (@Claim_amount<> '' and @Claim_amount<>'0')
BEGIN
SET @sSQL = @sSQL + ' AND tbl_Reim_Detail.Claim_Amount =' + @Claim_amount
END
If that doesn’t work, reply with the final value of @sSQL before you execute it.
Hogan
If that doesn't work, reply with the final value of @sSQL before you execute it.
Hogan
这篇关于我在演员表面做错了吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
