Database insert puzzle

[mxr618]

I have three forms:
Choose Login
Enter company information
Enter financial information
...in sequence where the data needs to go into the same record in the db.

The only way I can get close is to turn on client management in
application.cfm and set the data as variables using <CFSET>. I then use INSERT
INTO to get the data into the database.

This seems pretty mickey mouse to me and I have a further problem: I can't set
checkbox data as a variable.

So: how do I get the data from my forms -- with the check box info--into the
same record in the db?

[vkunirs]

Hi

Are you using the 3 forms in the same page,

if the 3 forms are in 3 different pages then you can do it easily.

in the first page action use the insert command and pass that inserted it to
the next 2 pages
in the next 2 pages use the update command tp update to the same record.

if all the 3 forms are in the same page then you do the same action as i
said above.
but here the page will be refresh for each form subimission.

[mxr618]

ok, i'm with you so far...there are three separate pages. if the second page
triggers the INSERT action, do I then capture the ID and pass it to the third
page's action?

thanks for your help...we're getting closer

[vkunirs]

Hi

Ya...you are correct.

if you are inserting the record in the second page.
there it self find out the id of that last record and forward it to the next
page through url or form inputs
so in the next page use the update command to update the require field.

thats it.

[ben]

Two alternatives:

1. Put all your fields on the same page into one and the same form or
2. Have your forms on 3 different forms which you present sequentially:
After the first form, insert the data into the DB, get the primary key
ID created and pass it to the second form.

After the second form, update the same record (using the same ID) with
the additional data.

Same after third form.

mxr618 wrote:

> I have three forms:
> Choose Login
> Enter company information
> Enter financial information
> ...in sequence where the data needs to go into the same record in the db.
>
> The only way I can get close is to turn on client management in
> application.cfm and set the data as variables using <CFSET>. I then use INSERT
> INTO to get the data into the database.
>
> This seems pretty mickey mouse to me and I have a further problem: I can't set
> checkbox data as a variable.
>
> So: how do I get the data from my forms -- with the check box info--into the
> same record in the db?
>
>

[kaneohe dreamer]

This is exactly what I am trying to do with an online employment application
for my company. I have four access data base tables set up. My primary key is
AppRec (Auto Number Field) in my first table. Form 1 writes to this table and
Access increments the value of AppRec. We then open Form 2 which writes to
table two. Table two has a foreign key (AppRec) which is tied to the Primary
Key AppRec in table one. When I open Form 2 I want to add the Primary Key Value
from Table 1 to the AppRec field in Form 2.

Ben's response is exactly what I am looking for but being kind of new to Cold
Fusion I was hoping I could get more specific information about how to set this
up.

Thanks

Charles Masteller

[KulaRider]

As far as the checkbox issue, you're probably running into a very common
situation. If a checkbox isn't checked on a form, it isn't considered defined
and you get errors relating to that. Simply add code to check for the
condition of each checkbox field before inserting data... <cfif Not
IsDefined('form.cbField')> <cfset form.cbField = ''> </cfif>

[ben]

When you open Form2, include the AppRec key - which you can retrieve
after having inserted the recordfrom Form2 - as a hidden field in Form2.
Then when you submit Form2, use this AppRec in the action page for Form2.

Ben

kaneohe dreamer wrote:

> This is exactly what I am trying to do with an online employment application
> for my company. I have four access data base tables set up. My primary key is
> AppRec (Auto Number Field) in my first table. Form 1 writes to this table and
> Access increments the value of AppRec. We then open Form 2 which writes to
> table two. Table two has a foreign key (AppRec) which is tied to the Primary
> Key AppRec in table one. When I open Form 2 I want to add the Primary Key Value
> from Table 1 to the AppRec field in Form 2.
>
> Ben's response is exactly what I am looking for but being kind of new to Cold
> Fusion I was hoping I could get more specific information about how to set this
> up.
>
> Thanks
>
> Charles Masteller
>
>