<cfgrid> jonesing me

[Jennifer Sanders]

I've been trying to set up <cfgrid> as a product administrator to allow users
to add new products to an Access database and change info for existing
products. The problem I'm running into is that fields where the user has not
entered any data are generating the following error:

'Error Executing Database Query.

Invalid data for CFSQLTYPE CF_SQL_BIT.'

Some fields must be optional as some products may not have valid info for
certain fields. (E.G. the color field will not be filled out if it's a food
product.)

I've made sure all the text fields that are optional are indicated as not
required in Access. I've also tried using a default value of 'null' in the
database, but no luck. How can I prevent a ColdFusion error from tripping
every time on of these optional fields is blank?

[paross1]

You could SUBMIT the grid and use FORM variables on the action page
(form.grid_name.field_name) rather than using the HREF method when selecting
the row and having to retrieve the values as CFGRIDKEY parameters.

Phil

[Jennifer Sanders]

Thanks for your reply. I'm not sure exactly what you mean. I got what you
meant that I can call variables submitted by <cfgrid> using the formula
form.grid_name.field_name. After submitting, I wasn't able to output any
variables using that formula. I'm submitting the form to CGI.SCRIPT_NAME. I
tried using <cfparam> to set default variables using your naming formula, but
no luck.

Here's my code:

<cfparam name="form.ProductGrid.ProductTitle" default="null">
<cfparam name="form.ProductGrid.ProductCode" default="null">
<cfparam name="form.ProductGrid.ShortDescription" default="null">
<cfparam name="form.ProductGrid.Style" default="null">
<cfparam name="form.ProductGrid.LongDescription" default="null">
<cfparam name="form.ProductGrid.Weight" default="0">
<cfparam name="form.ProductGrid.Price" default="000.00">
<cfparam name="form.ProductGrid.MSRP" default="000.00">
<cfparam name="form.ProductGrid.LargeProductImage" default="null">
<cfparam name="form.ProductGrid.ProductThumbnail" default="null">
<cfparam name="form.ProductGrid.QtyInStock" default="0">
<cfparam name="form.ProductGrid.Active" default="0">
<cfparam name="form.ProductGrid.DropShip" default="0">
<cfparam name="form.ProductGrid.DropShipEmail" default="null">
<cfparam name="form.ProductGrid.GiftRecipient" default="null">
<cfparam name="form.ProductGrid.Occasion" default="null">
<cfparam name="form.ProductGrid.SalesSeason" default="null">
<cfparam name="form.ProductGrid.Color" default="null">
<cfparam name="form.ProductGrid.Size" default="null">
<cfparam name="form.ProductGrid.Personalization" default="null">
<cfparam name="form.ProductGrid.Flavor" default="null">
<cfparam name="form.ProductGrid.CustomOptions" default="null">
<cfparam name="form.ProductGrid.DateCreated" default="null">
<cfparam name="form.ProductGrid.Taxable" default="0">
<cfparam name="form.ProductGrid.CustomField1" default="null">
<cfparam name="form.ProductGrid.CustomField2" default="null">
<cfparam name="form.ProductGrid.CustomField3" default="null">
<cfparam name="form.ProductGrid.CustomField4" default="null">
<cfparam name="form.ProductGrid.CustomField5" default="null">
<cfparam name="form.ProductGrid.CustomField6" default="null">
<cfparam name="form.ProductGrid.Preorder" default="0">

<!--start centering table--><table cellpadding="0" cellspacing="0" border="0"
width="100%"><tr><td align="center"><!--start main table--><table
cellpadding="0" cellspacing="0" border="0" width="800"><tr><td>
<!--Check if the form has been submitted, if so then update DB-->
<cfif IsDefined('form.ProductGrid.rowstatus.action')>
<cfgridupdate grid="ProductGrid" datasource="#MainDS#" tablename="Products"
keyonly="yes">
</cfif>

<cfquery name="GetProds" datasource="#MainDS#">
SELECT *
FROM Products
</cfquery>
<cfform action="#CGI.SCRIPT_NAME#" name="ProductForm"
enctype="application/x-www-form-urlencoded">
<cfgrid name="ProductGrid" width="800" height="400" autowidth="no"
query="GetProds" selectmode="edit" notsupported="<b>Your web browser must
support Java to view ColdFusion Java Applets</b>"
font="Geneva,Arial,sans-serif" textcolor="333333" rowheadertextcolor="333333"
rowheaderbold="yes" rowheaderfont="Geneva,Arial,sans-serif"
colheadertextcolor="333333" colheaderbold="yes"
colheaderfont="Geneva,Arial,sans-serif" delete="yes" deletebutton="Delete"
insert="yes" insertbutton="Add a Record">
<cfgridcolumn name="ProductTitle" display="yes" select="yes">
<cfgridcolumn name="ProductCode" display="yes" select="yes">
<cfgridcolumn name="ShortDescription" display="yes" select="yes">
<cfgridcolumn name="Style" display="yes" select="yes">
<cfgridcolumn name="LongDescription" display="yes" select="yes">
<cfgridcolumn name="Weight" display="yes" select="yes">
<cfgridcolumn name="Price" display="yes" select="yes">
<cfgridcolumn name="MSRP" display="yes" select="yes">
<cfgridcolumn name="LargeProductImage" display="yes" select="yes">
<cfgridcolumn name="ProductThumbnail" display="yes" select="yes">
<cfgridcolumn name="QtyInStock" display="yes" select="yes">
<cfgridcolumn name="Active" display="yes" select="yes">
<cfgridcolumn name="DropShip" display="yes" select="yes">
<cfgridcolumn name="DropShipEmail" display="yes" select="yes">
<cfgridcolumn name="GiftRecipient" display="yes" select="yes">
<cfgridcolumn name="Occasion" display="yes" select="yes">
<cfgridcolumn name="SalesSeason" display="yes" select="yes">
<cfgridcolumn name="Color" display="yes" select="yes">
<cfgridcolumn name="Size" display="yes" select="yes">
<cfgridcolumn name="Personalization" display="yes" select="yes">
<cfgridcolumn name="Flavor" display="yes" select="yes">
<cfgridcolumn name="CustomOptions" display="yes" select="yes">
<cfgridcolumn name="DateCreated" display="yes" select="yes">
<cfgridcolumn name="Taxable" display="yes" select="yes">
<cfgridcolumn name="CustomField1" display="yes" select="yes">
<cfgridcolumn name="CustomField2" display="yes" select="yes">
<cfgridcolumn name="CustomField3" display="yes" select="yes">
<cfgridcolumn name="CustomField4" display="yes" select="yes">
<cfgridcolumn name="CustomField5" display="yes" select="yes">
<cfgridcolumn name="CustomField6" display="yes" select="yes">
<cfgridcolumn name="Preorder" display="yes" select="yes">
</cfgrid><br>
<input name="submit" type="submit" value="Submit Changes">
</cfform>

[paross1]

I always use selectmode="row" and I notice that you are using "edit". According
to by old help pages.....

If SelectMode attribute is Edit, the grid returns a great deal of data in
order to inform the ACTION page of any inserts, updates or deletes that the
user has made to the grid. In most cases, you will not need to manually process
this data, but can just use the cfgridupdate tag to automatically gather the
data from the various form variables. In fact, cfgridupdate not only collects
the incoming data but can also write the appropriate SQL calls, and updates
your data source.

If you are unable to use cfgridupdate (if, for example, you need to distribute
the returned data to more than one data source,) you will need to write code to
read the appropriate form variables. In this mode ColdFusion creates three
array variables in the form scope for each cfgrid, named:

form.#GridName#.#ColumnName#
form.#GridName#.original.#ColumnName#
form.#GridName#.RowStatus.Action
Each row of the table that contains an update, insert, or deletion has a
parallel entry in each of these three arrays. To view all the information for
all the changes, you can traverse the three arrays as in the following example.

<CFLOOP INDEX="ColName" LIST="#ColNameList#">
<CFIF IsDefined("form.#GridName#.#ColName#")>
<CFOUTPUT><BR>form.#GridName#.#ColName#:<BR></CFOUTPUT>

<CFSET Array_New = evaluate("form.#GridName#.#ColName#")>
<CFSET Array_Orig =
evaluate("form.#GridName#.original.#ColName#")>
<CFSET Array_Action =
evaluate("form.#GridName#.RowStatus.Action")>

<CFIF NOT IsArray(Array_New)>
<B>The form variable is not an array!</b><BR>

<CFELSE>
<CFSET size = ArrayLen(Array_New)>
<CFOUTPUT>
Result Array Size is #size#.<BR>
Contents:<BR>
</CFOUTPUT>

<CFIF size IS 0>
<B>The array is empty.</B><BR>

<CFELSE>
<TABLE BORDER="yes">
<TR>
<TH>Loop Index</TH>
<TH>Action</TH>
<TH>Old Value</TH>
<TH>New Value</TH>
</TR>
<CFLOOP index="LoopCount" from="1" to=#size#>
<CFSET Val_Orig = Array_Orig[#LoopCount#]>
<CFSET Val_New = Array_New[#LoopCount#]>
<CFSET Val_Action = rray_Action[#LoopCount#]>
<CFOUTPUT>
<TR>
<TD>#LoopCount#</TD>
<TD>#Val_Action#</TD>
<TD>#Val_Orig#</TD>
<TD>#Val_New#</TD>
</TR>
</CFOUTPUT>
</CFLOOP>
</TABLE>
</CFIF>
</CFIF>
<CFELSE>
<CFOUTPUT>form.#GridName#.#ColName#: NotSet!</cfoutput><BR>
</CFIF>
</CFLOOP>

Phil