Insert and update/append in coldfusion

[RosieGp]

how do i write it in access. my table is in access... and i'm using coldfusion (CFM) to call this query.

I tried the following but it does not work: the record is added to Database even if the ID exists.

<cfquery name="GetData" datasource="MainDB">
SELECT * FROM NewUsersTable
<cfif IsDefined("form.ID") and "form.ID" eq "NewUsersTable
.ID">

<cfquery name="updatedata" datasource="MainDB">
UPDATE newUsersTable 
SET UserAddress = #session.UserAddress#
</cfquery>

<cfelse>
<cfquery name="insertdata" datasource="MainDB">    INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
    VALUES
    ('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#') 
</cfquery>
</cfif>
</cfquery>

Any help is appreciated. Thanks.

 

[philhege]

Hmm.

You have nested CFQUERY tags here.

Your first SELECT statement brings back every table in the NewUsersTable table.

The CFIF statement would evaluate the first record of the table. But your variables are contained in quotations, so CF is eveluating the literal strings, not the contents of the variables. Hence, the condition is never true, and the logic skips to the INSERT statement.

The update statement will update every record in NewUsersTable because there is no WHERE clause. Wouldn't you want to restrict this update to the record in question?

-----------
With business clients like mine, you'd be better off herding cats.

 

[RosieGp]

phil - I changed my query to the following:

<cfquery name="updatedata" datasource="MainDB">
UPDATE newUsersTable 
SET UserAddress = #session.UserAddress#
</cfquery>
<cfquery name="insertdata" datasource="MainDB">    INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
    VALUES
    ('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#') 
</cfquery>

So now the update query works fine, but how do I change the insert query so that a new record is inserted only if it does not exsits.

Please advise.

 

[RosieGp]

sorry i misses adding the where condition in update query

<cfquery name="updatedata" datasource="MainDB">
UPDATE newUsersTable 
SET UserAddress = #session.UserAddress#
where UserID=#form.UserID#
</cfquery>
<cfquery name="insertdata" datasource="MainDB">    
INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
VALUES
('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#') 
</cfquery>

So how can i change my insert query to add data if the UserId is not already added in teh database.

I tried using select with insert into but can't make it work.

please advise.

 

[RosieGp]

does anyone have a solution for the above issue. Help me with this please.
Thanks in advance.

 

[philhege]

Your problem is right here:

 <cfif IsDefined("form.ID") and "form.ID" eq "NewUsersTable.ID"

By using quotes, you're comparing two literal values, not the contents of the variables.

Try

 <cfif IsDefined("form.ID") and form.ID eq NewUsersTable.ID

On further inspection, the ID from the query should be GetData.ID, not NewUsersTable.ID, so

 <cfif IsDefined("form.ID") and form.ID eq GetData.ID etc.

-----------
With business clients like mine, you'd be better off herding cats.