0
inserting date into db using hidden field
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/td-p/868233
Apr 14, 2006
Apr 14, 2006
Copy link to clipboard
Copied
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
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

TOPICS
Getting started
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868234#M80108
Apr 14, 2006
Apr 14, 2006
Copy link to clipboard
Copied
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?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868237#M80111
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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...
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868235#M80109
Apr 14, 2006
Apr 14, 2006
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Expert
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868236#M80110
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
- 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.)
- 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.)
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868239#M80113
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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 !!!

Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868238#M80112
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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.

Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868240#M80114
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868241#M80115
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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())# ?
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())# ?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
New Here
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868242#M80116
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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.
Also as good practice you should always Scope your variables.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868243#M80117
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Expert
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868244#M80118
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
Just insert #now()# where you presently have '#date#' (line 9
of your code)
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
zac1234
AUTHOR
Participant
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868245#M80119
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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?

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?
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868246#M80120
Apr 15, 2006
Apr 15, 2006
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Expert
,
LATEST
/t5/coldfusion-discussions/inserting-date-into-db-using-hidden-field/m-p/868247#M80121
Apr 16, 2006
Apr 16, 2006
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

