Inserting and updating DB record issue

[Irish-Phoenix]

I am writing a management tool that allows you to add a record and or update a record in my DB. I bought a text editor or a WYSIWYG editor and am trying to adapt it to my code. The form uses a few form fields with teh editor as teh last spot to enter the content of the page. Right now all my content in the DB appears in the editor, I change the content and submit the form and it looses all the content in the editor but keeps the content in the form fields. This is the code I am using that works for the form fields, but not the editor:

<cfelseif ParameterExists(Form.btnEdit_OK)>

<cfif ParameterExists(Form.RecordID)>
<cfupdate datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" tableName="SEEevents" formFields="#Form.FieldList#">
<cflocation url="events_RecordView.cfm?RecordID=#Form.RecordID#">

<cfelse>
<cfinsert datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" tableName="SEEevents" formFields="#Form.FieldList#">
<cfquery name="GetNewRecord" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" maxRows=1>
SELECT SEEevents.ID AS ID_Field
FROM SEEevents
ORDER BY SEEevents.ID DESC
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#GetNewRecord.ID_Field#">

</cfif>

This is the code to insert a file from the editor that came with the editor:
<cfoutput query="myContent">
<cfquery datasource="db_example" dbtype="ODBC">
update sampleTable set sampleContent = '#evaluate("form.MyEditorName#myContent.contentID#")#'
where contentID = #myContent.contentID#
</cfquery>
</cfoutput>

Can someone help me to get this to work? I am stumped and in a spot that I need this working right away.
Please help

Thank you

Phoenix

[Newsgroup_User]

I believe you will not be able to use cfupdate and cfinsert with your
WYSIWYG editor since it apparently changes the names of your textarea
fields, so in the form.FieldList (sic; shouldn't that be
Form.FieldNames?) you no longer have a filed named as a field in your db...

you will have to use proper SQL INSERT and UPDATE statements inside
cfquery tags to make sure the info in the editor-changed fields is
entered into your db.

[Irish-Phoenix]

Would this be the code?

<cfelseif ParameterExists(Form.btnEdit_OK)>

<cfif ParameterExists(Form.RecordID)>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
UPDATE SEEevents set SEEevents.display, SEEevents.title, SEEevents.eventDate, SEEevents.eventTime, SEEevents.location, SEEevents.contact, SEEevents.phone, SEEevents.fax, SEEevents.email, SEEevents.URL, SEEevents.sponsor, SEEevents.Body = '#form.PDSeditor#'
FROM Form.FieldList
WHERE contentID = #form.RecordID#
</cfquery>
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
INSERT SEEevents set SEEevents.display, SEEevents.title, SEEevents.eventDate, SEEevents.eventTime, SEEevents.location, SEEevents.contact, SEEevents.phone, SEEevents.fax, SEEevents.email, SEEevents.URL, SEEevents.sponsor, SEEevents.Body = '#form.PDSeditor#'
FROM Form.FieldList
WHERE contentID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#GetNewRecord.ID_Field#">
</cfif>

Is this what I need to do? Can you help me tweek my code?
Thank you for your help also!

Phoenix

[azadisaryev]

well, no...
Ususal UPDATE SQL syntax (note: may be database-specific variations):
UPDATE tblname
SET fieldname1=formvalue1, fieldname2=formvalue2, ...
WHERE something=somethingelse

USUAL INSERT SQL syntax (again may be database-specific variations):
INSERT INTO tablename (fieldname1, fieldname2, ...)
VALUES (formvalue1, formvalue2, ...)
WHERE someconditionshere

now, as for the reference to your editor-specific textarea field, i am not quiet sure what you should put as the name of the form field in your update/insert queries... which editor is it? i can check their docs to see what the reference should be...

[Newsgroup_User]

USUAL INSERT SQL syntax (again may be database-specific variations):
INSERT INTO tablename (fieldname1, fieldname2, ...)
VALUES (formvalue1, formvalue2, ...)
WHERE someconditionshere

I'm sure this in an error of speed -- but one does not usually see a
WHERE clause in an INSERT statement.

[Irish-Phoenix]

How's this?

<cfelseif ParameterExists(Form.btnEdit_OK)>

<cfif ParameterExists(Form.RecordID)>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
UPDATE SEEevents
SET SEEevents set SEEevents.display, SEEevents.title, SEEevents.eventDate, SEEevents.eventTime, SEEevents.location, SEEevents.contact, SEEevents.phone, SEEevents.fax, SEEevents.email, SEEevents.URL, SEEevents.sponsor, SEEevents.Body = '#form.PDSeditor#'
WHERE RecordID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#Form.RecordID#">
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
INSERT INTO SEEevents set SEEevents.display, SEEevents.title, SEEevents.eventDate, SEEevents.eventTime, SEEevents.location, SEEevents.contact, SEEevents.phone, SEEevents.fax, SEEevents.email, SEEevents.URL, SEEevents.sponsor, SEEevents.Body = '#form.PDSeditor#'
VALUES form.display, form.title, form.eventDate, form.eventTime, form.location, form.contact, form.phone, form.fax, form.email, form.URL, form.sponsor, form.Body = '#form.PDSeditor#'
WHERE RecordID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#GetNewRecord.ID_Field#">
</cfif>

The editor is an eConstruct editor I can attach the readme file. Or the sample codes. The problem is, they only made this as a stand alone, no extra form fields used just the editor. It is also throwing a java script error, but that isn't effecting the operation of the editor so it seems. The editor is uploading images, saving them to a directory, making folders in the directory and so on, just the save function is messed up.

[Newsgroup_User]

Ian Skinner wrote:
> USUAL INSERT SQL syntax (again may be database-specific variations):
> INSERT INTO tablename (fieldname1, fieldname2, ...)
> VALUES (formvalue1, formvalue2, ...)
> WHERE someconditionshere
>
> I'm sure this in an error of speed -- but one does not usually see a
> WHERE clause in an INSERT statement.

you are absolutely right... error of speed, lack of sleep and late
hour... :)

[Dan_Bracuk]

How's this? Not very good.

Sabaidee gave you the proper syntax, which you ignored.

In addition to what he said,

You need octothorps around variables.
You need a way to have quotes around character values.
You need a way to convert strings to odbc dates

[Newsgroup_User]

wrong again, i am afraid... can you post the complete form code?
i have checked out eConstruct and it appears that the cfupdate and
cfinsert should work fine as long as there is only one editor instance
in the form... so i guess your problem is elsewhere, probably in the
form itself...

[Irish-Phoenix]

ok, I think I understand what you are saying, I need to match the table data to the form data?
Like this:
<cfelseif ParameterExists(Form.btnEdit_OK)>

<cfif ParameterExists(Form.RecordID)>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
UPDATE SEEevents
SET SEEevents.display=form.display, SEEevents.title=form.title, SEEevents.eventDate=form.eventDate, SEEevents.eventTime=form.eventTime, ...
WHERE RecordID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#Form.RecordID#">
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
INSERT INTO SEEevents (display, title, eventDate, eventTime, location, contact, phone, fax, email, URL, sponsor, Body)
VALUES (form.display, form.title, form.eventDate, form.eventTime, form.location, form.contact, form.phone, form.fax, form.email, form.URL, form.sponsor, form.Body = '#form.PDSeditor#')
WHERE RecordID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#GetNewRecord.ID_Field#">
</cfif>

I am sorry, I am sort of new to this and trying to figure it out on my own, that is why I am here. Is this the way it was explained? Also, what do I add to the code to make time and date proper in the DB?
I really appreciate all the help. Thank you.

Phoenix

[paross1]

INSERT doesn't take a WHERE clause. Also, what is this? form.Body = '#form.PDSeditor#') Your insert would look more like this

INSERT INTO SEEevents (display, title, eventDate, eventTime, location, contact, phone, fax, email, URL, sponsor, Body)
VALUES ('#form.display#', '#form.title#','#form.eventDate#', '#form.eventTime#', '#form.location#', '#form.contact, '#form.phone#','#form.fax#', '#form.email#', '#form.URL#', '#form.sponsor#', '#form.PDSeditor#')

Plus, you are also missing the #s for your form variables in your update.....

UPDATE SEEevents
SET SEEevents.display='#form.display#', SEEevents.title='#form.title#', ..... etc.

Phil

[Irish-Phoenix]

I attached the code for the editor page. There is a java script error it is throwing stating that the editor name is undifined. I suspect that is happening in my code in cold fusion since I did not change the name of the editor in my query and cfsets in teh top of the page.

I will tweek out this code in my insert and update query, and test it out. I am also using and access DB ver 2003.

[Irish-Phoenix]

I fixed the Query it is now taking all the form information but is still erroring out. In the error code it has the tablename="what is in the formfield" for each statement including the text editor. It says the line the qury is on in the problem.
This is how I fixed the insert / update code:

<cfelseif ParameterExists(Form.btnEdit_OK)>

<cfif ParameterExists(Form.RecordID)>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
UPDATE SEEevents
SET SEEevents.display='#form.display#', SEEevents.title='#form.title#', SEEevents.eventDate='#form.eventDate#', SEEevents.eventTime='#form.eventTime#', SEEevents.location='#form.location#', SEEevents.contact='#form.contact#', SEEevents.phone='#form.phone#', SEEevents.fax='#form.fax#', SEEevents.email='#form.email#', SEEevents.URL='#form.URL#', SEEevents.sponsor='#form.sponsor#', SEEevents.Body='#form.PDSeditor#'
WHERE RecordID = #form.RecordID#
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#Form.RecordID#">
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
INSERT INTO SEEevents (display, title, eventDate, eventTime, location, contact, phone, fax, email, URL, sponsor, Body)
VALUES ('#form.display#', '#form.title#', '#form.eventDate#', '#form.eventTime#', '#form.location#', '#form.contact#', '#form.phone#', '#form.fax#', '#form.email#', '#form.URL#', '#form.sponsor#', '#form.PDSeditor#')
</cfquery>
<cflocation url="events_RecordView.cfm?RecordID=#GetNewRecord.ID_Field#">
</cfif>

I attached the error I get with this message also.
I am missing something from what it says.

Thanks again

Phoenix

[Newsgroup_User]

ok, let's start with this:

<cfif ParameterExists(URL.RecordID)>
<script>
<!--- Required. Function to save editor content back to hidden
text-area fields --->
function saveIt() {
<cfoutput query="evenMan">
theObject = ObjEditorPDSeditor#URL.RecordID#;
theForm = window.document.events;
html = theObject.DOM.body.innerHTML;
theForm.PDSeditor#URL.RecordID#.value = html;
</cfoutput>
}
</script>
</cfif>

several things here:

1) this is the main function of your editor that saves entered data. you
are surrounding it with <cfif ParameterExists(URL.RecordID)>...</cfif>,
which means if you are EDITING data, it will not be saved (it will only
be saved if your are ADDING new data)

2) theObject = ObjEditorPDSeditor#URL.RecordID# and
theForm.PDSeditor#URL.RecordID# - you are using #URL.RecordID# here in
the editor object instantiation and reference to the form textarea
field, but your actual textarea filed is named just PDSeditor and in
<cf_econstruct_editor editorName="PDSeditor" ...> you are not using
#URL.RecordID# either. you should either remove the #URL.RecordID# part
from everywhere or add the #URL.RecordID# to the name of your textarea
field and in the editorName attribute of cf_econstruct_editor (not sure
which way is correct for the editor to work - remove or add - so try
both; i am inclined towards remove...)

let's see how this goes and then we'll deal with other bugs in your
form, if necessary...

[Irish-Phoenix]

I put in the tag:
<cfif ParameterExists(URL.RecordID)>
because I want to use the same form to either edit an existing record, and add a new record. should I put in an <cfelseif> with different code?

Ok, I changed the code to this:

<cfif ParameterExists(URL.RecordID)>
<script>
<!--- Required. Function to save editor content back to hidden text-area fields --->
function saveIt() {
<cfoutput query="evenMan">
theObject = ObjEditorPDSeditor#URL.RecordID#;
theForm = window.document.events;
html = theObject.DOM.body.innerHTML;
theForm.PDSeditor#URL.RecordID#.value = html;
</cfoutput>
}
</script>
</cfif>
this is the CF tag and form name:

<textarea cols="" rows="" name="PDSeditor#URL.RecordID#" style="display: none">
#Body_Value#
</textarea>
<cf_econstruct_editor
editorName="PDSeditor#URL.RecordID#"
formname="events"
documentBaseURL=" http://www.mywebsite.com"
documentPath="C:\websites\108392Zj7"
imageSource="/img/content"
omitToolbar=""
allowImageFileUpload="yes"
allowImageDelete="yes"
allowMakeFolder="yes"
allowRemoveFolder="yes"
editorWidth="600"
editorHeight="350"
allowEditSource="yes"
baseFont="Verdana"
baseFontSize="2"
baseFontColor="000000"
baseBGColor="FFFFFF">
</cfoutput>

it is still throwing an error from the javascript page for starters: I am attaching that page of code so you can look at it.

ALSO: I want to use this page to be able to make a new record, as well as edit existing records. How do I manage that, it errors out the page when I link to it directly to start with a blank slate and make a new record. (I have also tried getting the company that made this editor to help me out with their code and they do not respond.)

This is the Java script code that is erroring:
var <cfoutput>ObjEditor#editorName#</cfoutput> = null; // Content element.
var tbRaisedElement = null; // Current toolbar button that is being shown raised.
var tbOnClickInProcess; // Executing user's onClick event.
var tbMouseOutWhileInOnClick; // An onmouseout event occurred while executing the user's onClick event.

It is line 68 or 70 on the page I uploaded. It says the editor name is undefined.

Hope this all helps.
Again thank you for the help!

ALSO

[Newsgroup_User]

> Error Executing Database Query.
> [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
> Access Driver] Syntax error (missing operator) in query expression ''{d
> '2007-04-13'}''.

it seems the error is with your eventDate. in MS Access, dates should be
entered as ###date### (where date is an actual date, in m/d/yyyy format,
or d/m/yyyy, depending on locale settings in the database). but there
should be 3 # on both sides of the date and no '.

so try changing your SEEevents.eventDate='#form.eventDate#' to
SEEevents.eventDate=###form.eventDate###.
if that still throws an error, you will have to parse your date form
field and make a date in MS Access format from it first, i.e. before
your <cfquery> add <cfset eDate = DateFormat(form.eventDate,
"mm/dd/yyyy")> and then use that in your query:
SEEevents.eventDate=###eDate###

[Irish-Phoenix]

Ok, it is still erroring, but has the proper dateformat. I used the cfset function and that did a good job. It is still saying I am missing an operator.

Error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'VIP Dinner'.

The error occurred in C:\websites\108392zj7\admin\events_RecordAction.cfm: line 71

69 : <cfif ParameterExists(Form.RecordID)>
70 : <cfset eDate = DateFormat(form.eventDate, "mm/dd/yyyy")>
71 : <cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
72 : UPDATE SEEevents
73 : SET SEEevents.display=#form.display#, SEEevents.title=#form.title#, SEEevents.eventDate=###eDate###, SEEevents.eventTime=#form.eventTime#, SEEevents.location=#form.location#, SEEevents.contact=#form.contact#, SEEevents.phone=#form.phone#, SEEevents.fax=#form.fax#, SEEevents.email=#form.email#, SEEevents.URL=#form.URL#, SEEevents.sponsor=#form.sponsor#, SEEevents.Body=#form.PDSeditor#



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

SQL UPDATE SEEevents SET SEEevents.display=0, SEEevents.title=VIP Dinner, SEEevents.eventDate=#04/13/2007#, SEEevents.eventTime=8:30pm - 11:00pm, SEEevents.location=persona Restaurant, SEEevents.contact=person, SEEevents.phone=(000) 000.0000, SEEevents.fax=, SEEevents.email=mail@persons.com, SEEevents.URL=http://www.person.com, SEEevents.sponsor=, SEEevents.Body= <font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Notes:</strong> Reservations must be confirmed with a major credit card.<br><strong>Price:</strong> Advance: $95 per person (includes gratuity and cocktail hour) At the door: $125 per person <i> Limited seating available</i></font> WHERE RecordID = 13
DATASOURCE csee
VENDORERRORCODE -3100
SQLSTATE 42000

This is the code I am using now:

[Newsgroup_User]

Irish-Phoenix wrote:
> Ok, it is still erroring, but has the proper dateformat. I used the cfset
> function and that did a good job. It is still saying I am missing an operator.
>

what happened to '' around form variables? it should be '#form.title#',
'#form.location#' etc - any form values that are TEXT STRINGS should be
wrapped in ''.

[Dan_Bracuk · Accepted solution]

quote:

---

Originally posted by: Newsgroup User
> Error Executing Database Query.
> [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
> Access Driver] Syntax error (missing operator) in query expression ''{d
> '2007-04-13'}''.
it seems the error is with your eventDate. in MS Access, dates should be
entered as ###date### (where date is an actual date, in m/d/yyyy format,
or d/m/yyyy, depending on locale settings in the database). but there
should be 3 # on both sides of the date and no '.
so try changing your SEEevents.eventDate='#form.eventDate#' to
SEEevents.eventDate=###form.eventDate###.
if that still throws an error, you will have to parse your date form
field and make a date in MS Access format from it first, i.e. before
your <cfquery> add <cfset eDate = DateFormat(form.eventDate,
"mm/dd/yyyy")> and then use that in your query:
SEEevents.eventDate=###eDate###

---

This might work, but there are two better ways to solve this specific problem. One is to use cfqueryparam and the other is to use createodbcdate. These methods will not only work with Access, but with other databases as well. Plus, you don't have to worry about the format of your date.

[Irish-Phoenix]

Ok, the missing operator message is now fixed, now this is the error I am getting about the editor name missing:

Element PDSEDITOR is undefined in FORM.
The error occurred in C:\websites\108392zj7\admin\events_RecordAction.cfm: line 73

71 : <cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
72 : UPDATE SEEevents
73 : SET SEEevents.display='#form.display#', SEEevents.title='#form.title#', SEEevents.eventDate='###eDate###', SEEevents.eventTime='#form.eventTime#', SEEevents.location='#form.location#', SEEevents.contact='#form.contact#', SEEevents.phone='#form.phone#', SEEevents.fax='#form.fax#', SEEevents.email='#form.email#', SEEevents.URL='#form.URL#', SEEevents.sponsor='#form.sponsor#', SEEevents.Body='#form.PDSeditor#'
74 : WHERE RecordID = #form.RecordID#
75 : </cfquery>

Is this the Java Script error I am trying to fix in the other responce I just posted with teh Java code error? Once that is fixed, it will fix this? Or is this it's own problem?

Phoenix

[Irish-Phoenix]

Ok, the missing operator message is now fixed, now this is the error I am getting about the editor name missing:

Element PDSEDITOR is undefined in FORM.
The error occurred in C:\websites\108392zj7\admin\events_RecordAction.cfm: line 73

71 : <cfquery datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" dbtype="ODBC">
72 : UPDATE SEEevents
73 : SET SEEevents.display='#form.display#', SEEevents.title='#form.title#', SEEevents.eventDate='###eDate###', SEEevents.eventTime='#form.eventTime#', SEEevents.location='#form.location#', SEEevents.contact='#form.contact#', SEEevents.phone='#form.phone#', SEEevents.fax='#form.fax#', SEEevents.email='#form.email#', SEEevents.URL='#form.URL#', SEEevents.sponsor='#form.sponsor#', SEEevents.Body='#form.PDSeditor#'
74 : WHERE RecordID = #form.RecordID#
75 : </cfquery>

Is this the Java Script error I am trying to fix in the other responce I just posted with the Java code error? Once that is fixed, it will fix this? Or is this it's own problem?

Phoenix

[paross1]

Geesh... I'd change SEEevents.eventDate='###eDate###' to something like

SEEevents.eventDate=<cfqueryparam type=" CF_SQL_TIMESTAMP" value="#form.eventDate#">
or
SEEevents.eventDate=<cfqueryparam type=" CF_SQL_DATE" value="#form.eventDate#">
or
SEEevents.eventDate=#CREATEODBCDATE(form.eventDate)#

Phil

[Irish-Phoenix]

The only probelm left with this code now is the fact that the editor is undefined in the form. I don't understand how this can be.. I have it written just like they sent it too me. I am attaching the fixes I placed on the form it's self. Can anyone see where I can make changes to get this form defined properly?

This is the error now:
Variable editorname is undefined in the form

I don't see what I am missing. Can anyone help me with this last error?

Phoenix

[Newsgroup_User]

ok, you are moving in the right direction now...
a few more things:
1) all form variables (i.e form.something) must be surrounded with #
(i.e. #form.display#)
2) values that are of type string (i.e. text strings) must also be
surrounded with ' (i.e. '#form.display#')
3) integer values (numbers) should NOT be surrounded with '
4) you should NOT have a WHERE clause in your INSERT query, since you
are inserting a new record and you do not have RecordID for it
5) dates/times are database-specific in that the format of them depends
on the database. which db are you using and what is the datatype of the
date and time fields? the best way to insert dates/times in the db is to
use cf functions createodbcdate() or createodbcdatetime(). look those up
in the livedocs or the cfml reference.

how about the form code? i suspect you can easily revert to using
cfupdate and cfinsert if you correct a few other errors in your form...