Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

inserting date into db using hidden field

Participant ,
Apr 14, 2006 Apr 14, 2006
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
TOPICS
Getting started
1.5K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 14, 2006 Apr 14, 2006
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?
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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...
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 14, 2006 Apr 14, 2006
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 15, 2006 Apr 15, 2006
- 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.)





Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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 !!!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 15, 2006 Apr 15, 2006
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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())# ?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 15, 2006 Apr 15, 2006
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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?
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 15, 2006 Apr 15, 2006
Just insert #now()# where you presently have '#date#' (line 9 of your code)

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Apr 15, 2006 Apr 15, 2006
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?
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 15, 2006 Apr 15, 2006
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 16, 2006 Apr 16, 2006
LATEST
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.



Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources