Given fieldname could not be found in the table.

[Pappy17]

Another newbie question here. I am trying to update one table ("Table A") and
insert into another table ("Table B"):

<cfquery DATASOURCE="tech support survey">
UPDATE Table A
SET DateRecd=#Now()#
WHERE AdminID = #AdminID#
</cfquery>
<cfinsert datasource="tech support survey" tablename="Table B">

When I try to do this, I get an error saying that AdminID cannot be found in
Table B. Admin ID has nothing to do with Table B.

What am I doing wrong?

Thanks!

[mxstu]

This is just guessing... but it may be a scope problem. I assume #AdminID# is
a form field variable? Since you do not specify the fields to insert in your
CFINSERT statement, the tag may just be grabbing all of the form field names
and attempting to insert them all into your table. Since you say that AdminID
does not exist in Table B, CFINSERT throws an error.

Try specifying the names of the columns to insert by using the "formFields"
attribute of CFINSERT.

Also, you should scope your variables, for example:

#form.myID#
#url.myParam#
etc....

[Pappy17]

Thanks, mxstu. Now I'm getting a "Syntax error in INSERT INTO statement" message. I haven't tried putting "form." in front of the variables yet.

[Abinidi]

<cfquery DATASOURCE="tech support survey">
UPDATE Table A
SET DateRecd=#Now()#
WHERE (AdminID = #AdminID#)
</cfquery>

Surround the WHERE in parenthesis...
Try that

[mxstu]

Can you post the new code and the complete error message?

[Mountain Lover]

Historically, it was a bad idea to have spaces in table/field names and
they will cause errors. You can get around this by surrounding the table
name in quotes 'Table A' or renaming it.
HTH


--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[mxstu]

Originally posted by: Newsgroup User
Historically, it was a bad idea to have spaces in table/field names and
they will cause errors. You can get around this by surrounding the table
name in quotes 'Table A' or renaming it.


Agreed. IMO spaces in table names are not a good idea. I assumed (perhaps
incorrectly) that "Table A" was not the real table name ... or more accurately
that the real table name did not have a space in it ;-)

[Pappy17]

Thanks to everyone for their help!

mxstu, your assumption was correct. I was using "Table A" and "Table B" for
the sake of clarity.

Here is my code:
<cfquery DATASOURCE="tech support survey">
UPDATE IMSAdmin
SET DateRecd=#Now()#
WHERE AdminID = #AdminID#
</cfquery>
<cfinsert
DATASOURCE="tech support survey"
tablename="IMSSurvey"
formfields="ID, First, Last, CompName, Street1, City, State, Zip, PhoneNum,
CustID, Email, Product, PMgr, PS1, PS2, PMQuest1, PMQuest2, PMQuest3, PMQuest4,
PMQuest5, PMComment, PSQuest1, PSQuest2, PSQuest3, PSQuest4, PSQuest5,
PSComment, GIQuest1, GIQuest2, GIQuest3, GIQuest4, GIComment, BeAReference,
ReferUs, AddlComment, Date, Time, RName, RecipEmail, DateSent"

>

The error message I get now is:
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] You tried to assign the Null value to a variable that is not a Variant
data type.

The Error Occurred in D:\Inetpub\isi-info.com\Surveys\IMS\thanks.cfm: line 15

13 : tablename="IMSSurvey"
14 : formfields="ID, First, Last, CompName, Street1, City, State, Zip,
PhoneNum, CustID, Email, Product, PMgr, PS1, PS2, PMQuest1, PMQuest2, PMQuest3,
PMQuest4, PMQuest5, PMComment, PSQuest1, PSQuest2, PSQuest3, PSQuest4,
PSQuest5, PSComment, GIQuest1, GIQuest2, GIQuest3, GIQuest4, GIComment,
BeAReference, ReferUs, AddlComment, Date, Time, RName, RecipEmail, DateSent"
15 : >
16 : <cfquery NAME="thanks" DATASOURCE="tech support survey" maxrows="1">
17 : SELECT First, Last, RName

[jorgepino]

I would check you ID field
is a autonumber?

[Pappy17]

Yes, jorgepino, it is autonumber.

Thanks.

[jorgepino]

if you trying to add ID number to a field with auto number you may get the error
take the ID out to the list and see what happend or turn the autonumbering off

[Pappy17]

jorgepino,

I left the ID in, and turned autonumbering off. I now get an "Index or primary key cannot contain a Null value. " error. Should I do something in Access?

Thanks.

[mxstu]

Pappy17,

Leave the [ID] number out as jorgepino mentioned. You don't want to insert the unique record ID, you want it to be be automatically created by Access when you insert the other fields.

[Pappy17]

Thanks, mxstu.

I took out the ID number from <cfinsert> , and now I get this ever popular
error:

Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] Too few parameters. Expected 1.

The Error Occurred in D:\Inetpub\isi-info.com\Surveys\IMS\thanks.cfm: line 34

32 : FROM IMSSurvey
33 : ORDER BY ID DESC
34 : </cfquery>
35 : <cfmail
36 : QUERY="Thanks2"

--------------------------------------------------------------------------------

SQL SELECT ID, First, Last, CompName, Street1, City, State, Zip, PhoneNum,
Email, Product, Date, BeAReference, CustID, ReferUs, PMgr, PS1, PS2, PMQuest1,
PMQuest2, PMQuest3, PMQuest4, PMQuest5, PMComment, PSQuest1, PSQuest2,
PSQuest3, PSQuest4, PSQuest5, PSComment, GIQuest1, GIQuest2, GIQuest3,
GIQuest4, GIComment, AddlComment, RName FROM IMSSurvey ORDER BY ID DESC
DATASOURCE tech support survey
VENDORERRORCODE -3010
SQLSTATE 07002

Here's the code (lines 21-34):
<cfquery NAME="thanks2" DATASOURCE="tech support survey" maxrows="1">
SELECT ID, First, Last, CompName, Street1, City, State, Zip, PhoneNum, Email,
Product, Date, BeAReference, CustID, ReferUs, PMgr, PS1, PS2,

PMQuest1, PMQuest2, PMQuest3, PMQuest4, PMQuest5, PMComment,

PSQuest1, PSQuest2, PSQuest3, PSQuest4, PSQuest5, PSComment,

GIQuest1, GIQuest2, GIQuest3, GIQuest4, GIComment,

AddlComment, RName

FROM IMSSurvey
ORDER BY ID DESC
</cfquery>

Thanks everyone again for all your help.

[mxstu]

So this error isn't from the CFINSERT statement, it's from the next query
containing a SELECT statement? Just a guess, but I would think it's caused by
the column named "Date". Date is a reserved word. Try changing it to
something else .. ex. myDateColumn.

[mxstu]

Hmm .. once you get the "too few parameters ..." error fixed... are you depending on that SELECT statement to give you the information just inserted by the CFINSERT statement?

[jorgepino]

poppy17
I would recomend for you to use the
<CFQUERY DATASOURCE="indium">
INSERT INTO dbo.tEmployee (First, Last, CompName, ... )
SELECT '#Form.First#', '#Form.Last#','#form.CompName#' .....
</CFQUERY>
also you need to change the Date and time field they are reserved word and
will couse problems

[Pappy17]

mxstu and jorgepino,

Thanks so much for all your help. I changed the name of the Date and Time
columns, and that did the trick!

mxstu, yes, I am depending the SELECT statement to provide information just
inserted by the CFINSERT statement.

[mxstu]

Originally posted by: Pappy17
mxstu, yes, I am depending the SELECT statement to provide information just
inserted by the CFINSERT statement.

You may have a problem with that. The current code assumes that no other
records will be inserted into TableB in between the CFINSERT and the SELECT
statement, which is not true. Another record can be inserted within that
window, and if this happens your select statement will return the wrong ID.

1) You should wrap all of the related queries within a CFTRANSACTION. This
will ensure that if one query fails, all of the queries will fail as a single
unit.

2) As jorgepino suggested, you can use a CFQUERY instead of CFINSERT. If you
use CFQUERY, you can use then SELECT @@IDENTITY to retrieve the correct ID of
the record just inserted.

Using the two steps above will help ensure that you retrieve the information
for the correct record ID.

[Pappy17]

Thanks again, mxstu. I'll make those changes.

I'm not a programmer, just a technical writer. I have enough problems with English let alone ColdFusion!