CODEWITHSUNDEEP
c#sql-server
Jaishankar
Jaishankar

Reputation: 168

Conversion failed when converting the varchar value to datatype int in SQL Server

I am getting an error

Conversion failed when converting the varchar value 'ORB2.0000000001' to data type int.

Can you please help me resolve this issue? The stored procedure is working fine in development but on another machine I am getting that error

ALTER PROCEDURE [dbo].[EMR_GetReportData](
    @FromDate DATETIME
    , @ToDate DATETIME
    , @Code varchar(20) = NULL
    , @ReportType INT
) AS
BEGIN
    IF(@ReportType = 7)
    BEGIN
        SELECT DISTINCT
            PAI.OP_NO
            , PAI.Pat_Name
            , CONVERT(VARCHAR, PVD.Visit_Date, 106) AS DOA
            , DM.Name AS DrName
            , D.department
        FROM
            dbo.Pat_Info PAI
            INNER JOIN dbo.Pat_Visit_Details PVD ON PAI.OP_NO = PVD.OP_No
            INNER JOIN dbo.departments D ON PVD.Department_id = D.department_id
            INNER JOIN dbo.DoctorMain DM ON PVD.DrName COLLATE Latin1_General_CI_AI = DM.Code
        WHERE
            PVD.[Type] = 'OPD'
            AND PVD.Visit_Date BETWEEN @FromDate AND @ToDate
    END
    ELSE IF(@ReportType = 8)
    BEGIN
        SELECT
            PAI.OP_NO
            , PAI.Pat_Name
            ,  CONVERT(VARCHAR, PVD.DOA, 106) AS DOA
            , DM.Name AS DrName
            , D.department
        FROM
            dbo.Pat_Info PAI
            INNER JOIN dbo.Pat_Visit_Details PVD ON PAI.OP_NO = PVD.OP_No
            INNER JOIN dbo.departments D ON PVD.Department_id = D.department_id
            INNER JOIN dbo.DoctorMain DM ON PVD.DrName COLLATE Latin1_General_CI_AI = DM.Code
        WHERE
            PVD.[Type] = 'IPD'
            AND PVD.DOA BETWEEN @FromDate AND @ToDate
    END
    ELSE IF(@ReportType = 9)
    BEGIN
        SELECT
            D.Cr_No
            , PAI.Pat_Name
            , D.Code
            , CONVERT(VARCHAR, D.Visit_Date, 106) AS Visit_Date
            , D.[Description]
        FROM
            dbo.Diagnosis1 D
            INNER JOIN dbo.Pat_Info PAI ON D.Cr_No = PAI.OP_NO
        WHERE
            D.Code = @Code
            AND D.Visit_Date BETWEEN @FromDate AND @ToDate
    END
END

Upvotes: 1

Views: 1060

Answers (1)

Vikas Rana
Vikas Rana

Reputation: 1999

Your varchar value contains some charecters ORB in ORB2.0000000001 which can't be converted in to int. to remove the error remove these charecters from your report string and then convert to int.
You can convert any string to integer only when it contain int only (0,1,2,,3,4,5,6,7,8,9)
Also if you want decimal in your value than use float data type. see here some datatype manual of c#

Upvotes: 1

Related Questions