• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

INsert Into not working in query

Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

I wrote this part of my web site that allows you to insert records into the DB. Right now it is not working properly. I don't get any error, but I also don't get a new record. Can anyone see why this is happening? I am posting both pages of code, the edit page and action page. I can't figure out what I am missing.

Edit page:

<cfparam name="url.id" type="integer" default="0">
<cfparam name="variables.ProjectID" type="integer" default="#url.id#">
<cfparam name="variables.proj_name" default="">
<cfparam name="variables.Description" default="">
<cfparam name="variables.CategoryID" default="">
<cfparam name="variables.cat_name" default="">
<cfif url.id GT 0>
<cfquery name="projMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT c.CategoryID,
c.Name AS cat_name,
p.ProjectID,
p.Name AS proj_name,
p.Description
FROM Categories AS c
INNER JOIN Projects AS p ON c.CategoryID = p.CategoryID
WHERE p.ProjectID = <cfqueryparam value="#URL.ID#" cfsqltype="cf_sql_integer">
AND c.CategoryID = p.CategoryID
</cfquery>
<cfif projMan.RecordCount EQ 1>
<cfset variables.ProjectID = projMan.ProjectID>
<cfset variables.proj_name = projMan.proj_name>
<cfset variables.Description = projMan.Description>
<cfset variables.cat_name = projMan.cat_name>
<cfset variables.CategoryID = projMan.CategoryID>
</cfif>
</cfif>
<cfquery name="catList" datasource="#sitedatasource#"
username="#siteUserID#" password="#sitePassword#">
SELECT DISTINCT Categories.CategoryID, Categories.Name AS cat_name
FROM Categories
ORDER BY Name
</cfquery>
<head>
</head>
<body>
<cfoutput>
<form action="Project-Action.cfm" method="post" name="projects" id="projects"
enctype="multipart/form-data" onsubmit="saveIt()">
<input type="hidden" name="ID" value="#variables.ProjectID#">

<input type="text" name="proj_name" class="textInputs" value="#variables.proj_name#" maxLength="510">

<textarea cols="" rows="" name="PDSeditor" style="display: none">#variables.Description# </textarea>

<select name="CategoryID" size="1" class="smallText">
<cfif IsDefined("projMan.CategoryID")>
<option selected value="#projMan.CategoryID#">#projMan.cat_name#</option>
<cfelse>
<option value="">--Select a Category--</option>
</cfif>
<cfloop query="catList">
<option value="#CategoryID#">#cat_name#</option>
</cfloop>
</select></cfoutput>
</body>


Action Page:

<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
UPDATE Projects
SET
Projects.Name=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
Projects.CategoryID=<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
Projects.Description=<cfqueryparam cfsqltype="cf_sql_longvarchar"
value="#form.PDSeditor#">
WHERE ProjectID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="project-manager.cfm">
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
INSERT INTO Projects
(Name, Description, CategoryID)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">,
<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">)
</cfquery>
<cflocation url="project-manager.cfm">


I just don't see why there is no record being posted to the DB. Can anyone help me?

Thank you.

Phoenix
TOPICS
Advanced techniques

Views

2.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jan 24, 2008 Jan 24, 2008
the order of values you insert is different from the order of table
fields they are inserted into in your INSERT query:

INSERT INTO Projects
(Name, Body, MYFile, CategoryID)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.Body#">,
<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
null="#NOT fileuploaded#">)

in the INSER...

Votes

Translate

Translate
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

You didn't show your cfif tag, just the cfelse.

For troubleshooting, keep things as simple as possible and don't cflocate yourself away. Try something like this.

<cfif something>
true
<cfelse>
false
cfdump something.
</cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Can you be a little more spesific on the if / dump statement. I'm not good with cfdump yet. Still learning. I understand your looking to see what variables come back and should be displayted on the action page instead of sending it back to the manager page, but how would I write it so it works and gives us the information we need?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

My cfif tag is the button being used, here is the full code:

<cfif isdefined("form.proj_OK")>
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
UPDATE Projects
SET
Projects.Name=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
Projects.CategoryID=<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
Projects.Description=<cfqueryparam cfsqltype="cf_sql_longvarchar"
value="#form.PDSeditor#">
WHERE ProjectID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="project-manager.cfm">
<cfelse>
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
INSERT INTO Projects
(Name, Description, CategoryID)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">,
<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">)
</cfquery>
<cflocation url="project-manager.cfm">
</cfif>

How would I put a cfdump into this to find out what is being passed?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

<cfdump var="#form#">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

This is what I get.

CATEGORYID 1
FIELDNAMES ID,PROJ_NAME,PDSEDITOR,CATEGORYID,PROJ_OK
ID 0
PDSEDITOR
PROJ_NAME Polar Bears
PROJ_OK OK


It seems it is setting the ID to 0, meaning it isn't making a record. Why is it doing this? I have the DB cell in the table for ID set to auto number. Is there another reason why?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Go back to your form page and start dumping variables until you determine where it gets set to 0.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

do I use the same dump code, and block out each part of the form, doing each one, one at a time? (Like I said, new to using cfdump)

Thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

let me ask this, Do I really need an innerjoin using 2 queries to fill the form, and adding it to one table in the Action? Could that be the problem?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

<cfparam name="url.id" type="integer" default="0">
<cfparam name="variables.ProjectID" type="integer" default="#url.id#">
...
<input type="hidden" name="ID" value="#variables.ProjectID#">

the above sets your form.ID to 0 if no URL.ID is defined

your INSERT query never fires because the <cfelse> it is in never fires:
apparently, form.proj_OK is always defined, as your cfdump shows. i have
not noticed any form element named proj_OK in the code you posted - is
it a submit button or some form var set in your saveIt() js function?


---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

I'm sorry, I forgot to post the button code. here it is:

<input type="submit" name="proj_OK" value=" OK ">
and the cancel button:
<input type="submit" name="proj_Cancel" value="Cancel">
then the rest of the code:
</cfoutput>
</form>

I still can't figure it out. I have been going through this code all night. Need to get this last part working and I don't see why it won't "fire". Now the update a record part of the code works fine. (If that is any help) It's just adding a new record where it has to create an ID.

Phoenix

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Two submit buttons are almost always a bad idea. Submit with the cancel button and see what your form dumps out.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Nothing drops, the Cancel button does what it is supposed to. Just the submit new record is messing up with the ID setting to 0.

How about this, in my DB there are 2 tables. the Categories table, and the Projects table.

In the Categories table, CategoryID is set as auto number, and the primary key. In the projects table, projectID is set to auto number and also the primary key. Now there is also a cell in projects to place the CategoryID, but that is only set as number. Will this mess anything up? Lets say the first categoryID in the Categories table is 1, if i am to add a record to Projects, and the projectID is 1 and when I add a new record and use the categoryID that is 1, will that cause a problem?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Irish-Phoenix wrote:
> Nothing drops, the Cancel button does what it is supposed to. Just the submit
> new record is messing up with the ID setting to 0.

if i read your code correctly, pressing your Cancel button should
actually trigger the INSERT query on your action page, since
form.proj_OK will not be defined then...

"submit new record" is not messing up with the ID value in any way: the
ID is set to 0 by your code i posted in previous message: form.ID =
variables.ProjectID = url.ID (default = 0)so if url.ID is not defined,
then it and all other vars are set to 0. and i assume when you are
adding a new record no url.ID will be defined...

> How about this, in my DB there are 2 tables. the Categories table, and the
> Projects table.
>
> In the Categories table, CategoryID is set as auto number, and the primary
> key. In the projects table, projectID is set to auto number and also the
> primary key. Now there is also a cell in projects to place the CategoryID, but
> that is only set as number. Will this mess anything up? Lets say the first
> categoryID in the Categories table is 1, if i am to add a record to Projects,
> and the projectID is 1 and when I add a new record and use the categoryID that
> is 1, will that cause a problem?

no, it won't, unless you make it cause a problem :)

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

No when you press the cancel button it attaches to different code. there is actually cfelseif statements going on this page, a cancel function, delete, the ok button for the update insert and an edit button all work accept the insert statement. I just posted the problem code to make reading it simpler then trying to figure out all the code that is on the page. (I read someplcae when posting to boards like this make your code problems simple.)

You say my code is set wrong, how would I change it. Your right, when adding a new record, there is no url.ID, but when you post to the action page to create the new record, it should be creating one in the DB. So what code do I need to change and to what?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

maybe you should start by posting correct complete code, instead of just
snippets of it... i mean, sure, do take out totally irrelevant bits,
styles, etc, but, please, do leave the important stuff in!!! how is
anyone supposed to know you have that cfelseif block there? c'mon!
please post all relevant code. until then i will stop trying to guess
how you have things working...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

Sorry;
Here is all the code.

Edit page:

<cfparam name="url.id" type="integer" default="0">
<cfparam name="variables.ProjectID" type="integer" default="#url.id#">
<cfparam name="variables.proj_name" default="">
<cfparam name="variables.Description" default="">
<cfparam name="variables.CategoryID" default="">
<cfparam name="variables.cat_name" default="">
<cfif url.id GT 0>
<cfquery name="projMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT c.CategoryID,
c.Name AS cat_name,
p.ProjectID,
p.Name AS proj_name,
p.Description
FROM Categories AS c
INNER JOIN Projects AS p ON c.CategoryID = p.CategoryID
WHERE p.ProjectID = <cfqueryparam value="#URL.ID#" cfsqltype="cf_sql_integer">
AND c.CategoryID = p.CategoryID
</cfquery>
<cfif projMan.RecordCount EQ 1>
<cfset variables.ProjectID = projMan.ProjectID>
<cfset variables.proj_name = projMan.proj_name>
<cfset variables.Description = projMan.Description>
<cfset variables.cat_name = projMan.cat_name>
<cfset variables.CategoryID = projMan.CategoryID>
</cfif>
</cfif>
<cfquery name="catList" datasource="#sitedatasource#"
username="#siteUserID#" password="#sitePassword#">
SELECT DISTINCT Categories.CategoryID, Categories.Name AS cat_name
FROM Categories
ORDER BY Name
</cfquery>
<head>
</head>
<body>
<cfoutput>
<form action="Project-Action.cfm" method="post" name="projects" id="projects"
enctype="multipart/form-data" onsubmit="saveIt()">
<input type="hidden" name="ID" value="#variables.ProjectID#">

<input type="text" name="proj_name" class="textInputs" value="#variables.proj_name#" maxLength="510">

<textarea cols="" rows="" name="PDSeditor" style="display: none">#variables.Description# </textarea>

<select name="CategoryID" size="1" class="smallText">
<cfif IsDefined("projMan.CategoryID")>
<option selected value="#projMan.CategoryID#">#projMan.cat_name#</option>
<cfelse>
<option value="">--Select a Category--</option>
</cfif>
<cfloop query="catList">
<option value="#CategoryID#">#cat_name#</option>
</cfloop>
</select>
<input type="submit" name="proj_OK" value=" OK ">
<input type="submit" name="proj_Cancel" value="Cancel">
</cfoutput>
</body>

Action Page:

<cfif IsDefined("Form.proj_Delete")>
<cfquery name="DeleteRecord" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" maxRows=1>
DELETE
FROM Projects
WHERE Projects.ProjectID = #Form.ID#
</cfquery>
<cflocation url="project-manager.cfm">


<cfelseif IsDefined("Form.proj_Cancel")>
<cflocation url="project-manager.cfm">


<cfelseif IsDefined("Form.proj_Edit")>
<cflocation url="Project-edit.cfm?ID=#Form.ID#">

<cfelseif isdefined("form.proj_OK")>
<!--- <cfdump var="#form#"> --->
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
UPDATE Projects
SET
Projects.Name=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
Projects.CategoryID=<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
Projects.Description=<cfqueryparam cfsqltype="cf_sql_longvarchar"
value="#form.PDSeditor#">
WHERE ProjectID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="project-manager.cfm">
<cfelse>

<!--- this is the code that is not working right at this point --->
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
INSERT INTO Projects
(Name, Description, CategoryID)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">,
<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">)
</cfquery>
<cflocation url="project-manager.cfm">
<!--- end non working code --->
</cfif>

Manager page:

<cfquery name="getProjects" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Projects.Name AS PName, Projects.ProjectID AS ID,
Categories.Name AS CName, Categories.CategoryID
FROM Projects INNER JOIN Categories ON projects.CategoryID = Categories.CategoryID
<cfif StructKeyExists(url, 'ID')>
WHERE Categories.CategoryID = <cfqueryparam CFSQLType="CF_SQL_INTEGER" value="#URL.ID#" />
ORDER BY Projects.Name
<cfelse>
ORDER BY Categories.Name
</cfif>
</cfquery>
<cfset rowsPerPage = 6>
<cfparam name="URL.startRow" default="1" type="numeric">
<cfset totalRows = getProjects.recordCount>
<cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)>
<cfset startRowNext = endRow + 1>
<cfset startRowBack = URL.startRow - rowsPerPage>
<head>
</head>
<body>

To add a new record it is a link:
<a href="Project-edit.cfm" class="nav">Add New Project</a>

<cfloop query="getProjects" startRow="#URL.startRow#" endrow="#endRow#">
<cfoutput>
#PName# | #CName#
To edit an existing record and delete it is:
<form action="Project-Action.cfm" method="post">
<input type="hidden" name="ID" value="#ID#">
<input type="submit" class="formButtons" name="proj_Edit" value=" Edit ">
<input type="submit" name="proj_Delete" value="Delete">
</form>
</cfoutput>
</cfloop>
<cfoutput>
The rest of the Next / Previous buttons goes here.
</cfoutput>
</body>

I blocked off the section that is causing the problem, the rest works.
Sorry about that, I was only trying to simplify matters.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 22, 2008 Jan 22, 2008

Copy link to clipboard

Copied

so, does your action page serve multiple forms (i.e. add new project
form, edit project form)?
your problem is that you do not have any condition set when to run your
INSERT INTO query. i guess you must have tried a couple of different
approaches to setting that condition before...

the condition you need is one that would separate an UPDATE (editing
existing record) from INSERT (creating new record)... one obvious
solution is checking for form.ID eq 0 - when you are inserting new
project, it will not have an ID (url.ID would not be defined and thus
would be 0, and so consequently will be variables.projectID and
form.id); when you are editing an existing project, it will obviously
have an ID which does not eq 0...

something like:

...your prior code here...
<cfelseif isdefined("form.proj_OK")>
<!--- <cfdump var="#form#"> --->

<cfif form.id eq 0>
...your INSERT query here...
<cfelse>
...your UPDATE query here...
</cfif>


</cfif>

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 23, 2008 Jan 23, 2008

Copy link to clipboard

Copied

That did it! One last issue in this part and it will be 100%

It adds the new record now, BUT where the cell named description is, it gets it's info from the form named PDSeditor. This is not posting to the DB right now. How do I make the association between the 2? I can't change the name PDSeditor, it is my text editor.

Thank you for helping me out!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 23, 2008 Jan 23, 2008

Copy link to clipboard

Copied

hmm... my guess would be it has a lot to do with the style you have set
for that textarea. i mean the style="display:none". browsers can treat
it differently, but, iirc, most of them WILL NOT post such a form field
- it basically is taken out of DOM = does not exist. do you really need
to have that style declaration on it?

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 23, 2008 Jan 23, 2008

Copy link to clipboard

Copied

I believe so, here is the java script for the text editor:

<script language="Javascript"> <!--
<!--- Required. Function to save editor content back to hidden text-area fields --->
function saveIt() {
theObject = ObjEditorPDSeditor;
theForm = window.document.content;
html = theObject.DOM.body.innerHTML;
theForm.PDSeditor.value = html;
}
function mOvr(src,clrOver,fntcolor){
src.cursor="hand";
src.bgColor = clrOver;
src.color = fntcolor;
}
function mOut(src,clrIn,fntcolor){
src.cursor="default";
src.bgColor = clrIn;
src.color = fntcolor;
}
function mClk(src){
if(event.srcElement.tagName=="TD")
src.children.tags("A")[0].click();
}
//-->
</script>

It isn't working in the edit part either. (It works in all other parts of the site I put it in, why not now? I did take it out to test it and it makes the textarea box show as well as the text editor, and still won't post.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 23, 2008 Jan 23, 2008

Copy link to clipboard

Copied

sorry, can't help you with that, really, as i have never used or even
heard of the PDSeditor... i understand it is some sort of rich
text/HTML/WYSIWYG editor replacement for textareas, but i have not used
it. i prefer tinyMCE, and a long time ago i used to use SOeditor, and
now will probably switch over to FCKeditor since CF8 has it built-in...

but... if your ADD PROJECT page (or other pages) works fine with this
PDSeditor and saves the text you enter in the textarea into the db, then
it should work on the edit project page, too...

the function you posted, though, seems to just return the text you type
into this PDSeditor to the textarea... this js is called on form
submit... there must be another js function (or maybe even a whole js
library) that loads and initiates the PDSeditor instance(s) on the
page... if you can't see anything like that in your edit page (either
in-line js or included as external file) then maybe that is why it is
not working... look in the source of a page where the PDSeditor does
work to see how it is included/initialised and add same code to the edit
page if it is missing there.

strange, though, that you only have NAME attribute and no ID attribute
for the textarea - most js works with ID attribute to select an element
in the DOM as it is a lot easier than using NAME attrib.... maybe you
are missing a required ID attrib of the textarea and that's why the
PDSeditor does not load as it does not see the element to take over?

as a last resort, i would try just removing that style='display:none'
from your textarea to see if it matters at all or not...

on another issue, looking at the code for your SELECT form element (the
CATEGORY drop-down select), it looks like on the EDIT PROJECT page it
will display ONLY ONE category - the one that has been previously
selected for the project. what if i want to change the project category?
maybe you should populate the SELECT with all categories, but pre-select
the one that is currently associated with the project.

also, in your projMan query you do not need the AND c.CategoryID =
p.CategoryID in the WHERE clause - you already have that constraint in
the INNER JOIN...

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 23, 2008 Jan 23, 2008

Copy link to clipboard

Copied

I changed my code and took out the text editor. Now I have an error, not sure why, I use this code on other uploads.

Here is my new edit page: (All the code, I will block out what is going wrong.)
<cfparam name="url.id" type="integer" default="0">
<cfparam name="variables.ProjectID" type="integer" default="#url.id#">
<cfparam name="variables.proj_name" default="">
<cfparam name="variables.Body" default="">
<cfparam name="variables.MYFile" default="">
<cfparam name="variables.CategoryID" default="">
<cfparam name="variables.cat_name" default="">
<cfif url.id GT 0>
<cfquery name="projMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT c.CategoryID,
c.Name AS cat_name,
p.ProjectID,
p.Name AS proj_name,
p.MYFile,
p.Body
FROM Categories AS c
INNER JOIN Projects AS p ON c.CategoryID = p.CategoryID
WHERE p.ProjectID = <cfqueryparam value="#URL.ID#" cfsqltype="cf_sql_integer">
AND c.CategoryID = p.CategoryID
</cfquery>
<cfif projMan.RecordCount EQ 1>
<cfset variables.ProjectID = projMan.ProjectID>
<cfset variables.proj_name = projMan.proj_name>
<cfset variables.Body = projMan.Body>
<cfset variables.MYFile = projMan.MYFile>
<cfset variables.cat_name = projMan.cat_name>
<cfset variables.CategoryID = projMan.CategoryID>
</cfif>
</cfif>
<cfquery name="catList" datasource="#sitedatasource#"
username="#siteUserID#" password="#sitePassword#">
SELECT DISTINCT Categories.CategoryID, Categories.Name AS cat_name
FROM Categories
ORDER BY Name
</cfquery>
<head>
</head>
<body>
<cfoutput>
<form action="Project-Action.cfm" method="post" name="content" id="content"
enctype="multipart/form-data">
<input type="hidden" name="ID" value="#variables.ProjectID#">
<input type="hidden" name="oldimage" value="#variables.MYFile#">

<input type="text" name="proj_name" class="textInputs"
value="#variables.proj_name#" maxLength="510">

<input name="MYFile" type="file" id="MYFile">

<textarea name="Body" cols="50" class="lgtexinput" value="#variables.Body#"></textarea>

<cfoutput>
<select name="CategoryID" size="1" class="smallText">
<cfif IsDefined("projMan.CategoryID")>
<option selected value="#projMan.CategoryID#">#projMan.cat_name#</option>
<cfelse>
<option value="">--Select a Category--</option>
</cfif>
<cfloop query="catList">
<option value="#CategoryID#">#cat_name#</option>
</cfloop>
</select>
</cfoutput>
</cfoutput>

<input type="submit" name="proj_OK" value=" OK ">
<input type="submit" name="proj_Cancel" value="Cancel">
</form>

Here is all the code for the action page:
<cfif IsDefined("Form.proj_Delete")>
<cfquery name="DeleteRecord" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#" maxRows=1>
DELETE
FROM Projects
WHERE Projects.ProjectID = #Form.ID#
</cfquery>
<cflocation url="project-manager.cfm">


<cfelseif IsDefined("Form.proj_Cancel")>
<cflocation url="project-manager.cfm">


<cfelseif IsDefined("Form.proj_Edit")>
<cflocation url="Project-edit.cfm?ID=#Form.ID#">

<cfelseif isdefined("form.proj_OK")>
<cfparam name="fileuploaded" type="boolean" default="false">
<cfparam name="uploadedfile" default="">
<cfset pathToFile = "c:\websites\x9vdzd\img\projects\">
<!--- --->
<cfif len(trim(form.MYFile))><!--- if a file has been selected --->
<!--- try uploading new file --->
<cftry>
<cffile Action="upload" filefield="MYFile" accept="image/gif,
image/jpg, image/jpeg, image/pjpeg"
destination="#pathToFile#" nameconflict="MAKEUNIQUE">
<cfset fileuploaded = true>
<cfset uploadedfile = cffile.serverfile>
<cfcatch type="any">
<!--- if upload did not suceed, reset file uploading vars --->
<cfset fileuploaded = false>
<cfset uploadedfile = "">
<!--- this can be further enhanced by setting some var to hold error
message and return it to user --->
</cfcatch>
</cftry>
</cfif>
<!--- we are updating an existing record --->
<!--- if new file upload was successful and the feature has an image
associated with it - delete old image --->
<cfif form.id gt 0>
<cfif fileuploaded is true AND len(trim(form.oldimage))>
<cfif FileExists(pathToFile & form.oldimage)>
<cffile action="delete" file="#pathToFile & form.oldimage#">
</cfif>
</cfif>
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
UPDATE Projects
SET
<cfif fileuploaded is true>
Projects.MYFile=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#uploadedfile#">,
</cfif>
Projects.Name=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.proj_name#">,
Projects.CategoryID=<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
Projects.Body=<cfqueryparam cfsqltype="cf_sql_longvarchar"
value="#form.Body#">
WHERE ProjectID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cfelse>
<!--- inserting a new record (This is throwing the error)--->
<cfquery datasource="#sitedatasource#" username="#siteUserID#"
password="#sitePassword#">
INSERT INTO Projects
(Name, Body, MYFile, CategoryID)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.Body#">,
<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
null="#NOT fileuploaded#">)
</cfquery>
</cfif><cflocation url="project-manager.cfm"></cfif>

Here is my error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The error occurred in C:\Websites\x9vdzd\admin\Project-Action.cfm: line 74

72 : <cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">,
73 : <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
74 : null="#NOT fileuploaded#">)
75 : </cfquery>
76 : </cfif>


I took out the text editor and went this way. For some reason I can't get the editor to work with this code.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jan 24, 2008 Jan 24, 2008

Copy link to clipboard

Copied

Shoudn't this

<cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
null="#NOT fileuploaded#">

actually be this?

<cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
null=# NOT LEN(uploadedfile)#>

...since if uploadedfile is blank, then NULL is set to YES. Also, check the actual datatype of CategoryID.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 24, 2008 Jan 24, 2008

Copy link to clipboard

Copied

paross1 wrote:
> Shoudn't this
>
> <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
> null="#NOT fileuploaded#">
>
> actually be this?
>
> <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedfile#"
> null=# NOT LEN(uploadedfile)#>
>
> Phil
>

not really. the fileuploaded var is set to false by default and changed
to true on successful file upload.


---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation