none
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value "data'' to data type int.

    Question

  • Hello everybody,

    Im getting the following error when I try to Query my database. I just want to pull a list of users, their location, company, Id, firstname, lastname,etc. Below 
    Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'data' to data type int.


    Here is my query. any Help would be great. The tables lender_companyname(varchar(100)) and the lenderlocationname(varchar(100))

    Select lender_user_id AS LenderId,SRP,first_name AS FirstName, last_name AS LastName,email,lender_companyname, lender_locationname, account_status   from lenders A, lender_location B, lender_company C where A.lender_company_id = C.lender_companyname AND A.lender_location_id = B.lender_locationname AND account_status=('A') OR account_status=('L') OR account_status=('P') order by 1

    Wednesday, April 24, 2013 7:12 PM

Answers

  • Hi

    Without knowing the actual structure of the tables involved, it appears that your JOIN condition is wrong. 

    You have Company_id = companyName and Location_Id = LocationName.    What are the data types of the Id and the name columns?  maybe you have a different column to join on?  Is there a Company_ID column in Lender_Company table?  Is there a Location_Id in the Lender_LocationName table?

    If you can post your DDL for the tables we could help ensure the query is correct. 

    - will


    • Proposed as answer by Naomi NModerator Wednesday, April 24, 2013 7:32 PM
    • Edited by Will Durning Wednesday, April 24, 2013 7:34 PM added bits
    • Marked as answer by SClarke.SC Wednesday, April 24, 2013 7:40 PM
    Wednesday, April 24, 2013 7:30 PM
  • SELECT 
    lender_user_id AS LenderId,
    SRP,
    first_name AS FirstName, 
    last_name AS LastName,
    email,
    lender_companyname, 
    lender_locationname, 
    account_status   
    FROM 
    lenders A, lender_location B, lender_company C 
    WHERE 
    A.lender_company_id = C.lender_companyname
    AND A.lender_location_id = B.lender_locationname
    AND account_status=('A') 
    OR account_status=('L') 
    OR account_status=('P') 
    ORDER BY 1

    Hi Clarke,

    See the above query with highlighted part.  The problem lies here only.

    Could you please confirm what is the datatype for lender_company_id lender_location_id ? I am sure these are of INT data type. If so, then you cant compare int data type with varchar.  

    A.lender_company_id <-- int 
    C.lender_companyname  <-- Varchar

    Solution: 

    1> Convert int data type to varchar  e.g.    CONVERT(varchar(100), A.lender_company_id)
    2> I don't feel A.lender_company_id and C.lender_companyname will be having same kind data as the first one is ID i.e. int type of data and the other is Name i.e. textual type of data. So check for proper column to map.

    Please let me know, if this doesn't solve your problem. For better solution..please provide table structure with some sample data.


    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    • Marked as answer by SClarke.SC Wednesday, April 24, 2013 7:48 PM
    Wednesday, April 24, 2013 7:44 PM

All replies

  • Hi

    Without knowing the actual structure of the tables involved, it appears that your JOIN condition is wrong. 

    You have Company_id = companyName and Location_Id = LocationName.    What are the data types of the Id and the name columns?  maybe you have a different column to join on?  Is there a Company_ID column in Lender_Company table?  Is there a Location_Id in the Lender_LocationName table?

    If you can post your DDL for the tables we could help ensure the query is correct. 

    - will


    • Proposed as answer by Naomi NModerator Wednesday, April 24, 2013 7:32 PM
    • Edited by Will Durning Wednesday, April 24, 2013 7:34 PM added bits
    • Marked as answer by SClarke.SC Wednesday, April 24, 2013 7:40 PM
    Wednesday, April 24, 2013 7:30 PM
  • Thanks Will that help. I had to join _id to _id instead of id_companyname

    Wednesday, April 24, 2013 7:41 PM
  • SELECT 
    lender_user_id AS LenderId,
    SRP,
    first_name AS FirstName, 
    last_name AS LastName,
    email,
    lender_companyname, 
    lender_locationname, 
    account_status   
    FROM 
    lenders A, lender_location B, lender_company C 
    WHERE 
    A.lender_company_id = C.lender_companyname
    AND A.lender_location_id = B.lender_locationname
    AND account_status=('A') 
    OR account_status=('L') 
    OR account_status=('P') 
    ORDER BY 1

    Hi Clarke,

    See the above query with highlighted part.  The problem lies here only.

    Could you please confirm what is the datatype for lender_company_id lender_location_id ? I am sure these are of INT data type. If so, then you cant compare int data type with varchar.  

    A.lender_company_id <-- int 
    C.lender_companyname  <-- Varchar

    Solution: 

    1> Convert int data type to varchar  e.g.    CONVERT(varchar(100), A.lender_company_id)
    2> I don't feel A.lender_company_id and C.lender_companyname will be having same kind data as the first one is ID i.e. int type of data and the other is Name i.e. textual type of data. So check for proper column to map.

    Please let me know, if this doesn't solve your problem. For better solution..please provide table structure with some sample data.


    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    • Marked as answer by SClarke.SC Wednesday, April 24, 2013 7:48 PM
    Wednesday, April 24, 2013 7:44 PM