Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: inserting dates

  1. #1
    New to the CF scene
    Join Date
    Aug 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inserting dates

    Hi, I'm having a bit of a problem.... I can insert dates using queryanalyzer in the mm/dd/yyyy format. But when I insert the date from an asp page, i get an error. I have changed the settings of my site so that the format asp expects is mm/dd/yyyy, so I don't know why it's not working I am using <date%> to get todays date and display it in a text box. Other details are then entered and the form submitted, this is when I get the error :

    Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

    [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

    I've tried using the convert function to do an explicit conversion, but it makes no difference

    The offending line is:

    Code:
    SQLStmt = "INSERT into Item (cat_ref, descrip, date_added, date_last_pricecheck, cat_type, contract, cost_price, installation_charge, commercial_markup, supplier_name, supplier_phone, notes) "
    SQLStmt = SQLStmt & "VALUES ('" & cat_ref & "', '" & descrip & "', '" & date_added & "', '" & date_last_pricecheck & "', '" & cat_type & "', '" & contract & "', CONVERT(smallmoney,'" & cost_price & "'),CONVERT(smallmoney,'" & installation_charge & "'),CONVERT(smallmoney,'" & commercial_markup & "'), '"& supplier_name & "', '" & supplier_phone & "', '" & notes & "')"
    Any help very much appreciated

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Make sure the date is getting into the SQL you're building by adding:

    response.write SQLStmt
    response.flush

    just before the call to the database. Examine the SQL displayed and make sure the dates are properly there.
    Check out the Forum Search. It's the short path to getting great results from this forum.


  •  

    Posting Permissions

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