Just Skins Support Forums > Archives > Macromedia > Macromedia ColdFusion > Coldfusion Database Access > <cfgrid> jonesing me

<cfgrid> jonesing me

Posted: 10-26-2005, 11:25 PM
Jennifer Sanders
Guest
Posts: n/a
 
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?

Reply With Quote

Responses to "<cfgrid> jonesing me"

paross1
Guest
Posts: n/a
 
Re: <cfgrid> jonesing me
Posted: 10-27-2005, 04:54 PM
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

Reply With Quote
Jennifer Sanders
Guest
Posts: n/a
 
Re: <cfgrid> jonesing me
Posted: 10-27-2005, 05:54 PM
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>

Reply With Quote
paross1
Guest
Posts: n/a
 
Re: <cfgrid> jonesing me
Posted: 10-27-2005, 09:08 PM
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

Reply With Quote
 
LinkBack Thread Tools Search this Thread Display Modes
Reply

« Previous Thread | Next Thread »

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
cfgrid and semicolon bug VirtualED Coldfusion Flash Integration 4 09-29-2008 11:32 PM
CFGRID hyperlink bal_logicmanager Coldfusion Flash Integration 4 09-23-2005 06:33 PM
cfgrid Chikowski Coldfusion Flash Integration 10 07-22-2005 06:49 PM
checkbox in cfgrid iychen Coldfusion Flash Integration 0 06-21-2005 03:55 PM
Help with CFGrid Carolyn.Hajnasiewicz Coldfusion - Getting Started 1 05-05-2005 07:07 PM