| Bug #1680 | Date/Time fields containing NULLs return E_FAIL results | ||
|---|---|---|---|
| Submitted: | 27 Oct 2003 12:09 | Modified: | 30 Oct 2003 13:17 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.06.00 | OS: | Windows (Windows 32-bit) |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2003 13:17]
Dean Ellis
Using MDAC 2.7, MySQL 4.0.16 and MyODBC 3.51.06 I am unable to duplicate this behaviour. ISNULL(rs("nulldate")) also returns the expected value.
[2 Nov 2003 15:16]
[ name withheld ]
Dean, You are correct. I mistakenly thought a date of '0000-00-00 00:00:00' was the same as a NULL. Thanks, Bruce.
[14 Jul 2004 12:19]
Tavanti Pier Luigi
I've reached the same problem only in the case I've got a text or blob field without any char. The problem is that Mysql has not a 'default' value for those kind of fields so you must edit the program and write just a char (not a blank space!) in the field if the user don't fill anything ! I hope is the same problem...
[26 Jul 2004 21:08]
Jerry Killion
I too have had issues with datetime, If I set the cursortype to adOpenStatic (myODBC 3.51) and attempt to retrieve a recordset, I get an EOF condition if any datetime fields for any rows are set to the default ( 0 ). If I ensure there are no 0 datetime fields, the cursor returns the valid EOF condition. This to me is a real problem, since I have a datetime field that should not be filled in until an action is taken. I should not need to create an artificial datetime to handle a recordset issue. I am not using the datetime field to select the rows.
[17 Sep 2004 8:44]
Jochen Ulbricht
Hi, I had the same problem, too. My workaround was to set the default value of all datetime cols to '1970-01-01 00:00:00'. In this project it did't matter, but what if I wanna get a datetime col which is insered maybe from php with not supported format so that I get a '0000-00-00 00:00:00'? Then I'll get a bit in trouble in vb. It would be nice, if I could at least catch this error, but adodb does only amusing things from this time on.
[6 Jun 2007 23:10]
raul anguiano
the easiest way i found to workaround this problem is using the function cast in the query for the fields with null values in this case is a date field so i do something like this. select cast(field as date) from table or select cast(field as char(10)) from table both worked for me with vb6 and ado 2.5
[19 Jul 2007 8:49]
Tonci Grgin
This has been retested in Bug#28016 and I found no problems. There are two things to consider here: - SQL_MODE MySQL server runs in and (if updating is required) - the way ADO is doing UPDATEs. http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 If all conditions are met, everything works.
Description: A table contains date/time fields, and some records have NULL values. When using MyODBC with ADO (and VB6), the following code will return an E_FAIL error when either the .RecordCount or .MoveFirst commands are executed. The only occurs when the CursorLocation is set to adUseClient; adUseServer works fine. If the date/time field (containing the NULL) is not included in the query, the .RecordCount or .MoveFirst commands work fine. How to repeat: Using a table with NULL vaules in date/time fields, run the following VB6 code: Set conn = New ADODB.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim fld As ADODB.Field conn.CursorLocation = adUseClient conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _ & "SERVER=sequel;" _ & "DATABASE=cmsip;" _ & "UID=root;" _ & "PWD=;" _ & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 conn.Open sSQL = "SELECT * FROM users" rs.Open sSQL, conn, adOpenStatic, adLockReadOnly MsgBox "Recs Found: " & rs.RecordCount rs.MoveFirst Do Until rs.EOF rs.MoveNext Loop Suggested fix: Have MyODBC return NULLs in the date/time fields of the cursor.