Error Executing Database Query

Hello,

I'm getting an "Executing Database Query" error when I try to submit a small form I created. Any help I can get would be greatly appreciated.


Here is the CFM code on the form page:

<cfquery name="rsGoldStar" datasource="HRTraining">
SELECT *
FROM dbo.tblEmployees
WHERE Active = 1
ORDER BY LastName ASC</cfquery>
.
.
.

Here is the form code on the form page:

<form action="thanksGS.cfm" id="form1" name="form1" method="POST">
<h3>

</h3>
<table width="68%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td width="100%"><h2><strong>Referred by: </strong></h2></td>
</tr>
<tr>
<td><select name="EmployeeID">
<cfoutput query="rsGoldStar">
<option value="#rsGoldStar.EmployeeID#">#rsGoldStar.LastName#, #rsGoldStar.FirstName#</option>
</cfoutput>
</select>
<strong><span class="style3 style2">(Required)</span></strong></td>
</tr>
</table>
<table width="518" border="0">
<tr>
<td width="177"><h2>Date:</h2></td>
<td width="331"><input name="GoldCardDate" type="text" id="GoldCardDate" size="8" maxlength="8" /></td>
</tr>
<tr>
<td><h2>Applicant Name: </h2></td>
<td><input name="ApplicantName" type="text" id="ApplicantName" size="40" maxlength="50" /></td>
</tr>
<tr>
<td><h2>Position Applying For: </h2></td>
<td><input name="PositionApplyingFor" type="text" id="PositionApplyingFor" size="50" maxlength="75" /></td>
</tr>
</table>
<h2>&nbsp;</h2>
<p><input type="submit" name="Submit" value="submit" />
<label>
<input name="Reset" type="reset" id="Reset" value="Reset" />
</label>
<label></label>
</p>
</form>

________________________

Now here is the action page CFM code:

<cftry>
<cfloop list="#form.fieldnames#" index="fieldName">

<cfquery name="insertRow" datasource="HRTraining">
INSERT INTO tblGoldCards (EmployeeID, GoldCardDate, ApplicantName, PositionApplyingFor)
VALUES (#form.EmployeeID#, #form.GoldCardDate#, '#form.ApplicantName#', '#form.PositionApplyingFor#')
</cfquery>

</cfloop>


<!--- If you sign up for a twice ERROR--->
<cfcatch type="database">
<cfif cfcatch.Message CONTAINS " ">
</cfif>
<cfrethrow />
<cfabort>
</cfcatch>
</cftry>


Thank you in advance.

 

I would imagine that it has something to do with characters in your SQL param values. My first guess would be the date value as it appears you are just passing a date string. The low-rent hacky kind of fix would be to wrap it in single quotes. However, you should instead use <cfqueryparam/> for *ALL* SQL parameters 100% of the time. I cannot think of a single exception to that rule. It will help protect you from SQL injection and will help mitigate problems such as that.

Give that a try and see if it takes care of the issue. If not, let us know.

 

Does <cfqueryparam> replace <cfquery>? If so it didn't work it gave me this error "Attribute validation error for tag CFQUERYPARAM
The tag does not allow the attribute(s) NAME,DATASOURCE. The valid attribute(s) are CFSQLTYPE,LIST,MAXLENGTH,NULL,SCALE,SEPARATOR,VALUE."

 

It is amazing the stuff you can find with google

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm

As the name it implies, and as I alluded to previously, it allows you to parameterize the values that you pass to your query. I can't overstate how important this tag is. Please don't write queries without using it.

 

How do you plug it into my code?

 

I have already told you the answer, and pointed you to the official documentation that explains the usage of the tag, its attributes, and gives example code showing exactly how it is used. Are you truly unwilling/unable to figure it out from that? What have you tried? What worked? What failed?

 

This is the mod I made to the code

<cftry>
<cfloop list="#form.fieldnames#" index="fieldName">

<cfquery name="insertRow" datasource="HRTraining">
INSERT INTO tblGoldCards (EmployeeID, GoldCardDate, ApplicantName, PositionApplyingFor)
VALUES (<cfqueryparam value = "#form.EmployeeID#" cfsqltype="cf_sql_integer" maxlength="4">, <cfqueryparam value="#form.GoldCardDate#" cfsqltype="cf_sql_date" maxlength="8">, <cfqueryparam value="#form.ApplicantName#" cfsqltype="cf_sql_varchar" maxlength="50">, <cfqueryparam value="#form.PositionApplyingFor#" cfsqltype="cf_sql_varchar" maxlength="75">)
</cfquery>

</cfloop>


<!--- If you sign up for a twice ERROR--->
<cfcatch type="database">
<cfif cfcatch.Message CONTAINS " ">
</cfif>
<cfrethrow />
<cfabort>
</cfcatch>
</cftry>

And now I'm getting this

Exceptions

15:26:48.048 - Database Exception - in C:\Inetpub\wwwroot\goldStar\thanksGS.cfm : line 21
Error Executing Database Query.

15:26:48.048 - Database Exception - in C:\Inetpub\wwwroot\goldStar\thanksGS.cfm : line 21
Error Executing Database Query.

Line 21 of code is the "VALUES" line

 

Is EmployeeID a self-incrementing column by any chance?

Also, are you also really wanting to do this insert one time for each field in the form? I have a feeling that is not what you are intending to do. For example, I see that you have at least 4 fields. The way it is currently written, you are running that query at least 4 times.

 

EmployeeID is being pulled from a different table. On the form page a query creates a list of current employees. The user select a employee and the ID of the employee passed.

I would like it to run once and submit the data into the correct fields.

Thank you.

 

I removed <cfloop> tag. Didn't see a need for it. Same error though.

 

This question is still unanswered:

Is EmployeeID a self-incrementing column by any chance? If you are trying to insert a value into tblGoldCards.EmployeeID and tblGoldCards.EmployeeID is a self-incrementing column, you will get an exception.

Aside from that there is nothing obviously syntactically invalid about the query itself.

 

No it's not self-incrementing. there is a ID field (not shown) that is self-incrementing.

 

The ID field is in the table (database), not apart of the form or CF part.

 

Figured out the problem

It was the date maxlength increased it, now it works.

<cftry>


<cfquery name="insertRow" datasource="HRTraining">
INSERT INTO tblGoldCards (EmployeeID, GoldCardDate, ApplicantName, PositionApplyingFor)
VALUES (<cfqueryparam value = "#form.EmployeeID#" cfsqltype="cf_sql_integer" maxlength="4">, <cfqueryparam value="#form.GoldCardDate#" cfsqltype="cf_sql_date" maxlength="10">, <cfqueryparam value="#form.ApplicantName#" cfsqltype="cf_sql_varchar" maxlength="50">, <cfqueryparam value="#form.PositionApplyingFor#" cfsqltype="cf_sql_varchar" maxlength="75">)
</cfquery>




<!--- If you sign up for a twice ERROR--->
<cfcatch type="database">
<cfif cfcatch.Message CONTAINS " ">
</cfif>
<cfrethrow />
<cfabort>
</cfcatch>
</cftry>

 

You mean you aren't using a datetime datatype on that column??