DBAdapter.Fill(), datatype matching.

[zecodela]

i stored a Time field in MySQL database and the DBAdapter.Fill() use DateTime to store the return value. and, this leads to 'FormatException'. do u know any method to do some custom datatype matching to solve this problem? or, i just go to change the datatype in database to string or other simply datatype? thanks, jim

[sreejith ss nair]

Try to use ToString() method before passing into the database. Eg: DateTime dt; Assume that, dt is 12-12-2004 12:24:44 Am; dt.ToString("yyyy-MMM-dd") will return 2004-Dec-12. dt.ToString("yyyy/MMM/dd") will return 2004/Dec/12. dt.ToString("yyyy-MM-dd") will return 2004-12-12. dt.ToString("yyyy/MM/dd") will return 2004/12/12. dt.ToString("mm-DD-yyyy") will return 12-12-2004. dt.ToString("mm/DD/yyyy") will return 12/12/2004. Hope this will serve your purpose. Sreejith Nair [ My Articles ]

[Heath Stewart]

He's trying to fill a DataSet, not update the database. Besides, if you specify SqlDbType.DateTime for the column type then the value should be an instance of the DateTime struct. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]

[Heath Stewart]

There is no time type in SQL Server. If you're referring to the timestamp type then be sure to read the documentation for the SqlDbType enumeration. A timestamp translates to a byte[] array. In SQL Server a timestamp is an automatically generated array of binary numbers that is guaranteed to be unique with a database. It is not a time portion of a DateTime or anything like it. If DataAdapter.Fill is having trouble determining the proper type then you'll need to define the DataColumns in your DataTable within the DataSet manually. The easiest way to do this is to create a typed DataSet using VS.NET. You can drag-n-drop tables, views, or stored procedures from Server Explorer (i.e., from connected databases) into the designer when you create a new DataSet type. Right-click on your project (or a folder within your project) and click Add->Add New Type. Then click "DataSet", give it a name, and click OK. Now you get what looks like (and actually is) the XML Schema designer. Drag any of those objects from the Server Explorer I mentioned above onto this surface, or design the tables and fields (elements and child elements) yourself. You can even add DataRelations and keys in this view as well. When you compile your project a typed DataSet is created. Instantiate a new instance of this DataSet instead of the base DataSet class. For performance improvements, use the typed table and column properties which decrease the number of required look-ups to find the correct table or column, respectively. On the other hand, you chould change the type in SQL Server. If you intended to have a field that stores just the time, then you need to declare it as a DateTime field in SQL Server. It's your views and what you do with the data that determine if the time, date, or both matter. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]