25 Replies Latest reply on Jan 24, 2008 4:38 PM by Newsgroup_User

    INsert Into not working in query

    Irish-Phoenix Level 1
      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
        • 1. Re: INsert Into not working in query
          Dan Bracuk Level 5
          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>
          • 2. Re: INsert Into not working in query
            Irish-Phoenix Level 1
            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?
            • 3. Re: INsert Into not working in query
              Irish-Phoenix Level 1
              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?
              • 4. Re: INsert Into not working in query
                Dan Bracuk Level 5
                <cfdump var="#form#">
                • 5. Re: INsert Into not working in query
                  Irish-Phoenix Level 1
                  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?
                  • 6. Re: INsert Into not working in query
                    Dan Bracuk Level 5
                    Go back to your form page and start dumping variables until you determine where it gets set to 0.
                    • 7. Re: INsert Into not working in query
                      Irish-Phoenix Level 1
                      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
                      • 8. Re: INsert Into not working in query
                        Irish-Phoenix Level 1
                        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?
                        • 9. Re: INsert Into not working in query
                          Level 7
                          <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
                          • 10. Re: INsert Into not working in query
                            Irish-Phoenix Level 1
                            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
                            • 11. Re: INsert Into not working in query
                              Dan Bracuk Level 5
                              Two submit buttons are almost always a bad idea. Submit with the cancel button and see what your form dumps out.
                              • 12. Re: INsert Into not working in query
                                Irish-Phoenix Level 1
                                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?

                                • 13. Re: INsert Into not working in query
                                  Level 7
                                  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
                                  • 14. Re: INsert Into not working in query
                                    Irish-Phoenix Level 1
                                    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?
                                    • 15. Re: INsert Into not working in query
                                      Level 7
                                      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
                                      • 16. INsert Into not working in query
                                        Irish-Phoenix Level 1
                                        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.
                                        • 17. Re: INsert Into not working in query
                                          Level 7
                                          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
                                          • 18. Re: INsert Into not working in query
                                            Irish-Phoenix Level 1
                                            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!
                                            • 19. Re: INsert Into not working in query
                                              Level 7
                                              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
                                              • 20. INsert Into not working in query
                                                Irish-Phoenix Level 1
                                                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.
                                                • 21. Re: INsert Into not working in query
                                                  Level 7
                                                  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
                                                  • 22. INsert Into not working in query
                                                    Irish-Phoenix Level 1
                                                    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.
                                                    • 23. INsert Into not working in query
                                                      paross1 Level 2
                                                      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
                                                      • 24. Re: INsert Into not working in query
                                                        Level 7
                                                        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
                                                        • 25. Re: INsert Into not working in query
                                                          Level 7
                                                          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 INSERT part the fields order is NAME, BODY, MYFILE, CATEGORYID
                                                          in the VALUES part the order of the values is NAME, BODY, CATEGORYID, MYFILE

                                                          hth

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