Return from cfc page on cfcajaxproxy

I am trying to run a function to insert data into a table and then use the scope_identity from that insert to redirct a user to a different page. Could someone take a look at my code and let me know what I maybe doing wrong? Everything works except for the redirect after the record is inserted.

from page1.cfm
<cfajaxproxy cfc="mycfc" jsclassname="jsobj">
Function is trigged on a button push.
	function CreateIt(){
	var Type = '#info.Type#';
	var Style = '#info.Style#';
	var ID = #URL.ID#;
	var cfcAsAjax = new jsobj();
	cfcAsAjax.CreateIt(Type,Style,ID);
	ColdFusion.navigate('page2.cfm?mykey='+ '#CreateIt.TheNumber#');
	}

Open in new window


The CFC code:
<cffunction name="CreateIt" output="false" access="remote">
	<cfargument required="false" type="string" name="Type" />
	<cfargument required="false" type="string" name="Style" />
	<cfargument required="true" type="numeric" name="ID" />
	
	<cfquery name="CreateIt">
			INSERT into table2 (Type, Style)

			(select '#arguments.Type#','#arguments.Style#'
			from table1			
			where RefNumber = #arguments.ID#)
			
			select Scope_identity() as TheNumber
	</cfquery>
	<cfreturn CreateIt>
</cffunction>

Open in new window

earwig75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dgrafxCommented:
The following is how you'd do this insert:

INSERT into table2 (Type, Style)
Values ('#arguments.Type#','#arguments.Style#')

select Scope_identity() as TheNumber

TheNumber will be the ID (whatever the name is of the identity column) of the record that you just inserted.

Or are you trying to do something a bit different?
0
earwig75Author Commented:
I can get the scope_identity, but after that I want to send the user to a page with the scope_identity in the URL. That is the part that I cannot get to work.
0
dgrafxCommented:
<cfquery datasource="xxx" name="doInsert">
INSERT into table2 (Type, Style)
Values ('#arguments.Type#','#arguments.Style#')

select Scope_identity() as TheNumber
</cfquery>
<cflocation url="someurl.cfm?id=#doInsert.TheNumber#" addtoken="0">

or javascript
<cfoutput>
<script>
location.replace('someurl.cfm?id=#doInsert.TheNumber#');
</script>
</cfoutput>

just change the someurl.cfm to whatever you need ...

Good Luck!
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

earwig75Author Commented:
@dgrafx, I still receive the "Exception Thrown and Not Caught" error: 0.

Thank you.
0
dgrafxCommented:
ok so do this:

<cftry>
<cfquery datasource="xxx" name="doInsert">
INSERT into table2 (Type, Style)
Values ('#arguments.Type#','#arguments.Style#')

select Scope_identity() as TheNumber
</cfquery>
<cfdump var="#doInsert#">
<cfcatch>
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<cfabort>

what are the results of this?
you do have the "id" column set up as an identity column in your db - right?
0
earwig75Author Commented:
@dgrafx, I still only receive the same error and no dump is displayed. Yes, ID is the identity. Something odd that happens is, if I turn on debugging, the page I want it to go to is displayed in the debug window over-top of the current page with no browser window around it. the current page with the error just sits there though.
0
earwig75Author Commented:
Also, I tried using a cflocation to another page, just any random page (with no cf code on it etc) and it still fails. It is the cflocation that is failing.
0
dgrafxCommented:
just looked at your js function
change the last couple lines to:

x = cfcAsAjax.CreateIt(Type,Style);
ColdFusion.navigate('page2.cfm?mykey=' + x);

=======================

then change your cfc function to:
<cffunction name="CreateIt" output="false" access="remote">
      <cfargument required="false" type="string" name="Type" />
      <cfargument required="false" type="string" name="Style" />
      <cfquery datasource="xxx" name="doInsert">
      INSERT into table2 (Type, Style)
      Values ('#arguments.Type#','#arguments.Style#')
      
      select Scope_identity() as mykey
      </cfquery>      
      <cfreturn doInsert.mykey />
</cffunction>

let me know ...
0
earwig75Author Commented:
Sorry I am confused. If I change the last 2 lines of the js, how will the ID be passed to the function? This doesn't send the ID to the function at all?

x = cfcAsAjax.CreateIt(Type,Style);
ColdFusion.navigate('page2.cfm?mykey=' + x);
0
dgrafxCommented:
look at your code - the function is not using the passed in ID so why send it in?
what i mean is this:
you are sending in values '#arguments.Type#','#arguments.Style#'.
they have a value when they get to the query.
let's say arguments.Type is 'Hello' and arguments.Style is 'World'.

So you are really saying this:
insert into table2 (type,style)
select 'Hello', 'World'
from table1
where refnumber = 'something'

do you see what I mean?

you are not selecting values based on this passed in ID.
the only thing you would be doing is not inserting at all if there were no records in table1 with that refnumber

just try what I posted - it is not going to injure you to try and then post back issues or modification desires ...

also you need to test and see if you can actually insert and get a scope identity without errors - that in itself is a big issue.
0
earwig75Author Commented:
Ah, I understand. It has been so long that I forgot what I originally posted. I am doing it a bit differently now but it is the same concept and still not working. This is a sample of what I have now based on what you asked me to try. No luck.

js:
	function CreateIt(){
	var ID = #URL.mykey#;
	var cfcAsAjax = new jsobj();
	x = cfcAsAjax.CreateIt(ID);
	ColdFusion.navigate('page2.cfm?mykey=' + x);
	}

Open in new window


function:
<cffunction name="CreateIt" output="false" access="remote" returntype="Any">
	
	<cfargument required="true" type="numeric" name="ID" />
	
	<cfquery name="CreateIt" >

			INSERT into mytable (type, style)
			
			(select type, style
			from mytable2
			where Number = #arguments.ID#)
			
	  select Scope_identity() as mykey
	</cfquery>
	 <cfreturn CreateIt.mykey />
</cffunction>

Open in new window

0
dgrafxCommented:
run just the cfquery to make sure there are not problems there
run this on a .cfm page outside of a function just to test
use a number of your choosing for arguments.ID

<cftry>
        <cfquery name="CreateIt" >
                  INSERT into mytable (type, style)                  
                  (select type, style
                  from mytable2
                  where Number = #arguments.ID#)
                  
        select Scope_identity() as mykey
      </cfquery>
        <cfdump var="#CreateIt#">
<cfcatch>
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
0
earwig75Author Commented:
That seems to work fine.
0
earwig75Author Commented:
That returns RESULTSET with what I would expect to be the scopeID

Cached False, the Execution time, and a copy of the query.

The <cfdump var="#cfcatch#"> produces nothing at all.
0
dgrafxCommented:
ok cool - so the query is good - just wanted to check.

try this:

after x = cfcAsAjax.CreateIt(ID);
put this: console.log(x);
then press F12 key to show dev tools and select console - if on Chrome.
and comment out the line: ColdFusion.navigate('index.cfm?mykey=' + x);

this will test to see if you are getting x returned
0
earwig75Author Commented:
I received: Uncaught SyntaxError: parseJSON
0
dgrafxCommented:
where you have new jsobj I believe it should be jsObj (case sensitivity)

try that
0
earwig75Author Commented:
This time I received: Uncaught ReferenceError: jsObj is not defined
0
dgrafxCommented:
ok forget the case sensitivity - I forgot about your tag
<cfajaxproxy cfc="mycfc" jsclassname="jsobj">

i don't know ???
just try dumping your object - what does <cfdump var="#jsobj#"> give you?
0
earwig75Author Commented:
Where would I add that dump to?
0
dgrafxCommented:
just do:
<cfajaxproxy cfc="mycfc" jsclassname="jsobj">
<cfdump var="#jsobj#">

on a .cfm page

testing like that can be a real time saver instead of wondering what is going on in the hidden world of ajax

basically you are just trying to find some error or something coded incorrectly
0
earwig75Author Commented:
Variable JSOBJ is undefined.
0
dgrafxCommented:
oh yeah right - thats the js name

here: http://www.learncf.com/tutorial/13 
start from scratch with this basic tutorial
0
earwig75Author Commented:
I have seen that example but it isn't using scope_identity and it isn't even using a query. I have tried starting from scratch several times and have not had any luck. Thank you for your time and energy.
0
dgrafxCommented:
well ya know - it doesn't matter if it uses a query or scope_identity.
your problem is elsewhere.

to prove that remove your query entirely from your function and have it return anything like a static number or string - and it will still fail for some reason.
you didn't try that did you?
0
earwig75Author Commented:
It only fails on the cfreturn or if I add a cflocation. nothing fails when I just run the queries. The query runs fine and inserts the data into the table like it should.
0
earwig75Author Commented:
Let me rephrase. If I remove the cfreturn and the coldfusion.navigate, the cfc is reached properly and inserts the data. It only fails when I try and navigate or cflocation from either page (the cfm or the cfc).
0
dgrafxCommented:
well yes - i knew that - i was speaking of getting the ColdFusion.navigate to work properly.

you will not be able to use cflocation in cases like this - i wasn't aware that you were - fyi.

the problem is returning the ID back to the js function and relocating the page - correct?

is it returning any ID back to the function? no i don't think so - that's where you are getting syntax errors.
0
dgrafxCommented:
good morning
once again - try different tactics to find the issue

you could hard code a value just to see that the js is working such as:
x = 12;
ColdFusion.navigate('page2.cfm?mykey=' + x);

if that doesn't work then I give up.
if it does then try this to set x

x = jQuery.parseJSON(cfcAsAjax.CreateIt(ID));
note that you'll need to make a jquery file available to the page.
0
earwig75Author Commented:
Setting X manually works but when I use jquery it sets it to "null" in the URL when it navigates to the page.
0
dgrafxCommented:
do you mean it's null when setting it manually?

what is it when you do not set it manually and use:
x = jQuery.parseJSON(cfcAsAjax.CreateIt(ID));
0
earwig75Author Commented:
Manually works. When I use  jQuery it is null.
0
dgrafxCommented:
do this:
x=cfcAsAjax.CreateIt(ID);
console.log(x);

what does it show for this?

then do this:
x = jQuery.parseJSON(cfcAsAjax.CreateIt(ID));
console.log(x);

what does it show for this?

just fyi - to show console hit F12 and select Console - do this before refreshing page.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earwig75Author Commented:
Thank you. Unfortunately I have to wait until tomorrow to try.
0
dgrafxCommented:
ok
0
earwig75Author Commented:
Both return:

Uncaught SyntaxError: parseJSON
0
earwig75Author Commented:
This is finally working. All of the troubleshooting led me to realize the "returntype" was wrong. Once I set the returntype to "string" on the cfc function it began working properly. Thank you.
0
dgrafxCommented:
i'm glad you got it!

good luck ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.