asked on

Insert New Record into MS Access

HELP URGENT, I'm trying to insert a new record into ms access database for a survey, but current code below only updates the same record. I know this is simple but can't get it adding new record and it needs to be done by today. The AutoID is set to primary field in database, with "Number" not "Autonumber" as data type. thanks

<cfset ds="nlfcsdb">

<cfquery name="key" datasource="#ds#">
select max(AutoID) as AutoIDhold from nlfcssurvey
</cfquery>

<cfset holdtemp = #key.AutoIDhold#>


<cfquery name="keytest" datasource="#ds#">

UPDATE nlfcssurvey

SET

AutoID=#holdtemp#+1,
Academics='#Trim(ARGUMENTS.Academics)#',
AcademicsComments='#Trim(ARGUMENTS.AcademicsComments)#',
Faculty='#Trim(ARGUMENTS.Faculty)#',
FacultyComments='#Trim(ARGUMENTS.FacultyComments)#',
Athletics='#Trim(ARGUMENTS.Athletics)#',
AthleticsComments='#Trim(ARGUMENTS.AthleticsComments)#',
Spiritualvitality='#Trim(ARGUMENTS.Spiritualvitality)#',
SpiritualvitalityComments='#Trim(ARGUMENTS.SpiritualvitalityComments)#',
Facilities='#Trim(ARGUMENTS.Facilities)#',
FacilitiesComments='#Trim(ARGUMENTS.FacilitiesComments)#',
otherComments='#Trim(ARGUMENTS.otherComments)#',
resource='#Trim(ARGUMENTS.resource)#',
cur_datetime= #CreateODBCDate(ARGUMENTS.cur_datetime)#,
name='#Trim(ARGUMENTS.name)#'

</cfquery>

ASKER CERTIFIED SOLUTION

Wim

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nigel-SA

ASKER

almost there, what about the '

must it look like

SELECT' #holdtemp#+1', '#Trim(ARGUMENTS.Academics)#', ....

Nigel-SA

ASKER

Error Executing Database Query.
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

The error occurred in C:\CFusionMX7\wwwroot\newlifefc\survey\survey.cfc: line 96
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey_process.cfm: line 63
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey.cfc: line 96
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey_process.cfm: line 63

94 : INSERT INTO nlfcssurvey(AutoID,Academics,AcademicsComments,Faculty,FacultyComments,Athletics,AthleticsComments,Spiritualvitality,SpiritualvitalityComments,Facilities,FacilitiesComments,otherComments,resource,cur_datetime,name)
95 :
96 : SELECT #holdtemp#+1,'#Trim(ARGUMENTS.Academics)#','#Trim(ARGUMENTS.AcademicsComments)#','#Trim(ARGUMENTS.Faculty)#','#Trim(ARGUMENTS.FacultyComments)#','#Trim(ARGUMENTS.Athletics)#','#Trim(ARGUMENTS.AthleticsComments)#','#Trim(ARGUMENTS.Spiritualvitality)#','#Trim(ARGUMENTS.SpiritualvitalityComments)#','#Trim(ARGUMENTS.Facilities)#','#Trim(ARGUMENTS.FacilitiesComments)#','#Trim(ARGUMENTS.otherComments)#','#Trim(ARGUMENTS.resource)#',#CreateODBCDate(ARGUMENTS.cur_datetime)#,'#Trim(ARGUMENTS.name)#'
97 :
98 : FROM nlfcssurvey

Wim

The AutoID you want to insert already exists or if you have more fields that have the property 'Indexed" on 'Yes (No Duplicates)'.

just to test your query, change AutoID in your code to a number you're sure it doesn't exists.

SOLUTION

trailblazzyr55

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

@Nigel-SA,

Why not just make the column an autonumber?

Nigel-SA

ASKER

thanks, saved my bacon....