[flexcoders] Re: SQL Insert operation fails silently
Greg, My apologies for not getting back to the board sooner but at least I come bearing solutions. The problem was both an Adobe bug and problems with my code and the dB. Like the perfect storm it all came together to deny me the simple pleasure of an INSERT operation. Flex updater 2.0.1 fixed the child form INSERT problem (Thank you very much to Farah Gron and her team for working with me and including the fix in the update). For a child form (usually a dataGrid) inside a parent, the ArrayCollection that holds the child data is not populated correctly when adding a new record. The fix was to force the array from the array collection with this line: this.detailObject.childBeans = getChildBeansArray(); The wizard now puts this line just above this.datamanager.save(item) in the function saveItem(item:Object) Okay, that fixed Adobe's bug but still didn't work for me completely. In order to get the primary key from the parent table and insert it as the foreign key in the child, the wizard uses a SELECT statement with a WHERE clause that uses every field from the parent table. That might work and might not. In my case, several of those fields were not part of the parent form and so contained nulls (why my dB allowed nulls is a matter I'll have to discuss with my DBA -- oh yeah, that's me-- shoot!). What do you get when the WHERE clause returns nulls? You got it, nothing. I deleted those fields from the WHERE clause and added a timestamp to ensure I got the right foreign key and all is well in Flex land. PS the dB no longer allows nulls -- duh Thanks for your input Greg. It got me looking and helped out a great deal. Cheers, Dan --- In flexcoders@yahoogroups.com, greg h [EMAIL PROTECTED] wrote: Dan, Your writing the following makes me wonder if there might not be a conditional block somewhere that results in the INSERT child statement not being executed. You wrote: The INSERT for the child table simply isn't even attempted. That would be sensible since executing INSERT child without the necessary foreign key would result in an error at run time :-) Could you check the code around whatever calls the insert for any IF/ELSE logic? Where the initial subject line was fails silently, maybe the error is that there is no logic to select back the newly inserted primary key for the PARENT, causing conditional logic to skip INSERT child since, again, executing INSERT child without the foreign key would result in a runtime error. So now I really, really am curious. So please do post back once you get it all working. g On 12/20/06, nall_daniel [EMAIL PROTECTED] wrote: Thanks Greg, Your advice is appreciated and helpful. I'm using MS SQL server, CF 7.02, and of course Flex 2. I have nothing in my CF logs, nothing in SQL logs (SQL errors show up in the browser anyway ie fault=server_fault(event)). I use the Flash Tracer extension for Firefox (very helpful BTW) and see nothing there either. The INSERT for the child table simply isn't even attempted. I've contacted Adobe and they have verified the problem and submitted it as a bug. In the meantime, I have done this with CF flash forms before so I'll try the same technique I used there. INSERT parent, Lock the dB, grab primary key, INSERT child, release lock. We'll see. If I can get it working I'll definately post a solution. Hopefully Adobe will beat me to the punch. Thanks again... Dan
[flexcoders] Re: SQL Insert operation fails silently
Thanks Greg, Your advice is appreciated and helpful. I'm using MS SQL server, CF 7.02, and of course Flex 2. I have nothing in my CF logs, nothing in SQL logs (SQL errors show up in the browser anyway ie fault=server_fault(event)). I use the Flash Tracer extension for Firefox (very helpful BTW) and see nothing there either. The INSERT for the child table simply isn't even attempted. I've contacted Adobe and they have verified the problem and submitted it as a bug. In the meantime, I have done this with CF flash forms before so I'll try the same technique I used there. INSERT parent, Lock the dB, grab primary key, INSERT child, release lock. We'll see. If I can get it working I'll definately post a solution. Hopefully Adobe will beat me to the punch. Thanks again... Dan --- In flexcoders@yahoogroups.com, greg h [EMAIL PROTECTED] wrote: Dan, What database are you using? e.g. MySQL, MS SQL 2000, Oracle, DB2, Access, etc? Database vendor's implementations vary on how they automatically generate surrogate primary keys. Oracle: sequencehttp://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3092 MySQL: AUTO_INCREMENThttp://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html(see Forta re: capturing using ColdFusion http://www.forta.com/blog/index.cfm?mode=entryentry=A61BCDEA-3048-80A9-EFE79327EB7554DC ) Microsoft: @@identityhttp://msdn2.microsoft.com/en-us/library/ms187342.aspx (see Forta re: capturing using ColdFusionhttp://www.forta.com/blog/index.cfm?mode=eentry=221 ) etc. Basically, after the parent row is inserted, your logic must know the new primary key value before executing the insert into the child row. I just cross checked for questions like this over on the CF-Talk listhttp://www.houseoffusion.com/groups/CF-Talk/and found the following link detailing How do I grab the ID of the record I just created?: http://mysecretbase.com/get_the_last_id.cfm Regarding the code generated by the CF Wizard, I have not yet attempted using it for any parent/child insert operations like you are attempting. If the code generated by the wizard is deficient in capturing the necessary foreign key identifier before inserting the child, then you will need to edit the code that it generates for you. btw ... the error on the child inserts likely is not failing silently. It may be in your ColdFusion logs. Or if you start ColdFusion from the command prompthttp://www.forta.com/blog/index.cfm?mode=entryentry=A61BD25F-3048-80A9-EF9FCB7441C5C6C0you will see all errors on the console. It only appears to be silent because there is no handling for it to display Flex. (Gee, I sure wish there were a NetConnection debugger for Flex 2/AS3/AMF3 ... hint, hint Adobe ;-) If you test the CFC where the error is occurring by calling it from a .CFM test page, likely you will see the full error message too. Sorry that I can't be more helpful. I can not say where you can get best support specifically on issues related to the Flex CF Wizard. In addition to posting here on FlexCoders, you might also try crossposting on these other two lists: http://www.houseoffusion.com/groups/Flex/ http://www.houseoffusion.com/groups/CF-Talk/ Of course, for ColdFusion specific issues you are likely to get the best response on lists like CF-Talkhttp://www.houseoffusion.com/groups/CF-Talk/rather than here on the Flex list. It would be nice if code generators worked perfectly. Sometimes I think they work best in helping experienced coders get their work done faster. If you do resolve this problem, please post back on this thread with an update on what the solution was. hth, g On 12/19/06, nall_daniel [EMAIL PROTECTED] wrote: No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
[flexcoders] Re: SQL Insert operation fails silently
No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
[flexcoders] Re: SQL Insert operation fails silently
I have duplicated the problem with a very simple test db. I have two tables, PARENT CHILDREN. There is a one to many relationship From PARENT to CHILDREN. Using the CF wizard I generated a Master list of parents, and a Detail form. The Detail form allows users to enter parent information and has a datagrid subform where users can enter children of the parent. On new forms, the generated code fails to insert children into the db. On subsequent edits of the parent, children information is successfully updated into the db. Seems like because the PARENT primary key (foriegn key in CHILDREN) is auto-created by SQL, it is not available for CHILDREN insert operations and so fails. On edits, the key exists and updates are successful. however, this is a fairly common way of generating primary keys and should be handled by the wizard. Again anyone have any ideas? Dan --- In flexcoders@yahoogroups.com, nall_daniel [EMAIL PROTECTED] wrote: My sub-form in a CF Wizard generated form is not inserted into the database. I have a purchase form with a main form and a subform (datagrid). Main form is based on a table and subform on a related table. The form was generated by using the CF Wizard in Flex Builder. For a new purchase, the create (SQL INSERT) operation will insert the main form data into the database but does not insert the subform data. It does not fail or error out. It simply doesn't insert the data. If I then edit the same purchase, the update (SQL UPDATE) function works perfectly and all data is passed. The are no flash debug errors nor SQL errors. Any ideas? Dan
[flexcoders] SQL Insert operation fails silently
My sub-form in a CF Wizard generated form is not inserted into the database. I have a purchase form with a main form and a subform (datagrid). Main form is based on a table and subform on a related table. The form was generated by using the CF Wizard in Flex Builder. For a new purchase, the create (SQL INSERT) operation will insert the main form data into the database but does not insert the subform data. It does not fail or error out. It simply doesn't insert the data. If I then edit the same purchase, the update (SQL UPDATE) function works perfectly and all data is passed. The are no flash debug errors nor SQL errors. Any ideas? Dan
[flexcoders] Re: totaling items in datagrid
Thanks Ben,
I already use similar code with a labelFunction to do sub-totals
within the grid and it works very well. However, that code will only
do calculations on a single row and not on an entire column. The code
I wrote for totaling a column works as well but not on a new form
where the model has no data yet. I would also like to run the code
after the data model is populated (run the function last after all
other code has run). How can I iterate through a dataGrid when it
holds the data and my model or the array doesn't have any data yet?
--- In flexcoders@yahoogroups.com, bsdensmore [EMAIL PROTECTED] wrote:
a labelfunction will do the calculations as you tab through the fields
or as change rows on the grid.
I use this to do some calculations and it works great.
private function
calcAdjAmount(item:Object,column:DataGridColumn):String {
var adjustedAmt:Number;
var emptyField:String = ;
if (item['Num_Cases_Claimed'] != null) {
adjustedAmt = item['Num_Cases_Claimed'] *
Number(removeFormatting.format(item['Net_Promo_Amt']));
return
basicCurrencyFormat.format(adjustedAmt.toString());
}
else {
return emptyField;
}
}
Ben
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
No one has an answer to this? Or suggestions?
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
I am using the CF/Flex Application Wizard to generate a basic form
based application. I have a Master datagrid that list all the
purchase requests a user has submitted and a Detail form for
editing a
request or submitting a new request. The detail form has a datagrid
as a subform. I pass the mx:model the wizard created of the
subform's
data to this function:
[Bindable]
public var purchaseTotal:Number;
private function grandTotal(myArray:Array):void
{
purchaseTotal = 0;
var length:int = myArray.length;
for (var i:int = 0; i length; i++)
{
purchaseTotal += Number(myArray[i].Quantity *
myArray[i].UnitCost)
}
}
I assign purchaseTotal variable to the text property of a label and
run the function on the subform's datagrid.itemFocusOut event. Now
when a user is editing a purchase request, the grand total updates
when the user changes either the Quantity or the UnitCost.
Couple of
problems with what I'm doing:
1. No grand total in the label until the user tabs or clicks through
the datagrid subform. How can I get my function to run after the
mx:model is created and populated with data?
2. New requests never show a grand total. Where's the model's
data?
My apologies for being so long winded but I wanted to make sure
what I
was doing is clear.
Cheers,
Dan
[flexcoders] Re: totaling items in datagrid
Okay, I made a little progress on this. Instead of passing the
mx:model (which is an array) to my Grand Total function I'm now
passing the actual dataProvider which is an arrayCollection. Now, new
forms show the grand total as well as previously existing forms being
edited. That's where the data is held, not the model. One last thing
to do, How can I get my grand total function to run last after the
arrayCollection is populated?
Dan
--- In flexcoders@yahoogroups.com, nall_daniel [EMAIL PROTECTED]
wrote:
Thanks Ben,
I already use similar code with a labelFunction to do sub-totals
within the grid and it works very well. However, that code will only
do calculations on a single row and not on an entire column. The code
I wrote for totaling a column works as well but not on a new form
where the model has no data yet. I would also like to run the code
after the data model is populated (run the function last after all
other code has run). How can I iterate through a dataGrid when it
holds the data and my model or the array doesn't have any data yet?
--- In flexcoders@yahoogroups.com, bsdensmore bsdensmore@ wrote:
a labelfunction will do the calculations as you tab through the fields
or as change rows on the grid.
I use this to do some calculations and it works great.
private function
calcAdjAmount(item:Object,column:DataGridColumn):String {
var adjustedAmt:Number;
var emptyField:String = ;
if (item['Num_Cases_Claimed'] != null) {
adjustedAmt = item['Num_Cases_Claimed'] *
Number(removeFormatting.format(item['Net_Promo_Amt']));
return
basicCurrencyFormat.format(adjustedAmt.toString());
}
else {
return emptyField;
}
}
Ben
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
No one has an answer to this? Or suggestions?
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
I am using the CF/Flex Application Wizard to generate a basic form
based application. I have a Master datagrid that list all the
purchase requests a user has submitted and a Detail form for
editing a
request or submitting a new request. The detail form has a
datagrid
as a subform. I pass the mx:model the wizard created of the
subform's
data to this function:
[Bindable]
public var purchaseTotal:Number;
private function grandTotal(myArray:Array):void
{
purchaseTotal = 0;
var length:int = myArray.length;
for (var i:int = 0; i length; i++)
{
purchaseTotal += Number(myArray[i].Quantity *
myArray[i].UnitCost)
}
}
I assign purchaseTotal variable to the text property of a
label and
run the function on the subform's datagrid.itemFocusOut event.
Now
when a user is editing a purchase request, the grand total updates
when the user changes either the Quantity or the UnitCost.
Couple of
problems with what I'm doing:
1. No grand total in the label until the user tabs or clicks
through
the datagrid subform. How can I get my function to run after the
mx:model is created and populated with data?
2. New requests never show a grand total. Where's the model's
data?
My apologies for being so long winded but I wanted to make sure
what I
was doing is clear.
Cheers,
Dan
[flexcoders] Re: totaling items in datagrid
Got it. The CF/Flex wizard will generate an ArrayCollection variable
to hold the data for a datagrid sub-form. It will also generate a
function get_result() that gets all the data for the entire form. I
placed a call to my grand total function as the last line in
get_result() and voila! Now the grand total appears immediately after
the dataGrid fills with data and changes whenever the grid changes.
Gotta love this stuff
Dan
--- In flexcoders@yahoogroups.com, nall_daniel [EMAIL PROTECTED]
wrote:
Okay, I made a little progress on this. Instead of passing the
mx:model (which is an array) to my Grand Total function I'm now
passing the actual dataProvider which is an arrayCollection. Now, new
forms show the grand total as well as previously existing forms being
edited. That's where the data is held, not the model. One last thing
to do, How can I get my grand total function to run last after the
arrayCollection is populated?
Dan
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
Thanks Ben,
I already use similar code with a labelFunction to do sub-totals
within the grid and it works very well. However, that code will only
do calculations on a single row and not on an entire column. The code
I wrote for totaling a column works as well but not on a new form
where the model has no data yet. I would also like to run the code
after the data model is populated (run the function last after all
other code has run). How can I iterate through a dataGrid when it
holds the data and my model or the array doesn't have any data yet?
--- In flexcoders@yahoogroups.com, bsdensmore bsdensmore@ wrote:
a labelfunction will do the calculations as you tab through the
fields
or as change rows on the grid.
I use this to do some calculations and it works great.
private function
calcAdjAmount(item:Object,column:DataGridColumn):String {
var adjustedAmt:Number;
var emptyField:String = ;
if (item['Num_Cases_Claimed'] != null) {
adjustedAmt = item['Num_Cases_Claimed'] *
Number(removeFormatting.format(item['Net_Promo_Amt']));
return
basicCurrencyFormat.format(adjustedAmt.toString());
}
else {
return emptyField;
}
}
Ben
--- In flexcoders@yahoogroups.com, nall_daniel daniel.nall.ctr@
wrote:
No one has an answer to this? Or suggestions?
--- In flexcoders@yahoogroups.com, nall_daniel
daniel.nall.ctr@
wrote:
I am using the CF/Flex Application Wizard to generate a
basic form
based application. I have a Master datagrid that list all the
purchase requests a user has submitted and a Detail form for
editing a
request or submitting a new request. The detail form has a
datagrid
as a subform. I pass the mx:model the wizard created of the
subform's
data to this function:
[Bindable]
public var purchaseTotal:Number;
private function grandTotal(myArray:Array):void
{
purchaseTotal = 0;
var length:int = myArray.length;
for (var i:int = 0; i length; i++)
{
purchaseTotal += Number(myArray[i].Quantity *
myArray[i].UnitCost)
}
}
I assign purchaseTotal variable to the text property of a
label and
run the function on the subform's datagrid.itemFocusOut event.
Now
when a user is editing a purchase request, the grand total
updates
when the user changes either the Quantity or the UnitCost.
Couple of
problems with what I'm doing:
1. No grand total in the label until the user tabs or clicks
through
the datagrid subform. How can I get my function to run
after the
mx:model is created and populated with data?
2. New requests never show a grand total. Where's the model's
data?
My apologies for being so long winded but I wanted to make sure
what I
was doing is clear.
Cheers,
Dan
[flexcoders] Re: totaling items in datagrid
No one has an answer to this? Or suggestions?
--- In flexcoders@yahoogroups.com, nall_daniel [EMAIL PROTECTED]
wrote:
I am using the CF/Flex Application Wizard to generate a basic form
based application. I have a Master datagrid that list all the
purchase requests a user has submitted and a Detail form for editing a
request or submitting a new request. The detail form has a datagrid
as a subform. I pass the mx:model the wizard created of the subform's
data to this function:
[Bindable]
public var purchaseTotal:Number;
private function grandTotal(myArray:Array):void
{
purchaseTotal = 0;
var length:int = myArray.length;
for (var i:int = 0; i length; i++)
{
purchaseTotal += Number(myArray[i].Quantity * myArray[i].UnitCost)
}
}
I assign purchaseTotal variable to the text property of a label and
run the function on the subform's datagrid.itemFocusOut event. Now
when a user is editing a purchase request, the grand total updates
when the user changes either the Quantity or the UnitCost. Couple of
problems with what I'm doing:
1. No grand total in the label until the user tabs or clicks through
the datagrid subform. How can I get my function to run after the
mx:model is created and populated with data?
2. New requests never show a grand total. Where's the model's data?
My apologies for being so long winded but I wanted to make sure what I
was doing is clear.
Cheers,
Dan
