He is my solution, Two separate queries
<cfquery name="InsertOwner"
datasource="#Request.MainDSN#">
INSERT INTO Owners(
FIRSTNAME,
LASTNAME,
EMAIL,
STREET,
UNITNUMBER,
CTY,
STATE,
ZIP,
PHONE,
PROJECT,
NUMBEROFUNITS,
NOTES
)
VALUES (
'#Trim(Form.firstName)#',
'#Trim(Form.lastName)#',
<cfif Len(Trim(Form.emailAddress)) GT 0>
'#Trim(Form.emailaddress)#'
<cfelse>
NULL
</cfif>,
'#Trim(Form.street)#',
<cfif Len(Trim(Form.unit)) GT 0>
'#Trim(Form.unit)#'
<cfelse>
NULL
</cfif>,
'#Trim(Form.city)#',
'#Trim(Form.state)#',
'#Trim(Form.zip)#',
<cfif Len(Trim(Form.phone)) GT 0>
'#Trim(Form.phone)#'
<cfelse>
NULL
</cfif>,
'#Trim(Form.property)#',
<cfif Len(Trim(Form.numUnits)) GT 0>
'#Trim(Form.numUnits)#'
<cfelse>
NULL
</cfif>,
<cfif Len(Trim(Form.notes)) GT 0>
'#Trim(Form.notes)#'
<cfelse>
NULL
</cfif>
)
</cfquery>
<cfquery name="getid" datasource="#Request.MainDSN#">
SELECT OWNERID, FIRSTNAME, LASTNAME
FROM OWNERS
WHERE FIRSTNAME = <cfqueryparam
value="#form.firstname#">
AND
LASTNAME = <cfqueryparam value="#form.lastname#">
</cfquery>
//everything below this line is new
<cfoutput query="getid">
<cfset owner_id = #ownerid#>
<cfquery datasource="#Request.MainDSN#" name="putdate">
INSERT INTO DATES(
OWNERID,
INITIALCALL
)
VALUES(
#owner_id#,
#form.datecontacted#
)
</cfquery>
</cfoutput>
But now I am running into another problem with the date.
I get the insert with the correct id, but the information contained
within the #form.datecontacted# is the time, and not the actual date.
I'm thinking it has something to do with what the <cfinput
type=datefield ...> passes to the actionpage. I'm sure that the actual
date is there. Am I on the right track with this, or not?
-----Original Message-----
From: Mark Brown [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 01, 2006 1:51 PM
To: CF-Newbie
Subject: Re: inserting into two different tables
Hey,
I just struggle with the same thing, though I may be a bit confused as
to
exactly what your doing. But, if you look at my blog,
http://markbrown.instantspot.com/blog/. It may help you, or it may not.
Eitherway, I'm curiouse to see your final solution to the issue.
Thanks and good luck,
Mark
On 11/1/06, Robert Makowski <[EMAIL PROTECTED]> wrote:
>
> Something like this:
>
> <cfquery ..>
> .....
> SELECT SCOPTE_IDENTITY()
> </cfquery>
>
> <cfquery ..>
> INSERT INTO DATES(
> ...
> )
> VALUES (
> ....
> )
> </cfquery>
>
> Or am I completely wrong?
>
> -----Original Message-----
> From: Briggs, Meg [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 01, 2006 12:00 PM
> To: CF-Newbie
> Subject: RE: inserting into two different tables
>
> Just before your </cfquery> and after the closing parentheses:
>
> )
> SELECT SCOPE_IDENTITY()
> </cfquery>
>
> -----Original Message-----
> From: Robert Makowski [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 01, 2006 10:36 AM
> To: CF-Newbie
> Subject: RE: inserting into two different tables
>
> I'm using an access db (really small app)
>
> Here is the add function. I'm unclear where to put the SELECT
> SCOPE_IDENTITY()
>
> cfquery name="InsertOwner"
> datasource="#Request.MainDSN#">
>
> INSERT INTO Owners(
> FIRSTNAME,
> LASTNAME,
> EMAIL,
> STREET,
> UNITNUMBER,
> CTY,
> STATE,
> ZIP,
> PHONE,
> PROJECT,
> NUMBEROFUNITS,
> NOTES
> )
> VALUES (
> '#Trim(Form.firstName)#',
> '#Trim(Form.lastName)#',
> <cfif Len(Trim(Form.emailAddress)) GT 0>
> '#Trim(Form.emailaddress)#'
> <cfelse>
> NULL
> </cfif>,
> '#Trim(Form.street)#',
> <cfif Len(Trim(Form.unit)) GT 0>
> '#Trim(Form.unit)#'
> <cfelse>
> NULL
> </cfif>,
> '#Trim(Form.city)#',
> '#Trim(Form.state)#',
> '#Trim(Form.zip)#',
> <cfif Len(Trim(Form.phone)) GT 0>
> '#Trim(Form.phone)#'
> <cfelse>
> NULL
> </cfif>,
> '#Trim(Form.property)#',
> <cfif Len(Trim(Form.numUnits)) GT 0>
> '#Trim(Form.numUnits)#'
> <cfelse>
> NULL
> </cfif>,
> <cfif Len(Trim(Form.notes)) GT 0>
> '#Trim(Form.notes)#'
> <cfelse>
> NULL
> </cfif>
> )
>
> </cfquery>
>
>
>
> The field is call datecontacted which needs to go into a table called
> dates.
>
>
> -----Original Message-----
> From: Daniel Kessler [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 01, 2006 11:21 AM
> To: CF-Newbie
> Subject: Re: inserting into two different tables
>
> What is your database?
> If you're using Oracle (and I only really know oracle), you can use:
>
> <CFQUERY NAME="get_id" DATASOURCE="dpch">
> Select my_sequence.NextVal as id From DUAL
> </CFQUERY>
>
> and then use the variable ID in each of the two inserts.
>
> Or you can do one insert, query and then do a second insert. You
> probably should block that within a cftransaction (I think).
>
> On Nov 1, 2006, at 11:03 AM, Robert Makowski wrote:
>
> > I have a fairly basic form that I am submitting. It's a simple add
> > form: first, last name, address, email, etc., but there is a date
> > field
> > that I'm putting into another table. The date table has a bunch of
> > date
> > fields, and the foreign key is the ownerid from the other table.
The
> > problem is that I'm creating a new owner with a new id, so I can't
> > just
> > to two inserts, because I the owenerid hasn't been created yet.
Could
> > someone help me with this?
> >
> >
> >
> > Thanks.
> >
> >
> >
> > Rob Makowski
> >
> >
>
>
> --
>
> Daniel Kessler
>
> College of Health and Human Performance
> University of Maryland
> Suite 2387 Valley Drive
> College Park, MD 20742-2611
> Phone: 301-405-2545
> http://hhp.umd.edu
>
>
>
>
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2187
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
