invalid date or time string

[cfsetNewbie]

I am trying to put data into my db that is a date string from a .csv file, this is the format it's in from the file: 3/3/1952 (example)

this is my query:

<cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname, emplocation,empgender,empdob,empdoh,empee)
         VALUES
                  (<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,1))#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,3)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,4))#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
                 <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,
                 <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,10))), 'MM/DD/YYYY')#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">
                  )
   </cfquery>

This is the line giving me the problem:

<CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,

Can anyone help me get thi sworking properly? I've been looking at all kinds of documents and web tricks and it's not working.

Thank you

[Reed_Powell-ttnmOb]

Please show us a sample line of the data file, and also tell us the CF error that you are getting.

-reed

[cfsetNewbie]

Sorry, this is the error:

empdob is an invalid date or time string.

The error occurred in f:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 58

56 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
57 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
58 :                  <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,9))), 'MM/DD/YYYY')#">,
59 :                  <CFQUERYPARAM cfsqltype="cf_sql_date" VALUE="#DateFormat(CreateODBCDate(ParseDateTime(listgetAt(i,10))), 'MM/DD/YYYY')#">,
60 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">

The rest of the code is in my other post, the date appears in the .csv file like this: 00/00/0000 and it won't take it in my query, the table is also set for date and it's an access database.

[-__cfSearching__-]

empdob is an invalid date or time string.

The error seems pretty clear. The value "empDob" is not a date. Perhaps your file has a header row you are inadvertently trying to load?

[ConnecticutRick]

Thanks for this. I forgot I had a header row in the spreadsheet.

[JR__Bob__Dobbs]

If you run into an invalid date value in your CSV do you want to substitute a NULL value?  You could do something like:

<cfqueryparam cfsqltype="cf_sql_date" value="#listgetAt(i,9)#" null="#YesNoFormat( IsDate(listgetAt(i,9)) neq true  )#">

[JR__Bob__Dobbs]

You might also try using cfsqltype="cf_sql_timestamp".  That is the cfsqltype that works for MS SQL DateTime fields, it may also be appropriate to use in MS Access.

[Irish-Phoenix]

I fixed the date time problem, but now this code is giving me a data mismatch..

It's all correct from what I see.. it says the problem is in line 60, that's the last cfqueryparam in the input. It is marked as a text field

with the number 10 in it and the cfqueryparam is set as variablechar.

Here is my error:

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 60

58 :                    '#listgetAt('#index#',9, ',')#',
59 :                    '#listgetAt('#index#',10, ',')#',
60 :                    '#listgetAt('#index#',11, ',')#') 
61 :    </cfquery> 
62 : </cfloop> 

This is my query now:
<cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname, emplocation,empgender,empdob,empdoh,empee)
         VALUES
                  (<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,1))#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,3)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,4))#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
                 <cfqueryparam cfsqltype="cf_sql_date" value="#listgetAt(i,9)#" null="#YesNoFormat( IsDate(listgetAt(i,9)) neq true  )#">,
                <cfqueryparam cfsqltype="cf_sql_date" value="#listgetAt(i,10)#" null="#YesNoFormat( IsDate(listgetAt(i,10)) neq true  )#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,11)#">
                  )
   </cfquery>

and this is the line from the .csv file I'm trying to upload into the database:
12,active,Douglas,L,Berg,none,Marshall,MN,Male,3/3/1952,3/3/1952,none

It should all go in with no problem, what am I missing here?
Thank you for all the help so far. It seems to have satisfied the date problem.

[JR__Bob__Dobbs]

Please post more of your code:

1. More of your code.  It would be useful to see how your loop is executing.

2. The definition of your table structure.

[Dan_Bracuk]

Regarding:  The definition of your table structure.

Especially the siteid field.