Error in complex updates cold fusion

Suite and Optimus Suite 5
#1

hi, I’ve got some problem

based on:
docs.dhtmlx.com/doku.php?id=dhtm … ex_updates

I’m trying to submit a registration form and insert the data to 2 different table:
insert to user table, and user_info table

inserting to the user table, works with no problem.
when I’m trying to insert to the user_info table, it shows error like this:

[code]Element CONN.EVENT is undefined in a Java object of type class [Ljava.lang.String; referenced as ‘’

The error occurred in C:\Inetpub\wwwroot\olopluscf\xml\user.cfm: line 35

33 : <cfset ARGUMENTS.data.success()>
34 :
35 : <cfset request.conn.event.attach(“afterInsert”,my_update)>
36 : <!— we use ‘request’ scope to access connector within custom event—>[/code]

the dataprocessor code:

[code]

<cfset conn = createObject(“component”,request.dhtmlxConnectors[“form”]).init(request.dhtmlxConnectors[“datasource”],request.dhtmlxConnectors[“db_type”])/>

    <cfset request.conn.sql.query("INSERT INTO user_info
								(User_Id,first_name,last_name,address1,address2,city,state_id,zip_code,phone)
								VALUES
								(#id#,
								'#first_name#',
								'#last_name#',
								'#address1#',
								'#address2#',
								'#city#',
								#state_id#,
								#zip_code#,
								#phone#)")>	
<cfset ARGUMENTS.data.success()>

<cfset conn.render_table(“[user]”,“id”,“user_name,password”)>[/code]

and how to get the id in user table that just inserted, and save it in the user_info table?

thank you very much.

#2

Hi

  1. The currently descibed problem is based on the mix of variables scopes. You create the connector () in variables scope and then use the <cfset request.conn…). That’s the reason of your error. <request.conn> scope is required here, because you have to use this variable inside the custom function, and it will be impossible if you use “variables” scope. Use “request.conn” instead of “conn” everywhere in the connector code.

  2. If you use inside the event something like ARGUMENTS.data.get_value(“first_name”) - you have to mention all fields in the connector constructor (third parameter in conn.render_table function).

  3. ARGUMENTS.data.get_new_id() function will return the new ID after insert.

#3

hi thank you very much for your reply,

I’m trying the solution and it works well, but I’ve got some problem about this:

I can get the new id from the USER table, and successfuly inserted that ID to ther USER_INFO table. but I can’t insert the rest variables from the form.

so my form is like this:
-username
-pass
-firstname
-lastname
-etc

username and pass is saved in USER table
firstname, lastname, etc is saved in USER_TABLE

when I mention the fields in the connector constructor, it keeps getting error, because the firstname lastname etc doesn’t exist in the USER table :frowning:

#4

Hi. When you use several tables you can work in the next way:

  1. You render_sql from the connector
<cfset request.con.render_table("tbl1 INNER JOIN tbl2 ON tbl1.id  = tbl2.p_id","id", "flds1,flds2")>
  1. Then you can use custom events for insert/update/delete.
    I will show on the sample of update event:

[code]


<cfset local.id = ARGUMENTS.data.get_value(‘id’)>
<cfset local.flds = ARGUMENTS.data.get_value(‘flds’)>
<cfset local.flds2 = ARGUMENTS.data.get_value(‘flds2’)>
…

    <!---- Here we update the first table --->
<cfset request.con.sql.query("UPDATE tbl1 SET fld1='#local.fld1#' WHERE id=#local.id#")>
   <!--- now we update second table --->
<cfset request.con.sql.query("UPDATE tbl1 SET fld2='#local.fld2#' WHERE p_id=#local.id#")>
   <!--- And now we send message to connector that update is done. So Default queries will be ignored ---> 
   <cfset ARGUMENTS.data.success()>
[/code]

The same can be done for insert/delete events.

  1. If updates are not critical you can define custom events without any custom fuctions, directly after connector is created:

<cfset request.con.render_table("tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.p_id","id", "flds...")> <cfset request.con.sql.attach("delete","DELETE FROM tbl1 WHERE id={id}")> <cfset request.con.sql.attach("delete","DELETE FROM tbl2 WHERE p_id={id}")>
… And sone on for other events. In this case the default events are also ignored.

#5

hi Ivan thank you very much for your fast response

now I know how it works, but with this method I’ve got some problem to get the new ID to insert into the second table

this is my code:

[code]<!— config —>

<cfset request.conn = createObject(“component”,request.dhtmlxConnectors[“form”]).init(request.dhtmlxConnectors[“datasource”],request.dhtmlxConnectors[“db_type”])/>

	<cfset request.conn.sql.query("INSERT INTO [user]
							(user_name,password)
							VALUES
							('#user_name#','#password#')
							")>	

    <cfset request.conn.sql.query("INSERT INTO user_info
								(user_id,first_name,last_name,address1,address2,city,state_id,zip_code,phone)
								VALUES
								(#id#,
								'#first_name#',
								'#last_name#',
								'#address1#',
								'#address2#',
								'#city#',
								#state_id#,
								#zip_code#,
								#phone#)
								")>	
<cfset ARGUMENTS.data.success()>

<cfset request.conn.render_table("[user] INNER JOIN user_info ON [user].id = user_info.user_id",“id”,“user_name,password,first_name,last_name,address1,address2,city,state_id,zip_code,phone”)>
[/code]

I already changing the get_value to get_new_ID but it doesn’t works

#6

I’ve already figured out the solution :slight_smile:

REF: docs.dhtmlx.com/doku.php?id=dhtm … om_queries

this is my final code:

[code]

<cfset var user_name = ARGUMENTS.data.get_value(“user_name”)>
<cfset var password = ARGUMENTS.data.get_value(“password”)>
<cfset var first_name = ARGUMENTS.data.get_value(“first_name”)>
<cfset var last_name = ARGUMENTS.data.get_value(“last_name”)>
<cfset var address1 = ARGUMENTS.data.get_value(“address1”)>
<cfset var address2 = ARGUMENTS.data.get_value(“address2”)>
<cfset var city = ARGUMENTS.data.get_value(“city”)>
<cfset var state_id = ARGUMENTS.data.get_value(“state_id”)>
<cfset var zip_code = ARGUMENTS.data.get_value(“zip_code”)>
<cfset var phone = ARGUMENTS.data.get_value(“phone”)>

	<cfset request.conn.sql.query("INSERT INTO [user]
							(user_name,password)
							VALUES
							('#user_name#','#password#')
							")>
							
	<cfset id = request.conn.sql.get_new_id()>								

    <cfset request.conn.sql.query("INSERT INTO user_info
								(user_id,first_name,last_name,address1,address2,city,state_id,zip_code,phone)
								VALUES
								(#id#,
								'#first_name#',
								'#last_name#',
								'#address1#',
								'#address2#',
								'#city#',
								#state_id#,
								#zip_code#,
								#phone#)
								")>	
<cfset ARGUMENTS.data.success()>
[/code]

thank you very much Ivan for your help, deeply apriciated.