sql insert of date/time object returns oledb error code

If it works then you can have it this way:

INSERT INTO Ads(adIssue)
VALUES((#DateFormat(adIssue_final(),"mm/dd/yyyy")#)

That if you are only referring for a date.

Best wishes...

Try This

<cfquery datasource="dsn">
   INSERT INTO Ads(adIssue)
   VALUES( '#DateFormat(FORM.adIssue,"mm/dd/yyyy")#' )
</cfquery>

get rid of the single quotes in ur query '{d '2003-04-08'}'

PS : DONT use these statements
<CFSET myadissue = #ParseDateTime(#FORM.adIssue#)#>
<CFSET adissue_final = #CreateODBCDate(myadissue)#>

just call teh query like :
INSERT INTO Ads(adIssue) VALUES(#CreateODBCDateTime(adIssue)#)

let me know

K'Rgds
Anand

use Anands solution..

Regards
Hart

Anand, I am not using single quotes.. the outer quotes are put there by CF when it outputs the error, the inner quotes are put there by CreateODBCDate. I tried the suggestions you made in various formats CreateODBCDate(FORM.adissue), CreateODBCDateTime(FORM.adissue) and they all cause the malformed guid error.

I tried dash420's suggestion #DateFormat(FORM.adissue,"mm/dd/yyyy")# and this caused the date to be inserted into the database, BUT, it inserted it as a time actually and gave me some date in the year 1899.

TallerMike, I know for a fact that CreateODBCDate can be used with SQL as my CF4 book tells me it's meant specifically to create a date object compatible with SQL, as is CreateODBCDateTime as well. Your suggestion of ###CreateODBCDate(myadissue)### causes CF to return a syntax error. Syntax error in date in query expression '#{d '2003-04-12'}#'. Again, as I stated above, the outer single quotes are put there when CF outputs the error, they're not in my SQL code.

On an interesting note, I recalled from scripts written long ago that I've had date/time issues using OLEDB connections before. So I switched my db connection to ODBC and my original code worked flawlessly. This still doesn't satisfy my frustration as to how to insert a date and/or time into an access db connected via OLEDB. I've increased the points to 150 if anyone wants to take a crack at figuring this out for sure as I seem to recall there are several reasons to use OLEDB instead of ODBC.

Try this:

INSERT INTO Ads(adIssue)
VALUES(<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.adIssue#">)

I'm not denying that CreateODBCDate will work with SQL, but Access is a different breed of SQL. At one point in time, Access liked to have dates surrounded by pound signs. Do a search on the web for your problem through Yahoo and you will see that using pound signs to resovle similar problems is not so unusual. Some examples:

http://p2p.wrox.com/archive/pro_vb/2001-05/114.asp
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20155678.html


Please also note this post from the following link:
http://cfmxplus.blogspot.com/2002_11_10_cfmxplus_archive.html

Date processing with Access in CF
Some folks have had problems doing date processing within SQL against an Access database. (Let's not get into whether anyone should be choosing Access as a DB. For some low volume sites, it works adequately.)

Part of the date handling problem stems from the fact that Access may need help determining that what you're passing it is a date (versus a number). There are two ways to do this. You could use the CreateODBCDate() function to convert a date to a format that will be passed from CF to SQL as an ODBC Formatted date.

Another is to take advantage of the fact that Access can use pound signs around a date to indicate that the value is a date. Of course, those are the same pound signs that CF uses for variables, which can complicate things.

First, just know that one way to format a SQL statement WITHIN Access to process, for instance, records between two dates (leave CF out of the picture for a moment) would be to render it as:

SELECT StartDate FROM Employee WHERE StartDate between #01/01/97# and #01/01/98#

If you wanted to do that within a CFQUERY statement, then you'd need to escape those pound signs (double them) so that CF didn't think you were trying to refer to variables, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ##01/01/98##
</CFQUERY>

Of course, you could also convert this to use the ODBCDateFormat function, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat("01/01/98")#
</CFQUERY>

Notice again that the use of this function doesn't require the use of escaped pound signs because the function creates the date in a format Access understands. But you'll usually have the date coming in as a variable. You could then easily change the ODBCDateFormat as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat(date)#
</CFQUERY>

But if you tried to use the Access form of passing pound-delimited dates, such as in the first two examples above, and you wanted to use a variable for one of them, you'd then need 3 pound signs around that (two for the escaped pound for Access and one for the pound needed to refer to the variable/function), as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###date###
</CFQUERY>

Finally, note that if you wanted to use the now() function in that last example for the date (which returns the current date AND time), or if your incoming "date" variable had minutes and seconds in it, you'd need to wrap it in a dateformat function before using the "pound sign" approach to date formatting in Access because that doesn't like anything but a date to be passed in, as in:.

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###dateformat(now())###
</CFQUERY>

Note, too, that I've not bothered with any date formatting "mask". In my testing, Access is happy with the default dateformat form of date layout.

So when should you use the pound sign approach? Well, really, it's more something that's needed within Access itself to be able to detect that a string of numbers (and dashes or slashes) are in fact representing a date. The thing is, with the ODBCDateFormat function (and CF's Now() returning a date in ODBCDateTime Format), it's perhaps not as important to use the pound-surrounded approach to dates in Access.

posted by Charli

More from the following link:

http://www.maxfusion.co.uk/faq/viewfaq.cfm?QID=168

Q. How do I insert a date into my Access database?

The Problem: You want to insert a date into a date/time
field in your Access database. The Solution: You can
approach this either by inserting a properly formatted
string which Access will then convert into a date/time
object, or by creating one yourself. Either way will work.

<cfset myDate = "DateFormat(now(),"m/d/yy")>
<cfset myDate2 = CreateODBCDate(now())>

<cfquery...>
 INSERT INTO myTable(date1, date2)
 VALUES('#myDate#',#myDate2#)
</cfquery>

i always put single quotes around mine, and it worked.

Around your CreateODBCDate functions? Weird... I know in Oracle at least, if you put single quotes around fields that don't require them, that it slows down the server because it trys to convert the value from a string to whatever it needs. This is the case for numeric fields, not sure if it applies to date fields though?

no, sorry, see another post of mine =) i mean around dateFormat...

i don't know that i've ever tried it with createODBCdatetime.. although i have found alot of differences between acceptable and required syntax depending on if you are using oracle, ms sql server, or access.

those are the only ones i've used, but any time i convert and app from one to the other, it's almost assured that i have to change some sort of syntax in some way or another.

Just a fun story about the differences: Before I knew anything about oracle, i knew alot about ms sql server, and I took a DB class in college that was based on sql server. So i figured I didn't need to study or go to class, and answered questions as I would have in ms sql server.  well, when the course was over, I had a C-.  I thought I had an A since I work on this stuff every day.

I never used single quotes for oracle & sql ! & in both ive used CreateODBCDateTime() to insert datetime into the DB [if time is not required - just use CreateODBCDate()]

its really weird y ur query wont work !

make sure - ur checking the right cfm page, in the respective application & connecting to the respective DB

if everything is set up well - the query suggested shld definately work !

K'Rgds
Anand

Anand,
single quotes is not required if it is date data type. as
CreateODBCDate() is a date type. so single quotes is not required. if it is a string datatype then single quote is must. I always used single quotes as never like CreateODBCDate(). That is personal choice.

Well, nothing I've tried has worked with Access when connecting via an OLEDB DSN connection. So, I've given up trying to use OLEDB and have changed the DSN to ODBC and my original code works fine.

Thanks guys. I don't really know who to award the points to so I'm going to close my eyes, scroll the page up & down, and point at one at random.

thats funny.  i never even noticed that this was regarding oledb and not odbc until you just posted that.

thats probably why nothing we said worked, since most of it revolved around "createODBCdatetime"

haha

WooHoo! I won!

:) heh :)