Unable to update Access record

[Eric_Dahlenburg]

Hi,

I have a page that I use to document the issue of a part from the database.
It is suppose to create a record in the table "Issue" documenting the parts reduction (This part works)
It is also suppose to de-increment the part quantity in the table "Main_Table" via an update record server behavior.

That is the part that doesn't work. I have attached the code for this CFM file (sorry, its kinda long...) Any ideas what I am doing wrong here?

Thank You in advance,

Eric

Ps You can view the actual page at: http://devo.dns2go.com/Issue.cfm?PartNumber=1234&Contract=Aries%201X
You will need to log-in. Use Name: User Password: User

[Newsgroup_User]

your INSERT query is inside this <cfif> block:

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"Issue_frm">

while your UPDATE query is inside a different <cfif>:

<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ
"Issue_frm">

do you have BOTH MM_InsertRecord AND MM_UpdateRecord fields defined in
your form? and they both have "Issue_frm" values?

if the above is not an issue - do you get any errors on execution of
your UPDATE query or does it just not execute?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Kapitaine]

I'm not sure, but a few points to make would be:

1) Can't login to your application, it's LOCALHOST!
2) Why on Earth do ou set a variable three times? e.g:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

Mikey.

[Eric_Dahlenburg]

Thanks for the reply...

I looks like left over artifacts from using the Dreamweaver front-end. I have corrected
that problem. As for logging in, Here is the correct URL: http://devo.dns2go.com/Issue.cfm?PartNumber=1234&Contract=Aries%201X Sorry about that !

As for the update problem, it looks like Azadi spotted it, more on the reply to his post...

[Eric_Dahlenburg]

Azadi ,

Looks like you spotted the problem. I re-did the page with just the update behavior on that form button and it works fine. I then added the insert behavior to the same button and lost the update action. Looks like I cannot have an Insert AND Update behavior on the same form.

Here is a screenshot of what I am trying to accomplish: http://spacecoasthomes.net/shot.jpg

Basically, I want to update the "Update" table and Insert into the "Main_Table" from the same form, using the hidden field values and the two text fields. Any ideas?

Thanks,

Eric

Ps My original post had the wrong URL for the bad page. The correct URL is: http://devo.dns2go.com/Issue.cfm?PartNumber=1234&Contract=Aries%201X

[Newsgroup_User]

hmm... your screenshot did not really divulge any useful information...

but if all you want to do is have 2 actions on your form - Insert and
Update - AND your Insert action should also ALWAYS update the
main_table, than all you need to do is copy your UPDATE query into the
<cfif> block that inserts the record.

basically, your form will:
only update a table if UPDATE action is performed;
insert new record AND update another table if INSERT action is performed

fine details of the code will depend on how your form is constructed and
if all necessary form fields are available for both actions, but in a
nutshell, your form action code will look something like:

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"Issue_frm">
<cfquery ...>
INSERT ...
</cfquery>
<cfquery ...>
UPDATE ...
</cfquery>
</cfif>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ
"Issue_frm">
<cfquery ...>
UPDATE ...
</cfquery>
</cfif>


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Eric_Dahlenburg]

Azadi,

Thanks for the reply...

Can you clarify something in your reply?


Your reply made it seem that I can only update and insert from the same form if
both actions are to the same table. Can I update table A and insert into table B from the same form?
I have attached relevant code from my latest attempt. Any help would be appreciated.

Eric

[Newsgroup_User]

Azadi,

Thanks for the reply...

Can you clarify something in your reply?


Your reply made it seem that I can only update and insert from the same form if
both actions are to the same table. Can I update table A and insert into
table B from the same form?
I have attached relevant code from my latest attempt. Any help would be
appreciated.

Eric




<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ
"Issue_Frm">
<cfquery datasource="Parts" username="admin" password="dallas">
UPDATE Main_Table
SET Quantity=
<cfif IsDefined("FORM.hiddenField_Quantity") AND #FORM.hiddenField_Quantity#
NEQ "">
<cfqueryparam value="#FORM.hiddenField_Quantity#"
cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
WHERE Part_Number=
<cfqueryparam value="#FORM.hiddenField_PN#" cfsqltype="cf_sql_clob"
maxlength="50">
AND NEMS_Tag=
<cfqueryparam value="#FORM.hiddenField_NEMS#" cfsqltype="cf_sql_clob"
maxlength="50">
AND Contract_num=
<cfqueryparam value="#FORM.hiddenField_ConNum#" cfsqltype="cf_sql_numeric">
</cfquery>
<cflocation url="index.cfm">
</cfif>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"Issue_Frm">
<cfquery datasource="Parts" username="admin" password="dallas">
INSERT INTO Issue (Part_Number, "Date", User_Name, WAD, Contract, NEMS_Tag)
VALUES (
<cfif IsDefined("FORM.hiddenField_PN") AND #FORM.hiddenField_PN# NEQ "">
<cfqueryparam value="#FORM.hiddenField_PN#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.hiddenField_Date") AND #FORM.hiddenField_Date# NEQ "">
<cfqueryparam value="#FORM.hiddenField_Date#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.hiddenField_User") AND #FORM.hiddenField_User# NEQ "">
<cfqueryparam value="#FORM.hiddenField_User#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.textfield_WAD") AND #FORM.textfield_WAD# NEQ "">
<cfqueryparam value="#FORM.textfield_WAD#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.hiddenField_ConName") AND #FORM.hiddenField_ConName#
NEQ "">
<cfqueryparam value="#FORM.hiddenField_ConName#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.hiddenField_NEMS") AND #FORM.hiddenField_NEMS# NEQ "">
<cfqueryparam value="#FORM.hiddenField_NEMS#" cfsqltype="cf_sql_clob"
maxlength="50">
<cfelse>
''
</cfif>
)
</cfquery>
<cflocation url="index.cfm">
</cfif>




<!--- Here is the Form --->

<form id="Issue_Frm" name="Issue_Frm" method="POST"
action="<cfoutput>#CurrentPage#</cfoutput>">
<table width="477" height="53" border="1" cellpadding="3" cellspacing="1">
<tr>
<td width="95"><strong>WAD Info:</strong></td>
<td width="361"><label>
<input name="textfield_WAD" type="text" id="textfield_WAD" />
<input type="submit" name="Submit" value="Submit" />
<input name="hiddenField_User" type="hidden" id="hiddenField_User"
value="<cfoutput>#Session.MM_Username#</cfoutput>" />
<input name="hiddenField_PN" type="hidden" id="hiddenField_PN"
value="<cfoutput>#Info.Part_Number#</cfoutput>" />
<input name="hiddenField_Date" type="hidden" id="hiddenField_Date"
value="<CFOUTPUT>#DateFormat(Now(), "mm/dd/yyyy")#</CFOUTPUT>" />
<input name="hiddenField_NEMS" type="hidden" id="hiddenField_NEMS"
value="<cfoutput>#Info.NEMS_Tag#</cfoutput>" />
<input name="hiddenField_ConNum" type="hidden"
id="hiddenField_ConNum" value="<cfoutput>#Info.Contract_num#</cfoutput>" />
<input name="hiddenField_Quantity" type="hidden"
id="hiddenField_Quantity" value="<cfoutput>#Info.Quantity-1#</cfoutput>" />
<input name="hiddenField_ConName" type="hidden"
id="hiddenField_ConName" value="<cfoutput>#Info.Name_of_Contract#</cfoutput>" />
</label></td>
</tr>
</table>
<input type="hidden" name="MM_UpdateRecord" value="Issue_Frm">
<input type="hidden" name="MM_InsertRecord" value="Issue_Frm">
</form>
<p> </p>
<p> </p>
</body>
</html>

[Newsgroup_User]

as long as the FORM contains the data needed for both actions (INSERT
and UPDATE), you can insert/update that data into as many tables in as
many databases as you want.

from your code & explanations it looks like you want to:
1) UPDATE a record in Table A if the form's action is update ()
2) INSERT a new record into Table B and UPDATE existing record in Table
A if the form's action is update ()

based on the above, your form's action code should look something like:

<!--- form's action is UPDATE --->
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ
"Issue_Frm">
<!--- just doing update --->
<cfquery datasource="Parts" username="admin" password="dallas">
UPDATE Main_Table
SET ...
WHERE ...
</cfquery>
<cflocation url="index.cfm"><cfabort>
</cfif>
<!--- form's action is INSERT --->
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"Issue_Frm">
<!--- insert record into Issues table ... --->
<cfquery datasource="Parts" username="admin" password="dallas">
INSERT INTO Issue (Part_Number, "Date", User_Name, WAD, Contract,
NEMS_Tag)
VALUES (...)
</cfquery>
<!--- ... AND update the Main_Table table --->
<cfquery datasource="Parts" username="admin" password="dallas">
UPDATE Main_Table
SET ...
WHERE ...
</cfquery>
<cflocation url="index.cfm"><cfabort>
</cfif>


hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Eric_Dahlenburg]

Thank You so much for the help. For the basis of other viewing this thread, I have attached the working code. When the submit button is active, data is updated to the Issue table and a new record is written to the Main_Table table.

Thanks,

Eric

[Eric_Dahlenburg · Accepted solution]

Thank You so much for the help. For the basis of other viewing this thread, I have attached the working code. When the submit button is active, data is updated to the Issue table and a new record is written to the Main_Table table.

Thanks,

Eric