Problem inserting/updating records

[KevCB]

Hi

I'm having trouble trying to insert/update the records in SQL Server.

I have created a form based on two tables and created a select statement so I
can display the records on the form.

But when it comes to inserting/updating the record it doesn't seem to run the
function I created in the CFC file, and no error is displayed at all.

Here's the insert function from the CFC file: -

<!--- Insert a vacancy --->
<cffunction name="addVacancy" returntype="string">
<!--- Method arguments --->
<cfargument name="VacancyRef"
type="string"
required="yes"
hint="Vacancy reference">
<cfargument name="VacancyTitle"
type="string"
required="yes"
hint="Job title">
<cfargument name="VacancyDept"
type="string"
required="yes"
hint="Department">
<cfargument name="VacancyDetails"
type="string"
required="no"
hint="Vacancy Details">
<cfargument name="Gender"
type="string"
required="yes"
hint="Gender">
<cfargument name="MaritalStatus"
type="string"
required="yes"
hint="Marital status">
<cfargument name="AgeBand"
type="string"
required="yes"
hint="Age band">
<cfargument name="EthnicGroup"
type="string"
required="yes"
hint="Ethnic group">
<cfargument name="EthnicOrigin"
type="string"
required="yes"
hint="Ethnic origin">
<cfargument name="OtherEthnicDesc"
type="string"
required="no"
hint="Ethnic background">
<cfargument name="Disability"
type="any"
required="yes"
hint="Disability">
<cfargument name="DisabilityDetails"
type="string"
required="no"
hint="Disability details">
<cfargument name="AdvertSource"
type="string"
required="no"
hint="Publication">

<cftransaction>
<!---Insert vacancy --->
<cfquery datasource="#ds#">
INSERT INTO dbo.Vacancy(VacancyRef,
VacancyTitle,
VacancyDept,
VacancyDetails)
VALUES('#Trim(ARGUMENTS.VacancyRef)#',
'#Trim(ARGUMENTS.VacancyTitle)#',
'#Trim(ARGUMENTS.VacancyDept)#',
'#Trim(ARGUMENTS.VacancyDetails)#')
</cfquery>
<cfreturn true>

<!--- Insert Monitoring Data --->
<cfquery datasource="#ds#">
INSERT INTO dbo.MonitoringData(VacancyRef,
Gender,
MaritalStatus,
AgeBand,
EthnicGroup,
EthnicOrigin,
OtherEthnicDesc,
Disability,
DisabilityDetails,
AdvertSource)
VALUES('#Trim(ARGUMENTS.VacancyRef)#',
'#Trim(ARGUMENTS.Gender)#',
'#Trim(ARGUMENTS.MaritalStatus)#',
'#Trim(ARGUMENTS.AgeBand)#',
'#Trim(ARGUMENTS.EthnicGroup)#',
'#Trim(ARGUMENTS.EthnicOrigin)#',
'#Trim(ARGUMENTS.OtherEthnicDesc)#',
#ARGUMENTS.Disability#,
'#Trim(ARGUMENTS.DisabilityDetails)#',
'#Trim(ARGUMENTS.AdvertSource)#')
</cfquery>
<cfreturn true>
</cftransaction>
</cffunction>

Since I am inserting into two tables I have used the <cftransaction> tab to
use more than one query, am I using this tag correctly? I've never used it
before.

[KevCB]

Just a quick update:

I have managed to get the update function working now, but am still unable to
get the insert working. For some reason it is not inserting the records, and I
don't know why because I don't receive an error and nothing is mentioned in the
logs.

I have changed the code for the insert function slightly so it now reads:-

<!--- Insert a vacancy --->
<cffunction name="addVacancy" returntype="string">
<!--- Method arguments --->
<cfargument name="VacancyRef"
type="string"
required="yes"
hint="Vacancy reference">
<cfargument name="VacancyTitle"
type="string"
required="yes"
hint="Job title">
<cfargument name="VacancyDept"
type="string"
required="yes"
hint="Department">
<cfargument name="VacancyDetails"
type="string"
required="no"
hint="Vacancy Details">
<cfargument name="Gender"
type="string"
required="yes"
hint="Gender">
<cfargument name="MaritalStatus"
type="string"
required="yes"
hint="Marital status">
<cfargument name="AgeBand"
type="string"
required="yes"
hint="Age band">
<cfargument name="EthnicGroup"
type="string"
required="yes"
hint="Ethnic group">
<cfargument name="EthnicOrigin"
type="string"
required="yes"
hint="Ethnic origin">
<cfargument name="OtherEthnicDesc"
type="string"
required="no"
hint="Ethnic background">
<cfargument name="Disability"
type="string"
required="yes"
hint="Disability">
<cfargument name="DisabilityDetails"
type="string"
required="no"
hint="Disability details">
<cfargument name="AdvertSource"
type="string"
required="no"
hint="Publication">

<cftransaction>
<!---Insert vacancy --->
<cfquery datasource="#ds#">
INSERT INTO dbo.Vacancy(VacancyRef,
VacancyTitle,
VacancyDept,
VacancyDetails)
VALUES('#Trim(ARGUMENTS.VacancyRef)#',
'#Trim(ARGUMENTS.VacancyTitle)#',
'#Trim(ARGUMENTS.VacancyDept)#',
'#Trim(ARGUMENTS.VacancyDetails)#')
</cfquery>

<!--- Insert Monitoring Data --->
<cfquery datasource="#ds#">
INSERT INTO dbo.MonitoringData(VacancyRef,
Gender,
MaritalStatus,
AgeBand,
EthnicGroup,
EthnicOrigin,
OtherEthnicDesc,
Disability,
DisabilityDetails,
AdvertSource)
VALUES('#Trim(ARGUMENTS.VacancyRef)#',
'#Trim(ARGUMENTS.Gender)#',
'#Trim(ARGUMENTS.MaritalStatus)#',
'#Trim(ARGUMENTS.AgeBand)#',
'#Trim(ARGUMENTS.EthnicGroup)#',
'#Trim(ARGUMENTS.EthnicOrigin)#',
'#Trim(ARGUMENTS.OtherEthnicDesc)#',
'#Trim(ARGUMENTS.Disability)#',
'#Trim(ARGUMENTS.DisabilityDetails)#',
'#Trim(ARGUMENTS.AdvertSource)#')
</cfquery>
</cftransaction>
<cfreturn addVacancy>
</cffunction>

So just to clarify my problem, the function above doesn't seem to insert the
records into the two tables at all.

Can anyone help?

[CF_Oracle]

What the code of the calling page where you invoke component and it's method addVacancy?

[KevCB]

I've decided to place all the files that are used on my web hosting package
with my ISP rather than copy and pasting the code on here, I've saved them as
text files but for some reason it keeps running them as html files, so you will
have to save them to view them.

I have changed the code slightly for the CFC. I decided to try the insert
with the cfinsert tags rather than using the SQL syntax, but it still didn't
work.

The order in which the files run are as follows:

search.cfm - results.cfm - details.cfm - process.cfm

The idea is that when the user searches it displays the results. If the
VacancyRef is not passed to the details.cfm page then it knows that it is
inserting a record, else if the VacancyRef is passed to the page then it
displays the current record and allows you to update the record.

The process.cfm file is run when the submit button is clicked in the
details.cfm page, and it is this file that decides whether to update or insert
the record.

The text files can be found [url]http://kevincb.nadsl.net[/url].

[Dan Bracuk]

I see you are using cfinsert instead of cfquery. I never do that myself but I
have read on these forums that if your form fields don't include a primary key,
it won't work.

Could be something else though.

[KevCB]

Had a look through the code again, and added the primary keys in both cfinsert tags but that didn't work either :(

Thanks for the suggestion though.

[CF_Oracle]

1. In result page, in query <cfquery name="vSearch" datasource="HR">
SELECT *
FROM dbo.Vacancy
<!--- Search by vacancy reference --->
<cfif FORM.VacancyRef IS NOT "">
WHERE VacancyRef LIKE '%#FORM.VacancyRef#%'
</cfif>
.....
put WHERE outside cfif and also put cfif IsDefined like this:

SELECT *
FROM dbo.Vacancy
WHERE 1=1
<cfif IsDefined ("FORM.VacancyRef" AND FORM.VacancyRef IS NOT "">
VacancyRef LIKE '%#FORM.VacancyRef#%'
</cfif>
......
2. Provide duplicates checking before insert otherwise could be DB tables
columns unique constraints violation

[KevCB]

Hi

Could you explain what you mean in point 2, I'm not quite sure what your
talking about.

Although I have managed to change the <cfif> tags in the results page like you
mentioned, so thanks for that.

If it helps I've genereated the SQL script for the tables in SQL Server, and
placed them on my web space [url]http://kevincb.nadsl.net/[/url].

Thanks

[CF_Oracle]

Unless duplicate records are intentionally allowed, before invoke addVacancy
mathod do something like:
<cfquery name="qCheckDups" datasource="#REQUEST.DSN#">
SELECT Band_Translation
FROM Band
WHERE Trim(VacancyRef) = '#Trim(ARGUMENTS.VacancyRef)#'
AND Trim(VacancyTitle) = '#Trim(ARGUMENTS.VacancyTitle)#'
AND Trim(VacancyDept) = '#Trim(ARGUMENTS.VacancyDept)#'
AND Trim(VacancyDetails) = '#Trim(ARGUMENTS.VacancyDetails)#')
</cfquery>
<cfif qcheckDups.recordcount EQ 0>

<cfelse>

</cfif>

Also (not related to duplicates but helps to pinpoint a problem), in the
component within cftransaction use error handling like this:
<cftry>


<cfcatch type="database">
<cftransaction action = "rollback"/>
<cfinclude template="../ErrorHandlingTemplate.cfm">
</cfcatch>
</cftry>
<cftransaction action="commit"/>
</cftransaction>

[KevCB]

OK, I've added the following to my process.cfm page above the invoke.

<cfquery name="qCheckDups" datasource="HR">
SELECT *
FROM dbo.Vacancy
WHERE Trim(VacancyRef) = '#Trim(ARGUMENTS.VacancyRef)#'
AND Trim(VacancyTitle) = '#Trim(ARGUMENTS.VacancyTitle)#'
AND Trim(VacancyDept) = '#Trim(ARGUMENTS.VacancyDept)#'
AND Trim(VacancyDetails) = '#Trim(ARGUMENTS.VacancyDetails)#')
</cfquery>
<cfif qcheckDups.recordcount EQ 0>

<!--- Do it --->
<cfinvoke component="HR.components.vacancy"
method="#method#">
<!--- VacancyRef only if update method --->
<cfif IsDefined("FORM.VacancyRef")>
<cfinvokeargument name="VacancyRef"
value="#Trim(FORM.VacancyRef)#">
</cfif>
<cfinvokeargument name="VacancyTitle"
value="#Trim(FORM.VacancyTitle)#">
<cfinvokeargument name="VacancyDept"
value="#Trim(FORM.VacancyDept)#">
<cfinvokeargument name="VacancyDetails"
value="#Trim(FORM.VacancyDetails)#">
<cfinvokeargument name="Gender"
value="#Trim(FORM.Gender)#">
<cfinvokeargument name="MaritalStatus"
value="#Trim(FORM.MaritalStatus)#">
<cfinvokeargument name="AgeBand"
value="#Trim(FORM.AgeBand)#">
<cfinvokeargument name="EthnicGroup"
value="#Trim(FORM.EthnicGroup)#">
<cfinvokeargument name="EthnicOrigin"
value="#Trim(FORM.EthnicOrigin)#">
<cfinvokeargument name="OtherEthnicDesc"
value="#Trim(FORM.OtherEthnicDesc)#">
<cfinvokeargument name="Disability"
value="#Trim(FORM.Disability)#">
<cfinvokeargument name="DisabilityDetails"
value="#Trim(FORM.DisabilityDetails)#">
<cfinvokeargument name="AdvertSource"
value="#Trim(FORM.AdvertSource)#">
</cfinvoke>
</cfif>

But when running this I get an error:

Element VACANCYREF is undefined in ARGUMENTS.

[KevCB]

I replaced the ARGUMENTS with FORM and it came out with the following error:-

'Trim' is not a recognized built-in function name.

The error occurred in C:\Inetpub\wwwroot\HR\process.cfm: line 14

12 : AND Trim(VacancyTitle) = '#Trim(FORM.VacancyTitle)#'
13 : AND Trim(VacancyDept) = '#Trim(FORM.VacancyDept)#'
14 : AND Trim(VacancyDetails) = '#Trim(FORM.VacancyDetails)#')
15 : </cfquery>
16 : <cfif qcheckDups.recordcount EQ 0>

I also tried the code within the transaction and removed the above from
process.cfm, but it didn't show any error, and didn't insert the record.

[CF_Oracle]

TRIM is Oracle function . Use analog in SQL server or nothing if you have no one.

[KevCB]

OK, taken out the trims. But now it is giving me an error about my data types.

The data types text and varchar are incompatible in the equal to operator.

The error occurred in C:\Inetpub\wwwroot\HR\process.cfm: line 14

12 : AND VacancyTitle = '#Trim(FORM.VacancyTitle)#'
13 : AND VacancyDept = '#Trim(FORM.VacancyDept)#'
14 : AND VacancyDetails = '#Trim(FORM.VacancyDetails)#'
15 : </cfquery>
16 : <cfif qcheckDups.recordcount EQ 0>

[KevCB]

I've tried changing my data types, but now I keep getting the following error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[philh]

At least one of the fields you're evaluating is text, ntext, or image. The
error message is by design. You have to use the LIKE operator and wildcards to
find strings in text, ntext, or image data.

You should consider changing the data type to varchar, if possible, and if the
contents never exceed 8000 characters.

[KevCB]

Thanks for clearing the problem up philh, I've managed to stop the error, but
unfortunatley it is still not inserting records into SQL Server.

I've tried both bits of code that CF_Oracle has, but it didn't produce an
ErrorHandlingTemplate.cfm file, and brought back no errors.