4 Replies Latest reply on Jul 9, 2010 7:12 AM by cfsetNewbie

    how to set a cell to accept null values.

    cfsetNewbie Level 1

      Hello;

      I am having a problem with my insert query. My form, is passing the year (2010 and up) as a null value. I am using access (ugg) and I can't get it to accept this null value. I have tried setting required to no, and allow zero lenght, even deleted the table and remade it.. STILL I get this error:

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)   The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 234
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 206
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 204
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 234
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 206
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 204
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1

      232 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.securitCode#">,
      233 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.ExpirationMonth#">,
      234 :         <cfqueryparam value="#FORM.ExpirationYear#" cfsqltype="CF_SQL_VARCHAR">)
      235 : </cfquery>
      236 : 
      

       

       


      SQLSTATE  22003
      SQL   INSERT INTO MerchandiseOrdersItems (c_ID, cc_type, cc_num, cc_verify, cc_expir_m, cc_expir_y) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) )
      VENDORERRORCODE

        3


      This is my form element and query that is causing the problem:


      <cfparam name="FORM.ExpirationYear" default="">

       

      <form>

      <select name="ExpirationYear" class="formSelect">
                 <cfloop index="i" from="#VARIABLES.y1#" to="#VARIABLES.y2#">
                    <option value="#i#"<cfif FORM.ExpirationYear EQ i> selected</cfif>>#NumberFormat(i,"0000")#</option>
                 </cfloop>
               </select>

      </form>

       

      <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
      INSERT INTO MerchandiseOrdersItems
      (c_ID, cc_type, cc_num, cc_verify, cc_expir_m, cc_expir_y)
      VALUES (<cfqueryparam value="#getUpdate.NewID#" cfsqltype="CF_SQL_VARCHAR">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.creditType#">,
              <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#form.creditCard#">,
              <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.securitCode#">,
              <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.ExpirationMonth#">,
              <cfqueryparam value="#FORM.ExpirationYear#" cfsqltype="CF_SQL_VARCHAR" null="yes">)
      </cfquery>

       

      The string that is erroring is this:

      <cfqueryparam value="#FORM.ExpirationYear#" cfsqltype="CF_SQL_VARCHAR">

       

      I still get this error. SO It has to be something inside the access database not allowing this null value. It is set as text right now, allowing zero length and not required.

       

      Can anyone help me please?