Problem with Inserting into DB

Page 1 of 1

9 Replies - 3338 Views - Last Post: 24 February 2010 - 01:47 PM Rate Topic: -----

#1 xTorvos  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 60
  • View blog
  • Posts: 271
  • Joined: 23-October 09

Problem with Inserting into DB

Posted 14 February 2010 - 02:50 AM

I spent all day getting Coldfusion, Coldfusion Builder, Microsoft SQL 2008, and Microsoft SQL 2008 Server Management Studio set up. Now that I've finally gotten everything installed, connected, and working properly, I'm trying to get started in CF with some basic queries.

I'm trying to simply have a user input some information through a form, then use an INSERT statement to write it to my database.

I know that my connections are all working properly, because if I hard-code all the values into the insert statement, then it works. However, when I replace the static text with variables, I get errors.

Any help is appreciated!


Here is my code:
<html>
<head>
    <title>ColdFusion Hello World!</title>
</head>

<body>

<cfform action="First Page.cfm" method="post">
First Name: <cfinput type="text" name="firstname" required="yes"><br />
Last Name: <cfinput type="text" name="lastname" required="yes"><br />
Message: <cfinput type="text" name="message" size="80" required="yes"><br /><br />
<input type="submit" value="Submit" name="Submit">
</cfform>

<br /><br />

<cfif isDefined('form.firstname') AND isDefined('form.lastname')>
    <cfoutput>
        Thank You!
    </cfoutput>
    <cfquery name="selectQuery" datasource="Playground">
        SELECT * FROM Names
    </cfquery>
    <cfset variable.message=form.message>
    <cfset variable.rownum=selectQuery.currentrow+1>
    <cfquery name="insertQuery" datasource="Playground">
    INSERT INTO Names
    VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)
    </cfquery>
</cfif>

</body>
</html>


UPDATE!!!
If I have spaces in my message variable, then the error changes
Here is the error I'm getting with no spaces:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'FirstTest'.

The error occurred in C:\ColdFusion9\wwwroot\CFWork\First Page.cfm: line 28

26 : <cfquery name="insertQuery" datasource="Playground">
27 : INSERT INTO Names
28 : VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)
29 : </cfquery>
30 : </cfif>

VENDORERRORCODE 207
SQLSTATE 42S22
SQL INSERT INTO Names VALUES (2, 02/14/10, FirstTest, LastTest, TestTest)
DATASOURCE Playground

Here is the error with spaces in the message variable:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'blah'.

The error occurred in C:\ColdFusion9\wwwroot\CFWork\First Page.cfm: line 28

26 : <cfquery name="insertQuery" datasource="Playground">
27 : INSERT INTO Names
28 : VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)
29 : </cfquery>
30 : </cfif>

VENDORERRORCODE 102
SQLSTATE HY000
SQL INSERT INTO Names VALUES (2, 02/14/10, first, last, blah blah)
DATASOURCE Playground

This post has been edited by xTorvos: 14 February 2010 - 02:53 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Problem with Inserting into DB

#2 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Problem with Inserting into DB

Posted 14 February 2010 - 06:58 AM

The problem you have is that your query is wrong.
It should like something like this
INSERT INTO Names(rownum,dateentered,firstname,lastname,message)
VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)


You need to specify in the first parenthesis what columns you are entering data into, and then in the second set specify in the same order as the first set the values.
Was This Post Helpful? 0
  • +
  • -

#3 xTorvos  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 60
  • View blog
  • Posts: 271
  • Joined: 23-October 09

Re: Problem with Inserting into DB

Posted 14 February 2010 - 09:09 AM

HeartOnFire,

I read here that you can do it my way or your way. If this is the case, then the statement should be working I think. Let me know if this is not the case.

Thanks,
Torvos
Was This Post Helpful? 0
  • +
  • -

#4 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Problem with Inserting into DB

Posted 14 February 2010 - 07:11 PM

I have never seen it done your way before. Best practice is to do it the way I told you, because that way regardless of whether or not columns get changed the data will still get stored properly. Second best practice tip that will probably fix your problem:
What you want to do with your variables in the query is surround them with <cfqueryparam> tags. Look it up on the livedocs. Basically it will put data into the database as the type of data you specify. So that way you can help to reduce SQL Injection, which as you build more publically available websites you are going to find yourself getting slammed with. I do websites for companies that aren't to big, but still get about 100 entries added to my CF Logs for SQL Injections every night.

PS I just noticed this in your error message. CF does not allow the query to be written the way you are writing it. If you look at your error message it says that FirstTest is an invalid column meaning that it probably looks at your query as:

INSERT INTO name (2,2/14/2010,firsttest,secondtest...)
VALUES ()


So try rewriting it the way I posted and see what you get.

This post has been edited by xheartonfire43x: 14 February 2010 - 07:15 PM

Was This Post Helpful? 1
  • +
  • -

#5 xTorvos  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 60
  • View blog
  • Posts: 271
  • Joined: 23-October 09

Re: Problem with Inserting into DB

Posted 15 February 2010 - 07:34 AM

HeartOnFire,

I will definitely add the cfqueryparam tags, but first I want the query that I'm trying to execute to work. I've changed my query to how you suggested making it, but I'm still getting an error.

Here is the error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'FirstTest'.

The error occurred in C:\ColdFusion9\wwwroot\CFWork\First Page.cfm: line 28

26 : <cfquery name="insertQuery" datasource="Playground">
27 : INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
28 : VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)
29 : </cfquery>
30 : </cfif>



--------------------------------------------------------------------------------

VENDORERRORCODE 207
SQLSTATE 42S22
SQL INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages) VALUES (2, 02/15/10, FirstTest, LastTest, MessageTest)
DATASOURCE Playground


Here is the code that I changed: 
<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, #DateFormat(Now(), "mm/dd/yy")#, #form.firstname#, #form.lastname#, #variable.message#)
</cfquery>


Let me know what you think.
Was This Post Helpful? 0
  • +
  • -

#6 xTorvos  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 60
  • View blog
  • Posts: 271
  • Joined: 23-October 09

Re: Problem with Inserting into DB

Posted 15 February 2010 - 07:59 AM

SOLVED!

The problem was that I needed to use the single quotes in order to have it register. This is why when I hard-coded it in there, it worked just fine. This is what the correct code needs to look like:

<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, '#DateFormat(Now(), "mm/dd/yy")#', '#form.firstname#', '#form.lastname#', '#variable.message#')
</cfquery>


Thanks for the help, HeartOnFire. I'm sure we'll be meeting again soon.
Was This Post Helpful? 0
  • +
  • -

#7 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Problem with Inserting into DB

Posted 15 February 2010 - 02:23 PM

That would make sense. Sorry I didn't read far enough into the error to notice the quotes. and yes anytime you are doing a query without using the <cfqueryparam> tag and the variable is not an integer you need to wrap it in quotes.
Was This Post Helpful? 0
  • +
  • -

#8 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1546
  • View blog
  • Posts: 2,989
  • Joined: 13-January 08

Re: Problem with Inserting into DB

Posted 24 February 2010 - 07:55 AM

Since you said you were a beginning CF'er I'll add this little tip to the end of this thread.

I find that it's easier to supply a CFQUERY with what I'd call "ready to go" variables. What I mean is where your code looks like this:

<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, '#DateFormat(Now(), "mm/dd/yy")#', '#form.firstname#', '#form.lastname#', '#variable.message#')
</cfquery>


I'd suggest trying it like this:

<cfset variables.dateValue = DateFormat(Now(), "mm/dd/yy")>
<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, '#variables.dateValue#', '#form.firstname#', '#form.lastname#', '#variable.message#')
</cfquery>


Why do it that way? In the event that you do encounter an error with the query, Adobe CF's error messages aren't the most helpful. They'll tell you simply that the query failed and might even bring back some details from the database you're using. That said, you'd then proceed to examine your query for spelling errors, incorrect datatyping and finally the content of the variables you're trying to insert. Having the variables completely formed outside the cfquery tags will allow you to a CFDUMP of them or simply output them in your debugging efforts...which will make your life easier.

In addition, xheartonfire43x mentioned CFQUERYPARAM. Short message: USE IT. No matter what. Don't get used to writing cfqueries without it. It WILL save your ass from database hacking misery AND, if that's not enough, it will speed up the execution times of your queries.

Finally, if you form your variables outside of the cfquery tags they are completely ready to be used should you decide to take your query and make a stored procedure out of it. That is, you'd be replacing the CFQUERY block one day with a CFSTOREDPROC call...perhaps even surrounded in a CFTHREAD. What that all means is that you can prep your variables and then hand it all off to a thread containing a call to a stored procedure...and have your code go on it's way. No waiting for the query to execute before you go to your next action...just set it up, send it and forget it.

That last part is more advance coding concepts but laying the foundation early in your education isn't a bad idea.

Good luck!
Was This Post Helpful? 1
  • +
  • -

#9 Guest_gebuh*


Reputation:

Re: Problem with Inserting into DB

Posted 24 February 2010 - 11:35 AM

Not to derail the topic, but where can I get more info on this? I've got some pretty (for me anyway) complex queries that I would like to simplify for later generations. I've got stuff like this:
ISNULL(CAST(CAST(NULLIF((SELECT sum(t.final_hours) FROM some_table t 
	 					WHERE t.task_name IN(SELECT td.title FROM some_other_table td WHERE td.name = 'some_value') 
	 						AND c.comp_id=t.fk_comp_id), 0) AS FLOAT)/ 
	 					NULLIF(a.whatever,0) AS decimal(10,2)),0) as 'Some very Important Data'

that goes on for several lines. This is actually a variable string that gets coupled with the rest of the query. It's huge and ugly and will be difficult to maintain.
I've been googling, but can't find anything to uncomplicate this stuff.

View PostCraig328, on 24 February 2010 - 06:55 AM, said:

Since you said you were a beginning CF'er I'll add this little tip to the end of this thread.

I find that it's easier to supply a CFQUERY with what I'd call "ready to go" variables. What I mean is where your code looks like this:

<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, '#DateFormat(Now(), "mm/dd/yy")#', '#form.firstname#', '#form.lastname#', '#variable.message#')
</cfquery>


I'd suggest trying it like this:

<cfset variables.dateValue = DateFormat(Now(), "mm/dd/yy")>
<cfquery name="insertQuery" datasource="Playground">
INSERT INTO Names (Name_ID, Entered_Date, First_Name, Last_Name, Messages)
VALUES (#variable.rownum#, '#variables.dateValue#', '#form.firstname#', '#form.lastname#', '#variable.message#')
</cfquery>


Why do it that way? In the event that you do encounter an error with the query, Adobe CF's error messages aren't the most helpful. They'll tell you simply that the query failed and might even bring back some details from the database you're using. That said, you'd then proceed to examine your query for spelling errors, incorrect datatyping and finally the content of the variables you're trying to insert. Having the variables completely formed outside the cfquery tags will allow you to a CFDUMP of them or simply output them in your debugging efforts...which will make your life easier.

In addition, xheartonfire43x mentioned CFQUERYPARAM. Short message: USE IT. No matter what. Don't get used to writing cfqueries without it. It WILL save your ass from database hacking misery AND, if that's not enough, it will speed up the execution times of your queries.

Finally, if you form your variables outside of the cfquery tags they are completely ready to be used should you decide to take your query and make a stored procedure out of it. That is, you'd be replacing the CFQUERY block one day with a CFSTOREDPROC call...perhaps even surrounded in a CFTHREAD. What that all means is that you can prep your variables and then hand it all off to a thread containing a call to a stored procedure...and have your code go on it's way. No waiting for the query to execute before you go to your next action...just set it up, send it and forget it.

That last part is more advance coding concepts but laying the foundation early in your education isn't a bad idea.

Good luck!

Was This Post Helpful? 0

#10 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1546
  • View blog
  • Posts: 2,989
  • Joined: 13-January 08

Re: Problem with Inserting into DB

Posted 24 February 2010 - 01:47 PM

Guest_gebuh:

I'm not sure what you could do to simplify that. All the SQL functions in that string would be, I would assume anyway, necessary to what you're pulling out of there. You mentioned that it's "actually a variable string". Do you mean to say that the CF constructs a string to supply the query and that that mess is one of the possible results of that?

In either event, you might gain a little headway by looking into seeing if you can encapsulate some of the data formatting there into a single database function. If your CF is building that code on the fly to feed to the query then you could probably clean it up some by committing it to a component method and leaving all the logic for how the string is built in there.

In either event, you wouldn't really be cutting corners per se on maintenance...just putting it somewhere out of the general eye.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1