how to set a cell to accept null values.

[cfsetNewbie]

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?

[Dan_Bracuk]

What happens if you simply hardcode the keyword null into your insert query?

Possibly unrelated, but I notice you are passing a varchar datatype to a numeric field.

[Sreeindia]

Hi

Usually the error occurs due to mismatch in datatype of db field and cfsqltype of cfqueryparam. Is "FORM.ExpirationYear " of type date?.

If so <cfqueryparam value="#FORM.ExpirationYear#" cfsqltype="CF_SQL_DATE" null="yes">)

"http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-b20.htm" (have a look ).

Hope this will solve your issue

[Reed_Powell-ttnmOb · Accepted solution]

If you open this database in Access and go to design view for the table in question, and look at the definition for this field, what are the settings for "required" and "allow zero length"?

-reed

[cfsetNewbie]

I fixed it. I had it all set properly, it was the hosting company and their settings not allowing it to happen.

Thank you all for all your input.