Professional Web Applications Themes

DateTime problem - Macromedia ColdFusion

Hi, I posted a question a few days ago in the Getting Started board but never got an answer. But I have figured out where my problem lies-- it has something to do with inputting dates and converting them to a format MSSQL will accept. I used the DateFormat along with the CreateODBCDate function and it has worked wonderfully except in one case and I cannot figure out why this one in particular won't work. The user inputs a date into a text field and then I pass it into a stored procedure. If the date is not formatted properly, ...

  1. #1

    Default DateTime problem

    Hi,
    I posted a question a few days ago in the Getting Started board but never
    got an answer. But I have figured out where my problem lies-- it has something
    to do with inputting dates and converting them to a format MSSQL will accept. I
    used the DateFormat along with the CreateODBCDate function and it has worked
    wonderfully except in one case and I cannot figure out why this one in
    particular won't work.

    The user inputs a date into a text field and then I pass it into a stored
    procedure. If the date is not formatted properly, the stored procedure doesn't
    work and when I test the page it either says 'Error Executing Database Query.
    Invalid data for CFSQLTYPE CF_SQL_INTEGER' or The page cannot be displayed :
    There is a problem with the page you are trying to reach and it cannot be
    displayed. The difference I think depends on how the date is inputted. Ideally
    I want the user to be able to type in something like 12-16-05 (mm-dd-yy). This
    works on other pages, but not for this one, although the columns in the SQL
    database are all datetime format.

    This one page is the only problem I'm having... if you can help me in any way,
    I will be officially done with my project! Thanks in advance!



    Here is my code for the page that doesn't work

    <CFSTOREDPROC procedure="dbo.Find_RenterID" datasource="Monkey">
    <CFPROCPARAM type="IN" dbvarname="First_name"
    value="#Session.sessFirstName#" cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="Last_name"
    value="##Session.sessLastName##" cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="OUT" dbvarname="RenterID" variable="out_RenterID"
    cfsqltype="CF_SQL_INTEGER">
    </CFSTOREDPROC>
    <CFSTOREDPROC procedure="dbo.edit_renter" datasource="Monkey">
    <CFPROCPARAM type="IN" dbvarname="RenterID" value="#out_RenterID#"
    cfsqltype="CF_SQL_INTEGER">
    <CFPROCPARAM type="IN" dbvarname="FirstName" value="#FirstName#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="LastName" value="#LastName#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="UTEID" value="#UTEID#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="PhoneNumber" value="#PhoneNumber#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="email" value="#email#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="GraduationDate"
    value="#DateFormat(CreateODBCDate(FORM.GraduationD ate), "mm-dd-yy")#"
    cfsqltype="CF_SQL_TIMESTAMP">
    </CFSTOREDPROC>
    <cfquery name="NewRenterList" datasource="Monkey">
    SELECT *
    FROM dbo.View_all_renter_info
    ORDER BY LastName ASC</cfquery>


    Yet this page with a different stored procedure works perfectly and the year
    doesn't even have to be typed in correctly:

    <CFSTOREDPROC procedure="dbo.Schedule_Rental" datasource="Monkey">
    <CFPROCPARAM type="IN" dbvarname="Renter_FristName"
    value="#Session.sessSchedFirstName#" cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="Renter_LastName"
    value="#Session.sessSchedLastName#" cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="IN" dbvarname="RequestDate_TimeOut"
    value="#DateFormat(CreateODBCDateTime(FORM.Request TimeOut), "mm-dd-yyyy
    hhh:mm:ss")#" cfsqltype="CF_SQL_TIMESTAMP">
    <CFPROCPARAM type="IN" dbvarname="RequestDate_TimeIn"
    value="#DateFormat(CreateODBCDate(FORM.RequestTime In), "mm-dd-yyyy
    hhh:mm:ss")#" cfsqltype="CF_SQL_TIMESTAMP">
    <CFPROCPARAM type="IN" dbvarname="Show" value="#Show#"
    cfsqltype="CF_SQL_VARCHAR">
    <CFPROCPARAM type="OUT" dbvarname="rentalID" variable="out_rentalID"
    cfsqltype="CF_SQL_INTEGER">
    </CFSTOREDPROC>

    FeyBaby Guest

  2. #2

    Default Re: DateTime problem

    Don't use DateFormat(). CreateODBCDate () should be all you need when sending
    to the SP.

    The working page uses DateFormat () to create a string -- that SQL server
    just happens to know how to convert (back to an ODBC-compatible type).

    Also, this is where javascript can really improve the user's experience. A
    good date validater/formatter can be found at:
    [url]http://javascript.internet.com/forms/val-date.html[/url]

    Regards,
    -- MikeR

    MikerRoo Guest

Similar Threads

  1. DateTime Problem with .NET WebService
    By CI2 in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: March 24th, 11:46 AM
  2. Weird DateTime Regional Problem
    By Mark Irvine in forum ASP.NET Web Services
    Replies: 3
    Last Post: March 21st, 10:26 AM
  3. Problem with dataset and datetime
    By Alessandro in forum ASP.NET Web Services
    Replies: 0
    Last Post: November 13th, 07:28 AM
  4. Database datetime problem
    By Christopher Brandsdal in forum ASP
    Replies: 1
    Last Post: August 1st, 10:37 PM
  5. dateTime = Null problem
    By Jake in forum ASP.NET General
    Replies: 1
    Last Post: July 18th, 04:15 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139