Solved

Conversion failed when converting the varchar value '-1"' to data type int.

Posted on 2009-03-06
1,071 Views
Last Modified: 2012-06-21
My CF developer couldn't pass me NULLS so we decided on -1 (a value) that would never present itself in our db.  I tested the sp with the -1 values and everything worked well but when she called the sp, received the following error:

Conversion failed when converting the varchar value '-1"' to data type int.

A portion of my code is below...

Thx

ALTER PROCEDURE [dbo].[usp_Search2]

@Program varchar (50) = -1,
@Project varchar (255) = NULL,
@Center varchar (50) = NULL,
@Facility_Name varchar (255)= NULL,
@BuildNos varchar (255) = NULL,
SELECT
f.MainID,
p.Program,
p.Project,
p.Center,
f.Facility_Name,
f.Building_Nos,
From...
WHERE
(ISNULL(@Program,-1) = -1 or p.Program = @Program)    
and (ISNULL (@Project, -1)= -1 or p.Project = @Project)
and (ISNULL (@Center,-1) = -1 or p.Center = @Center)
and (ISNULL (@Facility_Name,-1)= -1 or f.Facility_Name = @Facility_Name)
and (ISNULL (@BuildNos, -1)= -1  or f.Building_Nos = @BuildNos)
0
Question by:Glen_D
    5 Comments
     
    LVL 8

    Expert Comment

    by:Lotok
    Why do you need it set as a varchar?
    Could you not use:


    @Program tinyint = -1,
    

    Open in new window

    0
     
    LVL 39

    Expert Comment

    by:Pratima Pharande
    Check whther all have datatype varchar

    p.Program,
    p.Project,
    p.Center,
    f.Facility_Name,
    f.Building_Nos,

    any one of this field amy have datatype int
    0
     

    Author Comment

    by:Glen_D
    The input from the front screen (for the parameters) comes from lookup tables in varchar format; my default value was NULL but CF has an issue with NULLS so we decied to use the -1.  I checked all my fields and everything is set to varchar; SQL is trying to convert the -1 to an int.
    0
     
    LVL 39

    Accepted Solution

    by:
    ALTER PROCEDURE [dbo].[usp_Search2]

    @Program varchar (50) = '-1',
    @Project varchar (255) = NULL,
    @Center varchar (50) = NULL,
    @Facility_Name varchar (255)= NULL,
    @BuildNos varchar (255) = NULL,
    SELECT
    f.MainID,
    p.Program,
    p.Project,
    p.Center,
    f.Facility_Name,
    f.Building_Nos,
    From...
    WHERE
    (ISNULL(@Program,'-1') = '-1' or p.Program = @Program)    
    and (ISNULL (@Project, '-1')= '-1' or p.Project = @Project)
    and (ISNULL (@Center,'-1') = '-1' or p.Center = @Center)
    and (ISNULL (@Facility_Name,'-1')= '-1' or f.Facility_Name = @Facility_Name)
    and (ISNULL (@BuildNos, '-1')= '-1'  or f.Building_Nos = @BuildNos)
     
    Tags:
    MS SQL 2005/2008
    0
     

    Author Comment

    by:Glen_D
    wow...that looks so simple; by quoting the -1 values, I'll prevent SQL from automatically trying to convert?

    Thx
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    680 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    29 Experts available now in Live!

    Get 1:1 Help Now