InfoPath Dev

[0x80040E07][Microsoft OLE DB Provider for SQL Server] Conversion failed when converting datetime from character string.

Hi, Can someone help me please with the above problem. I'm new to InfoPath and i'm trying to make a form that goes into out inventory database (MS-SQL 2005). I have every thing working except this query. The Idea behind this form is that a User pics the customers name and then a query updates the "Transaction Date" DropDown box. This works! However, once i select a date to query for the Assets checked out on that date i receive the above error.

The Drop down shows the datetime in this format "2010-10-20T15:31:31.160000000".... I have a feeling the once selected the dropdown box changes the format.

Any help would be greatly appreciated!

I think SQL server doesn't like the .160000000 part at the end.  Would your process still work if you lopped off the fractional seconds part at the end before doing your query?  It seems like SQL Server will probably accept up to 3 digits of fractional seconds.

The process would still work. Any idea how to lop off the fractional seconds.

Thanks for the reply

substring(date, 1, 19) where date is your date field, should provide you with the value without the fractional seconds.  Just use that in a rule.

Well that seem to clear up the error, but its not grabbing the row of data for any data i select from the drop down. I have a feeling is the way it formats the data to (10/26/2010 etc...) so why the number of characters maybe be ok now, it doesn't seem to be converting it to the same format on the next query. Is there away to access these combo boxes from the code editor maybe... that way i can write some toString functions or something.

You can display the field in a textbox to see what its actual value is.  I'm fairly certain that InfoPath is not reformatting it.  If you do need to reformat the field's content, you can do so with a combination of concat() and substring() functions.