INSERT confusion 1

[RikForgo]

I'm trying to run a simple new record insert with the code below, but I keep getting a syntax error. I've looked it over, and I'm sure there's something obvious that I'm missing here, but I can't see it. I have already checked and all the columns exist, and they are all the right data types. Any clues?


-----------------------
news_add_form.cfm
-----------------------

<p class="mediumheading">LSIA News Submission Console</p>
<form action="news_add_action.cfm" method="post">

<table width="574">
<tr>
<td width="119" class="mediumheading_sm">Headline</td>
<td width="443"> <input type="Text" name="headline" size="50" maxlength="50">
</td>
<input type="hidden" name="Headline_Required" value="You must enter a Headline!">
</tr>
<tr>
<td width="119" class="mediumheading_sm">Description</td>
<td width="443"> <textarea name="description" cols="55" rows="3" wrap="VIRTUAL"></textarea>
</td>
</tr>
<tr>
<td width="119" class="mediumheading_sm">Story</td>
<td width="443"> <textarea name="story" rows="6" wrap="VIRTUAL" cols="55"></textarea>
</td>
</tr>
<tr>
<td width="119" class="mediumheading_sm">Entry Date</td>
<td width="443"> <input type="Date" name="entry_date" size="30" maxlength="30">
<span class="main_bodytext">(ex. March 1, 2004)</span> </td>
</tr>
<tr>
<td width="119" class="mediumheading_sm">Date</td>
<td width="443"> <input type="Date" name="date" size="30" maxlength="30">
<span class="main_bodytext">(ex. 03/01/2004)</span></td>
</tr>
<tr>
<td width="119" class="mediumheading_sm">Link</td>
<td width="443"> <input type="Text" name="link" size="50" maxlength="50">
</tr>
<tr>
<td width="119" class="mediumheading_sm">Keywords</td>
<td width="443"> <input type="Text" name="keyword" size="50" maxlength="50">
</tr>
<tr>
<td width="119" class="mediumheading_sm">Byline</td>
<td width="443"> <input type="Text" name="byline" size="50" maxlength="50">
</td>
</tr>
<tr>
<td width="119" class="mediumheading_sm">Photo? (Y/N)</td>
<td width="443"><select name="photo" id="photo">
<option value="Y">Y</option>
<option value="N">N</option>
</select></td>
</tr>
<tr>
<td class="mediumheading_sm">Photo Name (lg)</td>
<td width="443"> <input type="Text" name="photo_lg" size="50" maxlength="50">
</tr>
<tr>
<td class="mediumheading_sm">Photo Name (sm)</td>
<td width="443"> <input type="Text" name="photo_sm" size="50" maxlength="50">
</tr>
<tr>
<td width="119" class="mediumheading_sm">Caption</td>
<td width="443"> <textarea name="caption" cols="55" rows="4" wrap="VIRTUAL"></textarea>
</td>
</tr>
<tr>
<td colspan="2" class="mediumheading_sm" height="57"> <input type="Submit" value=" Submit ">
</td>
</tr>
</table>
</form>

-----------------------
news_add_action.cfm
-----------------------

<!--- Use CFQUERY to insert all html form field values into the Distributor table --->
<CFQUERY DATASOURCE="#REQUEST.DataSource#">
INSERT INTO news
(headline, description, story, entry_date, date, link, keyword, byline, photo, photo_lg, photo_sm, caption)

VALUES
('#Form.headline#', '#Form.description#', '#Form.story#', '#Form.entry_date#', '#Form.date#', '#Form.link#', '#Form.keyword#', '#Form.byline#', '#Form.photo#', '#Form.photo_lg#', '#Form.photo_sm#', '#Form.caption#')
</CFQUERY>

<!--- After data insertion, return to dist_list.cfm --->
<CFLOCATION URL="news_console.cfm">

<!--- Use CFQUERY to insert all html form field values into the Distributor table --->
<CFQUERY DATASOURCE="#REQUEST.DataSource#">
INSERT INTO news
(headline, description, story, entry_date, date, link, keyword, byline, photo, photo_lg, photo_sm, caption)

VALUES
('#Form.headline#', '#Form.description#', '#Form.story#', '#Form.entry_date#', '#Form.date#', '#Form.link#', '#Form.keyword#', '#Form.byline#', '#Form.photo#', '#Form.photo_lg#', '#Form.photo_sm#', '#Form.caption#')
</CFQUERY>

<!--- After data insertion, return to dist_list.cfm --->
<CFLOCATION URL="news_console.cfm">

----------------------------
Error message thrown
----------------------------

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.


SQL = "INSERT INTO news (headline, description, story, entry_date, date, link, keyword, byline, photo, photo_lg, photo_sm, caption) VALUES ('qegqerg', 'qergqerg', 'qergqerg', 'March 8, 2004', '03/08/2004', 'test.cfm', 'efqrf', 'qerwqerg', 'Y', 'qwref', '34ftr', '34fr')"

Data Source = "A3332_LSIA"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (2:43) in the template file O:\Hosted Web Sites\Richard.Forgo\lsia_org\

http://www\news\submit\news_add_action.cfm.

Date/Time: 03/08/04 16:16:23
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705)
Remote Address: 141.157.68.220

 

[ecobb]

Try taking your single quotes away from your date values. I can't remember if Access likes quotes with dates or not.



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[philhege]

Access will accept CreateODBCDAte() formatted dates, or dates formatted as #MM/DD/YYYY# (note that the pound signs are literal, NOT Cold Fusion processing delimiters).

Also, DATE is a reserved word in SQL. You may have to surround it in brackets to eliminate the error.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[RikForgo]

Yep, changing the field name from "date" to "pub_date" fixed the problem. Thanks for the catch.

 

[ecobb]

Good call Phil! Here's a star!



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher