Results 1 to 7 of 7

Thread: Insert Statement error

  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Insert Statement error

    Hi All,

    I am trying to run an insert statement and getting an error.

    Insert statement:

    insert into conv_owner (name,street_num,
    street_direction,street_name,street_type,street_un it,
    address2,city,state,city_state,zip,wphone,inservic e,
    db_name,table_name)
    select "Facility's Owner",null,null,null,null,null,null,null,
    null,null,null,"Owner's Telephone Number",inservice,
    'BKFoodProtection.mdb','FP_Master'
    from fp_master

    Error message:
    Server: Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    Any idea why? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I think wphone is a numbering and you are inserting the chararcter value "Owner's Telephone Number"

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Both fields are alphanumeric.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    please post the schema of the table.

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    CREATE TABLE [CONV_OWNER] (
    [OWNER_ID] [DM_RECORDID] NOT NULL ,
    [NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STREET_NUM] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STREET_DIRECTION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STREET_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STREET_TYPE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STREET_UNIT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CITY] [DM_CITY] NULL ,
    [STATE] [DM_STATE] NULL ,
    [CITY_STATE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ZIP] [DM_ZIP] NULL ,
    [WPHONE] [DM_PHONE] NULL ,
    [INSERVICE] [numeric](1, 0) NULL ,
    [DB_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TABLE_NAME] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]
    GO

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    check all the datatypes of DM_* like DM_STATE
    DM_ZIP
    DM_PHONE
    DM_RECORDID


    make sure the the datatype of the column INSERVICE in the source is same as target table. If not use CONVERT or CAST

  7. #7
    Join Date
    Mar 2004
    Posts
    84
    Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •