Selecting variable from form and posting to mysql

[Guy_Dowd]

I have and exisiting form and I want to take the email and name from the form and input it into mysql database of emaillist . I also only want to do this if the i wish to subscribe is selected.

Quotation Request For:

Date: #DateFormat( Now(), "MMMM DD, YYYY")#
Need By: #form.needby#
Company: #form.company#
Name #form.firstname# #form.lastname#
Title: #form.title#
Address:
#form.address#
#form.city#, #form.state#, #form.zip#
phone: #form.phone#
fax: #form.fax#
e-mail: #form.email#
Reference: #form.reference#

---

Description

Truck Mounted Tank Capacity: #form.capacity#
Fill Valve: Size: #form.fill_valve_size# Style: #form.fill_valve_style#
Discharge Valve: Size: #form.discharge_valve_size#
Vacuum Pump: #form.vacuum_pump# : #form.other_vacuum_pump#
Aluminum Toolbox, Size: #form.toolbox_size#
Mounting: #form.mounted#
Misc. Custom Features:
#form.custom_features#

---

Service Units

#form.port_toilet_service#

---

Units

Material: #form.scu_material#
Number of Compartments:#form.compartment#
Vacuum Pump:#form.scu_vacuum#
Size:#form.scu_size#

---

Additional Notes For This Request:

#form.notes#

---

Email Subscirption

#form.subscribe#

Your request has been forwarded to I.. Thank you for your interest, we will be in contact with you shortly.

CLOSE PAGE

[BKBK]

You should count yourself lucky whenever a user is prepared to supply you such elaborate information. Data is money. You should save the data unconditionally. However, you should also record whether or not the user wants to subscribe.

The context underlying all of this is that you can identify each user. That in turn means you should implement login.

The standard way to process the form is by means of code like the following, in the action page of the form:

<cfif isDefined("form.lastname")>

    <cfquery name="saveUserDetails" datasource="myDSN">

        insert into userTable ... etc., etc.

    </cfquery>

    <cfquery name="saveToolDetails" datasource="myDSN">

        insert into componentPartsTable... etc., etc.

    </cfquery>

</cfif>

[Guy_Dowd]

I added below to my action page and receive this error

Element DSN is undefined in REQUEST

<cfset FullName = '#form.firstname# #Form.lastname#'>

<cfquery  name = "AddtoMailingList" Datasource = "#request.dsn#">

INSERT INTO emaillist

(CustomerName, CustomerEmail)

VALUES

('#FullName#', '#form.CustEmailAddress#')

</CFQUERY>          

[Dave Ferguson]

Unless you have defined the var request.dsn it will error.  I would look at other queries you have in your system and see what they have for the datasource attribute and use that.

Now, some pointers with your actuall code...

• I wouldn't combine the names before insert.  It will be very difficult later to only use the first name (as an example) if you want to.
• You should be using a query param for all input vars... see example below.

 <cfquery  name = "AddtoMailingList" Datasource = "#request.dsn#">
INSERT INTO emaillist
(CustomerName, CustomerEmail)
VALUES
(
<cfqueryparam value="#FullName#" cfsqltype="cf_sql_varchar" >, 
<cfqueryparam value="#form.CustEmailAddress#" cfsqltype="cf_sql_varchar"> 
)
</cfquery>

Hope this helps.

--Dave

[Guy_Dowd]

That is what is used in other queries. That is actually an included that is used elsewhere in the site that was added before i took over the website.

[Dave Ferguson]

Then you need to find out where/how request.dsn is being set for each request. I should be in the onRequestStart method of application.cfc however in your site is probably not there.  You need to make sure the var is set prior to the query being ran.

[Guy_Dowd]

under my _vti_cnf_ i have an application.cfm it has some jibberish i know nothing about on it. maybe that worked in the past prior to our host going to cf9 not sure if that has anything to do with that. or am i better off creating a new datasource which i do not know how to do.

[Dave Ferguson]

Guy Dowd wrote:

under my _vti_cnf_ i have an application.cfm it has some jibberish i know nothing about on it. maybe that worked in the past prior to our host going to cf9 not sure if that has anything to do with that. or am i better off creating a new datasource which i do not know how to do.

I would fathom to say that the application.cfm doesn't contain jibberish.  You need to do a search and find out where the request.dsn is being set.  It should be set on every request and is typically done in the application file.

[BKBK]

Guy Dowd wrote:

That is what is used in other queries. That is actually an included that is used elsewhere in the site that was added before i took over the website.

It is possible that the code that sets the variable request.dsn is included in the form page. If so, then, at least for the time being, you could solve the problem as follows.

Insert the line <cfset session.dsn = request.dsn> at the bottom of the form page (or on any other page where you know request.dsn is defined). You can then access the DSN in the action page as session.dsn.

[Dave Ferguson]

This is a terrible idea.  Why tell someone to correct the problem by creating a new var to mask the issue?

[BKBK]

fergusondj wrote:

This is a terrible idea.  Why tell someone to correct the problem by creating a new var to mask the issue?

We first have to establish what the issue is. What I suggest is implicitly a test. If it works, then it implies the request variable is probably not set in the Application file. Another strong possibility is that the request variable is set in one Application file, while the form page falls under the context of a second Application file. My suggestion not only makes sense, it is a well-known strategy: damage limitation.

I used the phrase 'for the time being' intentionally. Guy Dowd tells you that he is new to this application and that his knowledge of ColdFusion is modest. If, as you seem to suggest, he goes mucking about with the Application file or files, then he might cause more damage than he fixes.