Results 1 to 7 of 7

Thread: Convert Varchar to Money type

  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Convert Varchar to Money type

    I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed:
    JobNumber varchar 50
    Weight real 4
    FreightCost varchar 8
    TrackingNumber varchar 50
    Shipmethod varchar 50
    VOIDID varchar 3

    I then have a trigger set to update the PACKAGE table as followed
    CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
    FOR INSERT

    AS

    BEGIN
    UPDATE PACKAGE
    SET WEIGHT = inserted.WEIGHT,
    FREIGHTCOST = inserted.FREIGHTCOST,
    TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    COMMENTS = inserted.SHIPMETHOD
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'N'

    UPDATE PACKAGE
    SET WEIGHT = '',
    FREIGHTCOST = '0.00',
    TRACKINGNUMBER = '',
    COMMENTS = 'UPS VOID'
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'Y'

    END

    The format of the PACKAGE table is as followed
    Jobnumber varchar 50
    FreightCost money 8
    TrackingNumber varchar 50
    Comments varchar 2000
    Weight real 4

    I am getting the following error
    ---------------------------
    Microsoft SQL-DMO (ODBC SQLState: 42000)
    ---------------------------
    Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query.
    ---------------------------
    OK
    ---------------------------

    How do you use the convert function to change the data before the update? Thank You!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try this:

    UPDATE PACKAGE
    SET WEIGHT = '',
    FREIGHTCOST = convert(money, '0.00'),
    TRACKINGNUMBER = '',
    COMMENTS = 'UPS VOID'
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'Y'

  3. #3
    Join Date
    Mar 2006
    Posts
    4
    I still get the same error the error is in this part

    UPDATE PACKAGE
    SET WEIGHT = inserted.WEIGHT,
    FREIGHTCOST = inserted.FREIGHTCOST,
    TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    COMMENTS = inserted.SHIPMETHOD
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'N'

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Then try this also:

    UPDATE PACKAGE
    SET WEIGHT = inserted.WEIGHT,
    FREIGHTCOST = convert(money, inserted.FREIGHTCOST),
    TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    COMMENTS = inserted.SHIPMETHOD
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'N'

  5. #5
    Join Date
    Mar 2006
    Posts
    4
    Thank you very much!! This works GREAT

  6. #6
    Join Date
    Mar 2006
    Posts
    1

    Thumbs up

    Hi,
    i wanna help u. please try this:
    CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
    FOR INSERT

    AS

    BEGIN
    UPDATE PACKAGE
    SET WEIGHT = inserted.WEIGHT,
    FREIGHTCOST =convert(money,inserted.FREIGHTCOST),
    TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    COMMENTS = inserted.SHIPMETHOD
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'N'

    UPDATE PACKAGE
    SET WEIGHT = '',
    FREIGHTCOST = convert(money,'0.00'),
    TRACKINGNUMBER = '',
    COMMENTS = 'UPS VOID'
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'Y'

    END

    i hope this is can help u. if it still problem please give me the error. gud luck!

    Quote Originally Posted by cle5cap
    I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed:
    JobNumber varchar 50
    Weight real 4
    FreightCost varchar 8
    TrackingNumber varchar 50
    Shipmethod varchar 50
    VOIDID varchar 3

    I then have a trigger set to update the PACKAGE table as followed
    CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
    FOR INSERT

    AS

    BEGIN
    UPDATE PACKAGE
    SET WEIGHT = inserted.WEIGHT,
    FREIGHTCOST = inserted.FREIGHTCOST,
    TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    COMMENTS = inserted.SHIPMETHOD
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'N'

    UPDATE PACKAGE
    SET WEIGHT = '',
    FREIGHTCOST = '0.00',
    TRACKINGNUMBER = '',
    COMMENTS = 'UPS VOID'
    FROM PACKAGE
    INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE inserted.VOIDID = 'Y'

    END

    The format of the PACKAGE table is as followed
    Jobnumber varchar 50
    FreightCost money 8
    TrackingNumber varchar 50
    Comments varchar 2000
    Weight real 4

    I am getting the following error
    ---------------------------
    Microsoft SQL-DMO (ODBC SQLState: 42000)
    ---------------------------
    Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query.
    ---------------------------
    OK
    ---------------------------

    How do you use the convert function to change the data before the update? Thank You!

  7. #7
    Join Date
    Mar 2006
    Posts
    4
    This works great thank you very much!

Posting Permissions

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