"Bill" <Bill@discussions.microsoft.com> wrote in message
news:ABFD9880-7984-4AA3-B763-4D4327AB3159@microsoft.com...

> That was the problem... my "SqlDbTypeConverter" class was mapping the
> DateTime to a TimeStamp (which is varbinary) so the parameter data type
> was
> wrong. Hence the DateTime to Byte[] conversion failure.

> private static String[,] DBTypeConversionKey = new String[,]
> {
> {"BigInt","System.Int64"},
> {"Binary","System.Byte[]"},
> {"Bit","System.Boolean"},
> {"Char","System.String"},
> ...

> There must be a better way to do this....

> That was the problem... my "SqlDbTypeConverter" class was mapping the
> DateTime to a TimeStamp (which is varbinary) so the parameter data
> type was wrong. Hence the DateTime to Byte[] conversion failure.
>
> The converter class is simply a two dimensional array that maps the
> System data type name as a string to the SQL data type name as a
> string... ie: "System.DateTime" = "DateTime" and the other way... ie:
> "DateTime" = "System.DateTime"... It does this for any data type.
>
> private static String[,] DBTypeConversionKey = new String[,]
> {
> {"BigInt","System.Int64"},
> {"Binary","System.Byte[]"},
> {"Bit","System.Boolean"},
> {"Char","System.String"},
> {"DateTime","System.DateTime"},
> {"Decimal","System.Decimal"},
> {"Float","System.Double"},
> ...
> There must be a better way to do this....
>
> Given the System data type is there an API method that will retun the
> equivalent SQL data type?
>
> "Bill" wrote:
>
>> Yes the data type in the DatSet is DateTime as well.
>>
>> I have included the code for the function that I use to create the
>> update command. I'm thinking now that the problem may be in the
>> "SqlDbTypeConverter" class I have defined... I must not be converting
>> the system type to a SQL type properly.
>>
>> The column "LastChange" is a Timestamp. I ignore this column.
>>
>> Do I need to do this... is there a better way to create the
>> parameter?
>>
>> private SqlCommand GetUpdateCommand(string tablename,
>> DataColumnCollection fields, string prikey)
>> {
>> SqlCommand cmd = new SqlCommand();
>> string sql = "UPDATE [" + tablename + "] SET ";
>> foreach (DataColumn field in fields)
>> {
>> if (field.ColumnName.ToLower() != "lastchange")
>> {
>> if (field.ColumnName.ToLower() != prikey.ToLower())
>> sql += field + "=@" + field + ",";
>> SqlParameter param = new SqlParameter("@" +
>> field.ColumnName.ToString(),
>>
>> SqlDbTypeConverter.SystemTypeToDbType(field.DataType));
>> param.SourceColumn = field.ColumnName.ToString();
>> cmd.Parameters.Add(param);
>> }
>> }
>> sql = sql.TrimEnd(',');
>> sql += " WHERE " + prikey + "=@" + prikey;
>> cmd.CommandText = sql;
>> cmd.Connection = m_conn;
>> cmd.Transaction = m_transaction;
>> return (cmd);
>> }
>> -- Bill
>>
>> "Jesse Houwing" wrote:
>>
>>> Hello Bill,
>>>
>>>> That was my first though as well, but the data type of the column
>>>> in the database is a DateTime not Timestamp.. The next column in
>>>> the table is a timestamp..
>>>>
>>>> If I handle the row updated event and look at the command all
>>>> parameters seem to be mapped correctly.
>>>>
>>>> I can read the values fine...
>>>>
>>>> Could this be a Culture problem some how?
>>>>
>>> It would surprise me if it had anything to do with cultures. Are the
>>> types of the parameters and the field in your dataset also DateTime?
>>> Just checking.
>>>
>>> Maybe you could try to get a small solution together showing the
>>> issue... Then we might be able to look with you.
>>>
>>> Jesse
>>>
>>>> "Jesse Houwing" wrote:
>>>>
>>>>> Hello Bill,
>>>>>
>>>>>> I am having a problem with DateTime columns in SQLexpress and
>>>>>> .NET 2.0 DataAdapter.Update().
>>>>>>
>>>>>> I create MyDataTable containing a copy of the data in a table on
>>>>>> the
>>>>>> server.
>>>>>> The table contains one DateTime column.
>>>>>> DataTable column data type is System.DateTime.
>>>>>> SQLexpress column data type is DateTime.
>>>>>> I then modify the contents of the DateTime column
>>>>>> "MyDataRow["DateTimeColumn"] = DateTime.Now;"
>>>>>> When I want to update the server, I fill MyDataSet using
>>>>>> MyDataAdapter.Fill(MyDataSet,"Same Table Name as in MyDataTable"
>>>>>> )
>>>>>> with the data from the server.
>>>>>> I set MyDataAdapter.UpdateCommand to a SqlCommand using named
>>>>>> parameters matching column names.
>>>>>>
>>>>>> I then call MyDataAdapter.Update(MyDataTable.GetChanges());
>>>>>>
>>>>>> It produces the following error:
>>>>>>
>>>>>> "Failed to convert parameter value from a DateTime to a Byte[]."
>>>>>>
>>>>>> If I don't include the DateTime column in the update command,
>>>>>> everything works. (except I don't get my DateTime update)
>>>>>>
>>>>>> Any ideas would be helpful.
>>>>>>
>>>>> It looks like the columntype in the database is actually TimeStamp
>>>>> instead of Date.
>>>>>
>>>>> Try correcting the type of the column and generate your procedures
>>>>> and tableadapter
>>>>> again.
>>>>> --
>>>>> Jesse Houwing
>>>>> jesse.houwing at sogeti.nl
>>> --
>>> Jesse Houwing
>>> jesse.houwing at sogeti.nl