access relationships and coldfusion

[P_Roberts]

Hi guys, right a little help please.....I'm now a lot further on than last time
I posted on here, your help the first time around was very good :)

My Access database is now a relational one, with two tables dRegDetails and
dSubscriptionDetails, with the primary key of the former being an AutoNumber,
and another primary key of the latter also being an AutoNumber. To form the
relationship between the two tables I then also have the primary key from the
first table as a foreign key in the second table, with an equal join type -
this foreign key field is of a Number data type, not AutoNumber (as far as I'm
aware can't insert data into an autonumber - it gets generated automatically).
The only real need for this foreign key is to identify related records with the
first table.

All fine and seems to work in Access. Now come to submitting my form data, and
ColdFusion returns the below error. I presume it's because an AutoNumber data
type is a complex one (not sure which one though?) and obviously a Number is a
simple value. The CF documentation says it can't convert complex to simple
data types - but was wondering if I can extract the value of the complex
datatype and insert it into a new variable as a simple datatype???

The code : (dID is the primary key in the first table)

<cfquery datasource="cfukdoorstaff" name="pKey2fKey">
SELECT dID FROM dRegDetails
WHERE dEmail = '#FORM.dEmail#'
</cfquery>
<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dInitialDateReg)
VALUES (
'#pKey2fKey#'
'#currentDate#'
)
</cfquery>

The error in the browser :

"Complex object types cannot be converted to simple values.

The expression has requested a variable or an intermediate expression result
as a simple value, however, the result cannot be converted to a simple value.
Simple values are strings, numbers, boolean values, and date/time values.
Queries, arrays, and COM objects are examples of complex values.

The most likely cause of the error is that you are trying to use a complex
value as a simple one. For example, you might be trying to use a query variable
in a <CFIF> tag. This was possible in ColdFusion 2.0 but creates an error in
later versions.

The error occurred in
C:\CFusionMX7\wwwroot\ukdoorstaff\InsertRegDetails NEWa.cfm: line 35

33 : INSERT INTO dSubscriptionDetails (dID, dInitialDateReg)
34 : VALUES (
35 : '#pKey2fKey#'
36 : '#currentDate#'
37 : )

".

Hope you can help me :)

[paross1]

You probably should remove the single quotes from around the '#pKey2fKey#'
value, since you stated that it is numeric. Also, you are missing a comma
between your VALUES

INSERT INTO dSubscriptionDetails (dID, dInitialDateReg)
VALUES (#pKey2fKey#, '#currentDate#')

Phil

[mxstu]

You're missing the query column name in your VALUES(...) clause. Use

#pKey2fKey.dID#

... instead of just ...

#pKey2fKey#

That is probably what is causing the error. #pKey2fKey# is a query (ie.
complex ) object.

[mxstu]

Although you could also use a single query instead of the two separate queries

<cfquery datasource="cfukdoorstaff" name="insertSubDetails">
INSERT INTO dSubscriptionDetails (dID, dInitialDateReg)
SELECT dID, '#currentDate#'
FROM dRegDetails
WHERE dEmail = '#FORM.dEmail#'
</cfquery>