inserting date into db using hidden field

[zac1234]

i have a hidden field on my contact form and i want to collect the date that the user submits the form.
my hidden field: <input name="date" type="hidden" id="date" value="<cfoutput>>#DateFormat(Now())#</cfoutput>">

then my inserting code:

<cftransaction>
<CFQUERY NAME="insertRecord" DATASOURCE="weblordatabase">
INSERT INTO contactformtable1 (firstname, lastname, company, email,
telephone, fax, service, comments, remoteHost, date, submit)
VALUES
('#firstname#', '#lastname#', '#company#', '#email#',
'#telephone#', '#fax#', '#service#', '#comments#', '#CGI.REMOTE_HOST#', '#date#', '#submit#')
</CFQUERY>

what am i doing wrong? i just keep getting the follwoing eror code:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred in D:\Domains\weblor.com\wwwroot\insert.cfm: line 9

7 : VALUES
8 : ('#firstname#', '#lastname#', '#company#', '#email#',
9 : '#telephone#', '#fax#', '#service#', '#comments#', '#CGI.REMOTE_HOST#', '#date#', '#submit#')
10 : </CFQUERY>
11 : <cfquery name="GetRecord" datasource="weblordatabase">

all help will be much appriciated

[Guest]

I presume remoteHost is a text field that is long enough to hold the full path. Could you <CFOUTPUT> CGI.REMOTE_HOST and post it?

[zac1234]

quote:

---

Originally posted by: jdeline
I presume remoteHost is a text field that is long enough to hold the full path. Could you <CFOUTPUT> CGI.REMOTE_HOST and post it?

---

yes thats correct the remotHost is just a text field and works fine...

[Dan_Bracuk]

quote:

---

Originally posted by: zac1234
i have a hidden field on my contact form and i want to collect the date that the user submits the form.
my hidden field: <input name="date" type="hidden" id="date" value="<cfoutput>>#DateFormat(Now())#</cfoutput>">

then my inserting code:

<cftransaction>
<CFQUERY NAME="insertRecord" DATASOURCE="weblordatabase">
INSERT INTO contactformtable1 (firstname, lastname, company, email,
telephone, fax, service, comments, remoteHost, date, submit)
VALUES
('#firstname#', '#lastname#', '#company#', '#email#',
'#telephone#', '#fax#', '#service#', '#comments#', '#CGI.REMOTE_HOST#', '#date#', '#submit#')
</CFQUERY>

what am i doing wrong? i just keep getting the follwoing eror code:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred in D:\Domains\weblor.com\wwwroot\insert.cfm: line 9

7 : VALUES
8 : ('#firstname#', '#lastname#', '#company#', '#email#',
9 : '#telephone#', '#fax#', '#service#', '#comments#', '#CGI.REMOTE_HOST#', '#date#', '#submit#')
10 : </CFQUERY>
11 : <cfquery name="GetRecord" datasource="weblordatabase">

all help will be much appriciated

---

The first thing you are doing wrong is using a cold fusion function to get the current date and time instead of your database. While using cold fusion's now() function will work, using your db's equivalent is more efficient.

The next thing is using dateformat without a mask.

The next thing is using dateformat instead of one of the two functions that start with createodbc.

[BKBK]

- If the date column has datetime datatype, as one would expect, you will get an error if you attempt to insert '#date#'. Correct is #date#, without the single-quotes;

- For the variable you now call date, I would use the name theDate instead. It saves you from worrying about database-forbidden names;

- Since the date value you wish to store is "now", it makes the date-insert pretty straightforward. You don't need to format the form input. Just insert the value #now()# or #createodbcdatetime(now())#. (This assumes, of course, that the datatype is datetime.)

[zac1234]

quote:

---

Originally posted by: BKBK
- If the date column has datetime datatype, as one would expect, you will get an error if you attempt to insert '#date#'. Correct is #date#, without the single-quotes;

- For the variable you now call date, I would use the name theDate instead. It saves you from worrying about database-forbidden names;

- Since the date value you wish to store is "now", it makes the date-insert pretty straightforward. You don't need to format the form input. Just insert the value #now()# or #createodbcdatetime(now())#. (This assumes, of course, that the datatype is datetime.)

---

a ha.... ok so i will give this a go an let know how i get on - its so good to have a found a forum with so much knowledge in and so many people willing to help, hopefully one day i will be able to help others - may be in a few years at this rate !!!

[zac1234]

quote:

---

The first thing you are doing wrong is using a cold fusion function to get the current date and time instead of your database. While using cold fusion's now() function will work, using your db's equivalent is more efficient.

The next thing is using dateformat without a mask.

The next thing is using dateformat instead of one of the two functions that start with createodbc.

---

ok thanks for the advise but could you give me some more info, please remember that i am totally new to coding and i ned things spelling out for me.

[Dan_Bracuk]

quote:

---

Originally posted by: zac1234

quote:

---

The first thing you are doing wrong is using a cold fusion function to get the current date and time instead of your database. While using cold fusion's now() function will work, using your db's equivalent is more efficient.

The next thing is using dateformat without a mask.

The next thing is using dateformat instead of one of the two functions that start with createodbc.

---

ok thanks for the advise but could you give me some more info, please remember that i am totally new to coding and i ned things spelling out for me.

---

The db function that returns the current date and time depends on the db. What are you using?

You are never too new to use the manual. If you don't have a cfml reference manual, the internet does. Remember, google is your freind.

When talking to dbs, the format of your date never matters. Don't worry about it. Cold Fusion has two functions, createodbcdate and createodbcdatetime that are designed to help you get date fields into your queries. They are much more versatile than using dateformat.

[zac1234]

i'm using ms access db.

yeah i know the web is just fantastic - everything i have done so far has either been from searching google or my o'reilly reference book. but the problem seems to be for knowing where to put the code on the page and in what order.

so just to confirm you say that the value of my hidden field should be #createodbcdatetime(now())# ?

[Chase Woofer]

You can use the CREATEODBCDATETIME() function or you can just take out the whole field from your form and just use NOW() in your insert statement, this will work with Access, I use it quite often.

Also as good practice you should always Scope your variables.

[zac1234]

ohhhh ok, so i dont even need the hidden field, but if i just use NOW() how does it know which colum to put the date into in my db?

[BKBK]

Just insert #now()# where you presently have '#date#' (line 9 of your code)

[zac1234]

sorted thanks guys, all working and doing what i want it to now

just so i know though..... i'm asuming that it knows what colum to put the date/time into becuase of the order in which it is placed. so the date_time is the 10th item on the insert statement

INSERT INTO contactformtable1 (firstname, lastname, company, email,
telephone, fax, service, comments, remoteHost, thedate_time, submit)

and on the values statement the #NOW()# is also 10th:

VALUES
('#firstname#', '#lastname#', '#company#', '#email#',
'#telephone#', '#fax#', '#service#', '#comments#', '#CGI.REMOTE_HOST#', #now()#, '#submit#')

have i got the right idea?

[Dan_Bracuk]

Get rid of the octothorps from around now() in your list of values. It just so happens that the cold fusion function and msaccess functions have the same name. Octothorps will cause cold fusion to use the cold fusion function, which you don't want. Taking them away will tell cold fusion to use the msaccess function, which you do want.

[BKBK]

Get rid of the octothorps from around now() in your list of values. It just so happens that the cold fusion function and msaccess functions have the same name.
Most databases will accept the raw 'Coldfusion' result #now()# or, arguably better, #createodbcdatetime(now())# in that spot, provided of course the column is of type datetime. I think it would also work even if the database does not have the now() function. Only a guess; not tried it yet.