Insert Multiple Records and newest record id
8 Message(s) by 2 Author(s) originally posted in cfml discussion
| From: SonOfNels |
Date: Wednesday, October 24, 2007
|
The problem I have is I am using a somewhat normalized
database and I am trying
to enter multiple
record s from a form into a
table called catfit. It also needs
to add information to another table called prodmat and
pull the id of the
record I just added to the prodmat table and insert it into the catfit table in
one shot.
Promat - Holds all of the products
columns in this table
prodid
mold
desc
features
pic
Catfit - Table that
link s Prodmat and Modcats
columns in this table
prodid
catid
modlid
Now the form I have
<form method="post" action="action.cfm">
<select name="modlid">
<option value ="#modlid#" selected="selected">#modlid#
</option>
<option value="1">Sewing Machines
</option>
<option value="9">Vacuums
</option>
<option value="12">Air Purifiers
</option>
</select> <select name="catid">
<option value ="#catid#" selected="selected">#catid#
</option>
<option value="None">None
</option>
<option value="1">Sewing Machines-Embroidery
</option>
<option value="2">Sewing Machines-Computerized
</option>
<option value="3">Sewing Machines-Professional
</option>
<option value="4">Sewing Machines-Quilting
</option>
<option value="5">Sewing Machines-Electronic
</option>
<option value="6">Sewing Machines-Value Machines
</option>
<option value="7">Sewing Machines-Sergers
</option>
<option value="8">Sewing Machines-Specialty
</option>
<option value="9">Air Purifier
</option>
<option value="10">Vacuums-Upright
</option>
<option value="11">Vacuums-Canister
</option>
<option value="12">Vacuums-Central
</option>
</select>
<select name="catid">
<option value ="#catid#" selected="selected">#catid#
</option>
<option value="None">None
</option>
<option value="1">Sewing Machines-Embroidery
</option>
<option value="2">Sewing Machines-Computerized
</option>
<option value="3">Sewing Machines-Professional
</option>
<option value="4">Sewing Machines-Quilting
</option>
<option value="5">Sewing Machines-Electronic
</option>
<option value="6">Sewing Machines-Value Machines
</option>
<option value="7">Sewing Machines-Sergers
</option>
<option value="8">Sewing Machines-Specialty
</option>
<option value="9">Air Purifier
</option>
<option value="10">Vacuums-Upright
</option>
<option value="11">Vacuums-Canister
</option>
<option value="12">Vacuums-Central
</option>
</select> <select name="catid">
<option value ="#catid#" selected="selected">#catid#
</option>
<option value="None">None
</option>
<option value="1">Sewing Machines-Embroidery
</option>
<option value="2">Sewing Machines-Computerized
</option>
<option value="3">Sewing Machines-Professional
</option>
<option value="4">Sewing Machines-Quilting
</option>
<option value="5">Sewing Machines-Electronic
</option>
<option value="6">Sewing Machines-Value Machines
</option>
<option value="7">Sewing Machines-Sergers
</option>
<option value="8">Sewing Machines-Specialty
</option>
<option value="9">Air Purifier
</option>
<option value="10">Vacuums-Upright
</option>
<option value="11">Vacuums-Canister
</option>
<option value="12">Vacuums-Central
</option>
</select>
<input type="text" name="modl" value="#modl#" style="form">
<input type="text" name="pic" value="#pic#" style="form">
<textarea name="desc" rows="25" cols="60" wrap="virtual"
value="#desc#">#desc#
</textarea>
<textarea name="features" rows="25" cols="60" wrap="virtual"
value="#features#">#features#
</textarea>
<input type="submit" value="Submit" style="form" name="submit">
</form>
I have worked with the action.CFM page to no avail what so ever so I won't
even post it. Now the prodmat table is the table where I need to get the prodid
(Autonumber column) for the record that was just inserted so I can insert that
number into the catfit table under the prodid column (number column). So this
what I hoping to get in the end.
When someone submits this form it'll add the data to the prodmat table and
grab the prodid generated from the new record. Then instert a new record for
each of the catid select boxes into the catfit table. so something like this.
If they select all three categories upon the submit it'd add to the catfit
table for each record:
molid = 1
catid = 2
prodid = 16 (for instance this'd be the newest record added to the prodmat
table)
molid = 1
catid = 6
prodid = 16
molid = 1
catid = 8
prodid = 16
Please help I am loosing my hair over this!
| From: cf_dev2 |
Date: Wednesday, October 24, 2007
|
| From: SonOfNels |
Date: Saturday, October 27, 2007
|
I checked the link out and followed the tutorial and I have one problem I
cannot get past.
http://www.dmxzone.com/forum/topic.asp?topic_id=33515
For the sake of sanity I posted a link to someone elses exact same problem.
Can someone tell me why this'd happen and how to correct it?
| From: SonOfNels |
Date: Saturday, October 27, 2007
|
I tried to CFOUTPUT the values for my form and they are correct as displayed, but when I check the database they are all the the same.
| From: cf_dev2 |
Date: Saturday, October 27, 2007
|
Can someone tell me why this'd happen and how to correct it?
Their problem is the UPDATE doesn't uniquely identify the individual record
they wish to update. The UPDATE statement translates to "update all records
where the
parent ID = 2". Since there are two records that match that
condition, both records get updated:
ID, PartNumber, Qty
12 xyzabc 2
12 abcxyz 3
If you want to update a specific record, your WHERE
clause must use that
record's unique
key (or whatever
combination of keys uniquely identify it). In
your table the unique key might be:
WHERE catid = 2
AND prodid = 16
That is assuming your table does not allow duplicate catid + prodid entries.
Another option is to add an autonumber column to the table and use that value
in the WHERE clause.
| From: SonOfNels |
Date: Saturday, October 27, 2007
|
After taking your suggestion, I added a auto number
field (
CID , It is a hidden
input field called CDI) to the catfit table and set my
query up like so:
<CFLOOP
index="LoopCount"
from=1
to=#Val(form.locCount)#>
<CFSET variables.locID="form.locID#Evaluate(LoopCount)#">
<CFSET variables.locID="#Evaluate(variables.locID)#">
<CFSET variables.catid="form.catid#Evaluate(LoopCount)#">
<CFSET variables.catid="#Evaluate(variables.catid)#">
<CFQUERY
datasource="absi">
UPDATE catfit
SET
catid= #variables.catid#
WHERE
prodid= #variables.locID# AND CID=#form.CDI#
</CFQUERY>
<CFOUTPUT>
#catid#
#variables.locID#
</CFOUTPUT>
</CFLOOP>
This is the
error it sends back. I understand what it means I just don't know
how to send one number at a time instead of all three.
ODBC Error Code = 37000 (Syntax error or access violation)
[
Microsoft ][ODBC Microsoft
Access Driver] Syntax error (comma) in query
expression 'prodid= 163 AND
<BR>CID=125,126,127'.
<BR>
| From: SonOfNels |
Date: Saturday, October 27, 2007
|
SOrry never mind the br tags I meant them to be [B]bold[B].
| From: cf_dev2 |
Date: Saturday, October 27, 2007
|
Number the CDI fields just like you're doing with form.catID. So you will have
form.catId1, form.CID1
form.catId2, form.CID2
form.catId3, form.CID3
... Nothing to do with your error but a few observations:
>
<CFSET variables.catid="form.catid#Evaluate(LoopCount)#">
>
<CFSET variables.catid="#Evaluate(variables.catid)#">
1. You do not need evaluate(). Just use
array notation
<CFSET variables.catid = form["catid" & LoopCount] >
> WHERE prodid= #variables.locID# AND CID=#form.CDI#
2. If CID uniquely identifies an individual record, you do not need to
include
prodid
> CID, It is a hidden input field called CDI
3. Give the input field the same name. Consistency makes
code easier to
follow. Also, consider using more descriptive table and column names rather
than acronyms, which can be cryptic or ambigous.
Next Message: leading zero
Blogs related to Insert Multiple Records and newest record id
Click here to get all the information you need
baby watch metal tree painted wall sculpture andrea del c mexico christian college association gospel hymn john wayne osculati pictures of girls on motorcycles bigmouthfulls.com 966 502316615 umbilical
cord piper j-3 cub how are
...
click here python untrusted interpreter delicate sound of thunder ...
... general de cecyteh midifiles cinderella movie theme free downloadable xplite ideas para elaborara una maqueta del sistema respiratorio para pythonpath set animaux sex femme au maroc pain and spinal
cord and kainic acid flash video
...
Home of the funny and useful products.
ati rage 128 drivers m&s fabric mearsk india 3.579 masonic swords aveda pensacola fl nanoresolution raa south australia albertson foods. flac converter
cord blood stem cells bank aviary community in georgia southside community hospital
...