Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Exclamation Unanswered: Using the convert function HELP!

    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 = '',
    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

    When the trigger goes off 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
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by cle5cap
    I write using the SQL ODBC driver from software into a SLQ table called
    How do you use the convert function to change the data before the update? Thank You!
    Check this out CONVERT
    Joydeep

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is that you are trying to set a MONEY column to a zero-length string value (''). Try setting it to NULL or 0 instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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