insert and update a record on the same form

[iccsi]

I would like to use same form to insert and update a record to database.

I use Dreamweaver to have a form.

I tried many ways, it seems that I need have insert and update on the different form.

In principle, I should be able to check record exists or not, if yes then update the record, if not then insert a new record.

Can you please help and advise is it can be done using ColdFusion?

If yes, can you please advise where I can get example for this?

Your help and information is great appreciated,

Regards,

Iccsi

[Dan_Bracuk]

The part where you check for existing records is done by querying the database.  This could be done as part of a stored procedure that you call with ColdFusion or as a cfquery.  I use Dreamweaver in code view to write my ColdFusion code.

[iccsi]

Thanks a million for the message and helping,

Is it possible  to have link for sample code?

Thanks again,

Regards,

Iccsi,

[Leonard_B]

Hello, Iccsi.

You could try something like this.

On your processing page / section try something like this:

Check for existence of database record.

<cfquery name="rsCheck" datasource="datasource">

. . . query content here . . .

</cfquery>

Evaluate and process based on query

<cfswitch expression="#rsCheck.RecordCount#">

<cfcase value="0">

No record found - Insert process here

Redirect to a custom insert page/section message

<cflocation url="redirect-to-desired-location.cfm?RecordProcess=Insert" addtoken="yes|no">

</cfcase>

<cfcase value="1">

Record found - Update process here

Redirect to a custom update page/section message

<cflocation url="redirect-to-desired-location.cfm?RecordProcess=Update" addtoken="yes|no">

</cfcase>

</cfswitch>

Of course you can tweak / change the above to fit your needs. This is just an idea to try.

Leonard B

[iccsi]

Thanks for the message and help,

According to your code, it seems that I still need have one form for insert and one form for update.

I just use a form to process both and direct user to the right form.

please let me know, if I am wrong,

Thanks again for helping,

Iccsi,

[Leonard_B]

Hello, Iccsi.

= = =

In principle, I should be able to check record exists or not, if yes then update the record, if not then insert a new record.

= = =

I may be off track here, but reading this sentence in your original post, leads me to think there is a field(s) in your form that

you would be using to determine if the record exists or does not exist, i.e. product id, product name, etc.

When submitting the form, you would use the determinig field(s) to query the desired db table for record presence and based on

query results either update the respective record or add the information as a new record.

So --- one form, db table search, process according to db results

Leonard B

[BreakawayPaul]

There are probably a couple of ways to do this, and I've done it in the past using various methods.  One was to use variables as form values with null defaults, like this:

<cfparam name="user_email" default="" />

Then in the form:

<input name="email" id="email" type="text" value="#user_email#" />

This gives you a form you can use for inserts, as the values are blank.  When you need to update a record, you usually pass something with a URL string, like this:

page.cfm?item=42

Then on the same page, between the <cfparam>s and the form, have this:

<cfif StructKeyExists(URL,"item")>

<cfquery name="myquery" datasource="mydsn">

SELECT...

</cfquery>

Then have something where you assign each query item to the variabled you use in the form:

<cfset user_email = myquery.email>

When you load the page, and the URL variable is null, you get a blank form.  When you pass a URL variable, the form is populated.  The only thing left is to let the processing page know whether you are inserting or updating.  I usually do this by changing the submit button.

<cfif URL.item eq "">

<input name="additem" type="submit" value="Add Record" />

<cfelse>

<input name="updateitem" type="submit" value="Update Record" />

</cfif>

The on the processing page:

<cfif StructKeyExists(FORM,"additem")>

--- INSERT query here ---

</cfif>

<cfif StructKeyExists(FORM,"updateitem")>

--- UPDATE query here ---

</cfif>

The other way I've used is to use IsDefined() for your form element values, but I've had inconsistent results from IsDefined, so I avoid using it unless I have to.

[Dan_Bracuk]

We all have our own way of doing things.  Personally, I try to avoid having both a form scope and a url scope on the same page.  I'm very easily confused so I keep things as simple as possible.

[WolfShade]

Every database record should have a unique ID.  Doesn't matter if it's plain integers, alphanumeric, or a generated UID.

If you give the form a field for the ID of the record and default it to 0, this will be beneficial.  On the query, set it to check for the value of the ID - if it's 0, then write code to INSERT; if it's anything else, write code to UPDATE.

^_^

[iccsi]

Yes, thanks for the message and help,

Web form has POST and GET method.

I can use POST method form and running a stored procedure to check primary key in the table and doing insert or update in the stored procedure.

Please let me know if I am wrong,

Thanks again for the message and help,

Regards,

Iccsi,