Solved

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

Posted on 2009-03-06
1,072 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.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    In this article I will describe the Backup & Restore 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.
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
    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…

    933 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

    16 Experts available now in Live!

    Get 1:1 Help Now