Form to SQL database

What am I doing wrong?

Page 1 of 1

3 Replies - 1252 Views - Last Post: 04 November 2008 - 02:28 PM Rate Topic: -----

#1 AFProgMan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 21-October 08

Form to SQL database

Post icon  Posted 04 November 2008 - 08:07 AM

I am more of a beginner in coldfusion than .Net, java, HTML, and PHP. I cannot understand what I am doing wrong and why the this query is not working. Am I assigning the variables incorrectly or what? My code is here and the object is obvious.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Test</title>
</head>
<cfset question1=SubmittedQuestion>
<cfset email2=SubmittedEmail>

<cfquery name="SubmitData" datasource="#request.site.mainDSN#">
INSERT INTO tbl_sharons_nook_QA (questionQ, questionStatus, questionEmail)
VALUES (#question1#, 'Pending', #email2#)
</cfquery>

<cfquery name="GetData" datasource="#request.site.mainDSN#">
Select questionID, questionQ, questionA
From tbl_sharons_nook_QA
</cfquery>


<body>
<form action="" method="post">
Email Address: 
  <input type="text" name="SubmittedEmail" size="30" maxlength="100" value="Email"><br>
Question to Submit:<input type="text" name="SubmittedQuestion" size="30" maxlength="100" value="question"><br>
<input type="submit" value="Submit Question" onclick="SubmitData">
</form>
<p>
 


<!--output of query-->
<table>
<tr>
<td>Question ID</td><td>Question</td><td>Answer</td></tr>
<cfoutput query="GetData">
<tr>
<td>#questionID#</td><td>#questionQ#</td><td>#questionA#</td>
</tr>
</cfoutput>
</table>
</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: Form to SQL database

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Form to SQL database

Posted 04 November 2008 - 10:18 AM

View PostAFProgMan, on 4 Nov, 2008 - 07:07 AM, said:

<cfset question1=SubmittedQuestion>
<cfset email2=SubmittedEmail>

<cfquery name="SubmitData" datasource="#request.site.mainDSN#">
INSERT INTO tbl_sharons_nook_QA (questionQ, questionStatus, questionEmail)
VALUES (#question1#, 'Pending', #email2#)
</cfquery>

<cfquery name="GetData" datasource="#request.site.mainDSN#">
Select questionID, questionQ, questionA
From tbl_sharons_nook_QA
</cfquery>


What is the exact error message?

Since you are using a self-posting form, my guess would be the placement of the insert query. The form variables SubmittedQuestion and SubmittedEmail do not exist the first time the page loads. Causing an error. You can test for a submit using the FORM structure. Then conditionally run the INSERT if a POST occurred.

<cfif structKeyExists(FORM, "SubmittedQuestion")>

<!--- the form was submitted, run the insert ....--->

</cfif>

... do rest of code ...

Though you will probably get a syntax error in the query since questionQ and questionEmail appear to be varchar fields, so the values must be enclosed in single quotes. Though it is really recommended to use cfqueryparam instead.

This post has been edited by sansclue: 04 November 2008 - 10:19 AM

Was This Post Helpful? 0
  • +
  • -

#3 AFProgMan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 21-October 08

Re: Form to SQL database

Posted 04 November 2008 - 02:10 PM

But where would I putt hat if statement? Directly inside of my form containing the submit button?
Was This Post Helpful? 0
  • +
  • -

#4 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Form to SQL database

Posted 04 November 2008 - 02:28 PM

View PostAFProgMan, on 4 Nov, 2008 - 01:10 PM, said:

But where would I putt hat if statement? Directly inside of my form containing the submit button?


No, at the top of your page. Not tested, but here is how you might arrange the code.

Update I removed the two variables question1 and email2 because you do not need them. (Also, I scoped the variables. The code should work either way, but it is a good practice)

<!---
	run the insert only if the form was submitted
--->
<cfif structKeyExists(FORM, "SubmittedQuestion")>
	<cfquery name="SubmitData" datasource="#request.site.mainDSN#">
	INSERT INTO tbl_sharons_nook_QA (questionQ, questionStatus, questionEmail)
	VALUES ( '#FORM.SubmittedQuestion#', 'Pending', '#FORM.SubmittedEmail#' )
	</cfquery>

</cfif>

<!---
	I am assuming this query and cfoutput are for debugging?
	By placing them here they will run every time the page loads
--->
<cfquery name="GetData" datasource="#request.site.mainDSN#">
Select questionID, questionQ, questionA
From tbl_sharons_nook_QA
</cfquery>

<!--output of query-->
<table>
<tr>
<td>Question ID</td><td>Question</td><td>Answer</td></tr>
<cfoutput query="GetData">
<tr>
<td>#questionID#</td><td>#questionQ#</td><td>#questionA#</td>
</tr>
</cfoutput>
</table>

<form action="#CGI.SCRIPT_NAME#" method="post">
Email Address:
  <input type="text" name="SubmittedEmail" size="30" maxlength="100" value="Email"><br>
Question to Submit:<input type="text" name="SubmittedQuestion" size="30" maxlength="100" value="question"><br>
<input type="submit" value="Submit Question" onclick="SubmitData">
</form>


This post has been edited by sansclue: 04 November 2008 - 02:30 PM

Was This Post Helpful? 1

Page 1 of 1