Professional Web Applications Themes

Access data type mismatch for empty form field - Coldfusion Database Access

Hello Everyone; I'm using an MS Access DB on a CFMX (CF 6) site...and updating records in a db from a form ..the CFM for code looks like : <cfquery name="ProjectTable" datasource="caproject"> UPDATE CAprojectDB SET CaClientNum = '#Form.CaClientNum#', DateAdded = '#Form.DateAdded#', CaJobNum = #Form.CaJobNum#, QntyOrdered = #Form.QntyOrdered#, DieCut = '#Form.diecut#', OnHold = '#Form.OnHold#', ProjDueDate = '#Form.ProjDueDate#', UpdateDate = '#Form.UpdateDate#', notes = '#Form.notes#', Size = '#Form.Size#', ArtIntoCp = '#Form.ArtIntoCp#' WHERE ProjectID = #Form.ID# </cfquery> However, when the form is submitted and some of the fields are emtpy I get a "Data type mismatch in criteria expression. " error. I've tried adding ...

  1. #1

    Default Access data type mismatch for empty form field

    Hello Everyone;

    I'm using an MS Access DB on a CFMX (CF 6) site...and updating records in a db
    from a form ..the CFM for code looks like :

    <cfquery name="ProjectTable" datasource="caproject">
    UPDATE CAprojectDB
    SET CaClientNum = '#Form.CaClientNum#',
    DateAdded = '#Form.DateAdded#',
    CaJobNum = #Form.CaJobNum#,
    QntyOrdered = #Form.QntyOrdered#,
    DieCut = '#Form.diecut#',
    OnHold = '#Form.OnHold#',
    ProjDueDate = '#Form.ProjDueDate#',
    UpdateDate = '#Form.UpdateDate#',
    notes = '#Form.notes#',
    Size = '#Form.Size#',
    ArtIntoCp = '#Form.ArtIntoCp#'
    WHERE ProjectID = #Form.ID#
    </cfquery>

    However, when the form is submitted and some of the fields are emtpy I get a
    "Data type mismatch in criteria expression. " error. I've tried adding code to
    the Insert fields...such as

    <cfif isdefined("form.Size")>
    Size = '#Form.Size#',
    </cfif>

    But, doesn't seem to do much.... I want to be able to update fields to empty
    strings also... and can't really just check for non-empty strings....
    Any help you can give is GREATLY appreciated!

    Thanks so much,
    John


    createmedia Guest

  2. #2

    Default Re: Access data type mismatch for empty form field

    When defining th columns in the database, you need to also set if the field can
    be NULL or not.
    In ms access, click on the column and look at the bottom of the screen, you
    will see the "allow null"
    Although I would have expected a different error message for this.
    Ensure you have the correct data types set for each field.
    EG. "DateAdded" if this is a dtate then it should be a date/time datatype
    and you should set it with code like DateAdded =
    #CreateODBCDate(Form.DateAdded)#,

    Another thing that could be causing problems is that you have a couple of
    columns in the database named with reserved words
    "notes" and "size". If you can't change these then enclose in

    To determine if the form field contains any data, you should check if it is
    defined (as you have done) but also check the length
    <cfif isdefined("form.Size") And Len(form.Size) NEQ "">
    Size = '#Form.Size#',
    </cfif>

    But as you just want to insert an empty string anyway, there is no need to
    check the length as it will do this anyway.

    Ken


    The ScareCrow Guest

  3. #3

    Default Re: Access data type mismatch for empty form field

    Thanks Ken,

    Renamed the "notes" and "size" fields....Good catch! The text fields are all
    set to "Allow Zero Length" .. the Date fields seem to be the problem... They
    don't have an "Allow Zero Length" property... just a "Required" property .. of
    which all are set to "No"

    Experimenting with one Date field called UpdateDate : <<UpdateDate =
    '#Form.UpdateDate#',>> by sending the value in the form as an empty field or
    string... Query like :

    <cfquery name="ProjectTable" datasource="caproject">
    UPDATE CAprojectDB
    SET CaClientNum = '#Form.CaClientNum#',
    UpdateDate = '#Form.UpdateDate#',
    ArtIntoCp = '#Form.ArtIntoCp#'
    WHERE ProjectID = #Form.ID#
    </cfquery>

    But, still getting the error : "Data type mismatch in criteria expression" .
    The field in the MS Access DB is a Date/Time field...and the form is sending
    the value :
    <input type="text" size="15" name="UpdateDate"
    value="<cfoutput>#DateFormat(dateInfo.UpdateDate)# </cfoutput>">

    I want to allow an empty string to allow for an empty date field...but still
    can't figure out why I get the Data type mismatch error...

    Thanks again!!!



    createmedia Guest

  4. #4

    Default Re: Access data type mismatch for empty form field

    Maybe something like this?

    <cfquery name="ProjectTable" datasource="caproject">
    UPDATE CAprojectDB
    SET CaClientNum = '#Form.CaClientNum#',
    <CFIF Form.UpdateDateUpdateDate NEQ "">
    UpdateDate = '#Form.UpdateDate#'
    <CFELSE>
    UpdateDate = NULL
    </CFIF>,
    ArtIntoCp = '#Form.ArtIntoCp#'
    WHERE ProjectID = #Form.ID#
    </cfquery>

    Phil

    paross1 Guest

  5. #5

    Default Re: Access data type mismatch for empty form field

    Thanks Ken!

    this is what worked!

    <CFIF Form.UpdateDate NEQ "">
    UpdateDate = #CreateODBCDate(Form.UpdateDate)#,
    <CFELSE>
    UpdateDate = NULL,
    </CFIF>

    small typo but it worked!! Allows Null date values now!
    You da man! :-)

    createmedia Guest

  6. #6

    Default Re: Access data type mismatch for empty form field

    Ken? You're welcome anyway. :D

    Phil
    paross1 Guest

Similar Threads

  1. Access database getting mismatch data on criteria
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 2nd, 10:00 PM
  2. data type mismatch error...
    By P_Roberts in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 30th, 04:27 PM
  3. Data type mismatch in criteria expression
    By Captain Ru in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 21st, 05:24 PM
  4. Replies: 1
    Last Post: October 13th, 01:59 AM
  5. Type Mismatch (error 13) with recordsetclone and form
    By DC Hendrickson in forum Microsoft Access
    Replies: 2
    Last Post: July 24th, 12:30 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