System.Data.Common.DbDataAdapter Bug.

D

David Coleman

Hello all,

I am running XP Sp2, VS 2005 Sp1, Sql 2005 Sp2.

I am receiving the exception "Invalid character value for cast
specification." from the line

((DbDataAdapter)da).Update( dataSet );

The dataset contains a single table and the table contains a single row.
(see below).

The dataRow.HasErrors is set true, the dataRow.GetColumnsInError() returns a
zero element array.

The exception is overcome by setting the DateTime values from
DateTime.MinValue to DBNull.Value.

This is obviously a bug... Either dataRow.GetColumnsInError() should return
the DateTime columns in error
Or DateTime.MinValue should be an acceptable value for a DateTime column.

David.

DATA ROW.
{
RowState:Added. RowError:Invalid character value for cast specification.
[BranchCode]={H}, [ReferenceNumber]={E00009},
[SupplierLinkKey]={50052}, [PurchaseFinancialYear]={2006},
[PurchaseActiveMonth]={6}, [PurchaseActiveMonthFlag]={L},
[PurchaseActiveMonthDate]={30/06/2006 12:00:00 AM},
[TransactionType]={Purchase}, [TransactionGroup]={PO},
[TransactionStatus]={A}, [SupplierLedgerCode]={N},
[SupplierPaymentTerms]={}, [SupplierTradingName]={Cummins Marine Business
Unit}, [CustomerOrderNumber]={123456}, [SupplierTaxFlag]={False},
[CustomerLinkKey]={0}, [CustomerTradingName]={},
[DeliveryContactName]={David Coleman}, [DeliveryAddressLine1]={16 Uralba
Place}, [DeliveryAddressLine2]={}, [DeliveryAddressLine3]={},
[DeliveryAddressLine4]={}, [DeliverySuburb]={Wahroonga},
[DeliveryState]={NSW}, [DeliveryPostcode]={2076},
[DeliveryCountry]={}, [DeliveryDespatchCode]={N},
[DeliveryLocationCode]={}, [DeliveryFreightCode]={N},
[DeliveryPhoneNumber]={02 9489 2069}, [DeliveryPhoneOther1]={},
[DeliveryPhoneOther2]={}, [DeliveryFaxNumber]={02 9489 1203},
[DeliveryMobileNumber]={0419 611 453},
[DeliveryEmailAddress]={[email protected]},
[DeliveryWebAddress]={}, [IsHeldOrder]={False},
[StockCommitted]={False}, [OrderComments]={Test},
[OrderTotal]={10492.74}, [OrderTax]={953.88}, [OrderWeight]={0},
[OrderPerimeter]={0}, [DespatchLocation]={I}, [DespatchMethod]={CC},
[DateDespatching]={4/04/2007 12:00:00 AM}, [DateDespatched]={1/01/0001
12:00:00 AM}, [DateDelivered]={1/01/0001 12:00:00 AM},
[DateInvoiced]={1/01/0001 12:00:00 AM}, [DateCancelled]={},
[DateCreated]={2/04/2007 1:22:06 PM}, [WhoCreated]={DavidC},
[DateModified]={2/04/2007 1:22:06 PM}, [WhoModified]={DavidC}}

STACK TRACE

at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at AppFramework.DalCn.TableUpdateBase(DataTable dataTable,
Boolean isTransaction)"

INSERT COMMAND

INSERT INTO PurchaseOrderEntry (BranchCode, ReferenceNumber,
SupplierLinkKey, PurchaseFinancialYear, PurchaseActiveMonth,
PurchaseActiveMonthFlag, PurchaseActiveMonthDate, TransactionType,
TransactionGroup, TransactionStatus, SupplierLedgerCode,
SupplierPaymentTerms, SupplierTradingName, CustomerOrderNumber,
SupplierTaxFlag, CustomerLinkKey, CustomerTradingName, DeliveryContactName,
DeliveryAddressLine1, DeliveryAddressLine2, DeliveryAddressLine3,
DeliveryAddressLine4, DeliverySuburb, DeliveryState, DeliveryPostcode,
DeliveryCountry, DeliveryDespatchCode, DeliveryLocationCode,
DeliveryFreightCode, DeliveryPhoneNumber, DeliveryPhoneOther1,
DeliveryPhoneOther2, DeliveryFaxNumber, DeliveryMobileNumber,
DeliveryEmailAddress, DeliveryWebAddress, IsHeldOrder, StockCommitted,
OrderComments, OrderTotal, OrderTax, OrderWeight, OrderPerimeter,
DespatchLocation, DespatchMethod, DateDespatching, DateDespatched,
DateDelivered, DateInvoiced, DateCancelled, DateCreated, WhoCreated,
DateModified, WhoModified) VALUES (?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)

MESSAGE

Invalid character value for cast specification

SQL TABLE SCRIPT

USE [MyBizData]
GO
/****** Object: Table [dbo].[PurchaseOrderEntry] Script Date: 04/02/2007
15:22:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PurchaseOrderEntry](
[BranchCode] [varchar](1) NOT NULL,
[ReferenceNumber] [varchar](8) NOT NULL,
[SupplierLinkKey] [int] NOT NULL,
[PurchaseFinancialYear] [int] NOT NULL,
[PurchaseActiveMonth] [int] NOT NULL,
[PurchaseActiveMonthFlag] [varchar](1) NOT NULL,
[PurchaseActiveMonthDate] [datetime] NOT NULL,
[TransactionType] [varchar](12) NOT NULL,
[TransactionGroup] [varchar](2) NOT NULL,
[TransactionStatus] [varchar](1) NOT NULL,
[SupplierLedgerCode] [varchar](1) NULL,
[SupplierPaymentTerms] [varchar](1) NULL,
[SupplierTradingName] [varchar](40) NULL,
[CustomerOrderNumber] [varchar](15) NULL,
[SupplierTaxFlag] [bit] NOT NULL,
[CustomerLinkKey] [int] NOT NULL,
[CustomerTradingName] [varchar](50) NULL,
[DeliveryContactName] [varchar](40) NULL,
[DeliveryAddressLine1] [varchar](50) NULL,
[DeliveryAddressLine2] [varchar](40) NULL,
[DeliveryAddressLine3] [varchar](40) NULL,
[DeliveryAddressLine4] [varchar](40) NULL,
[DeliverySuburb] [varchar](30) NULL,
[DeliveryState] [varchar](5) NULL,
[DeliveryPostcode] [varchar](6) NULL,
[DeliveryCountry] [varchar](5) NULL,
[DeliveryDespatchCode] [varchar](4) NULL,
[DeliveryLocationCode] [varchar](1) NULL,
[DeliveryFreightCode] [varchar](1) NULL,
[DeliveryPhoneNumber] [varchar](24) NULL,
[DeliveryPhoneOther1] [varchar](24) NULL,
[DeliveryPhoneOther2] [varchar](24) NULL,
[DeliveryFaxNumber] [varchar](24) NULL,
[DeliveryMobileNumber] [varchar](24) NULL,
[DeliveryEmailAddress] [varchar](50) NULL,
[DeliveryWebAddress] [varchar](50) NULL,
[IsHeldOrder] [bit] NOT NULL,
[StockCommitted] [bit] NOT NULL,
[OrderComments] [varchar](50) NULL,
[OrderTotal] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderEntry_OrderTotal]
DEFAULT ((0)),
[OrderTax] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderEntry_OrderTax]
DEFAULT ((0)),
[OrderWeight] [float] NOT NULL CONSTRAINT
[DF_PurchaseOrderEntry_OrderWeight] DEFAULT ((0)),
[OrderPerimeter] [float] NOT NULL CONSTRAINT
[DF_PurchaseOrderEntry_OrderPerimeter] DEFAULT ((0)),
[DespatchLocation] [varchar](20) NULL,
[DespatchMethod] [varchar](20) NULL,
[DateDespatching] [datetime] NULL,
[DateDespatched] [datetime] NULL,
[DateDelivered] [datetime] NULL,
[DateInvoiced] [datetime] NULL,
[DateCancelled] [datetime] NULL,
[DateCreated] [datetime] NULL,
[WhoCreated] [varchar](12) NULL,
[DateModified] [datetime] NULL,
[WhoModified] [varchar](12) NULL,
CONSTRAINT [PK_PurchaseOrderEntry] PRIMARY KEY CLUSTERED
(
[BranchCode] ASC,
[ReferenceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
 
N

Nicholas Paldino [.NET/C# MVP]

David,

This is not a bug. From the documentation from SQL Server 2005 Books
Online (located at http://msdn2.microsoft.com/en-us/library/ms187819.aspx)
states that the date range for a datetime column is from January 1, 1753,
through December 31, 9999.

So you entering the values of 1/01/0001 12:00:00 AM is invalid.

Why are you using these values? Why not use null?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

David Coleman said:
Hello all,

I am running XP Sp2, VS 2005 Sp1, Sql 2005 Sp2.

I am receiving the exception "Invalid character value for cast
specification." from the line

((DbDataAdapter)da).Update( dataSet );

The dataset contains a single table and the table contains a single row.
(see below).

The dataRow.HasErrors is set true, the dataRow.GetColumnsInError() returns
a zero element array.

The exception is overcome by setting the DateTime values from
DateTime.MinValue to DBNull.Value.

This is obviously a bug... Either dataRow.GetColumnsInError() should
return the DateTime columns in error
Or DateTime.MinValue should be an acceptable value for a DateTime column.

David.

DATA ROW.
{
RowState:Added. RowError:Invalid character value for cast specification.
[BranchCode]={H}, [ReferenceNumber]={E00009},
[SupplierLinkKey]={50052}, [PurchaseFinancialYear]={2006},
[PurchaseActiveMonth]={6}, [PurchaseActiveMonthFlag]={L},
[PurchaseActiveMonthDate]={30/06/2006 12:00:00 AM},
[TransactionType]={Purchase}, [TransactionGroup]={PO},
[TransactionStatus]={A}, [SupplierLedgerCode]={N},
[SupplierPaymentTerms]={}, [SupplierTradingName]={Cummins Marine
Business Unit}, [CustomerOrderNumber]={123456},
[SupplierTaxFlag]={False}, [CustomerLinkKey]={0},
[CustomerTradingName]={}, [DeliveryContactName]={David Coleman},
[DeliveryAddressLine1]={16 Uralba Place}, [DeliveryAddressLine2]={},
[DeliveryAddressLine3]={}, [DeliveryAddressLine4]={},
[DeliverySuburb]={Wahroonga}, [DeliveryState]={NSW},
[DeliveryPostcode]={2076}, [DeliveryCountry]={},
[DeliveryDespatchCode]={N}, [DeliveryLocationCode]={},
[DeliveryFreightCode]={N}, [DeliveryPhoneNumber]={02 9489 2069},
[DeliveryPhoneOther1]={}, [DeliveryPhoneOther2]={},
[DeliveryFaxNumber]={02 9489 1203}, [DeliveryMobileNumber]={0419 611 453},
[DeliveryEmailAddress]={[email protected]}, [DeliveryWebAddress]={},
[IsHeldOrder]={False}, [StockCommitted]={False},
[OrderComments]={Test}, [OrderTotal]={10492.74}, [OrderTax]={953.88},
[OrderWeight]={0}, [OrderPerimeter]={0}, [DespatchLocation]={I},
[DespatchMethod]={CC}, [DateDespatching]={4/04/2007 12:00:00 AM},
[DateDespatched]={1/01/0001 12:00:00 AM}, [DateDelivered]={1/01/0001
12:00:00 AM}, [DateInvoiced]={1/01/0001 12:00:00 AM},
[DateCancelled]={}, [DateCreated]={2/04/2007 1:22:06 PM},
[WhoCreated]={DavidC}, [DateModified]={2/04/2007 1:22:06 PM},
[WhoModified]={DavidC}}

STACK TRACE

at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping)\r\n at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)\r\n at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet)\r\n at AppFramework.DalCn.TableUpdateBase(DataTable dataTable,
Boolean isTransaction)"

INSERT COMMAND

INSERT INTO PurchaseOrderEntry (BranchCode, ReferenceNumber,
SupplierLinkKey, PurchaseFinancialYear, PurchaseActiveMonth,
PurchaseActiveMonthFlag, PurchaseActiveMonthDate, TransactionType,
TransactionGroup, TransactionStatus, SupplierLedgerCode,
SupplierPaymentTerms, SupplierTradingName, CustomerOrderNumber,
SupplierTaxFlag, CustomerLinkKey, CustomerTradingName,
DeliveryContactName, DeliveryAddressLine1, DeliveryAddressLine2,
DeliveryAddressLine3, DeliveryAddressLine4, DeliverySuburb, DeliveryState,
DeliveryPostcode, DeliveryCountry, DeliveryDespatchCode,
DeliveryLocationCode, DeliveryFreightCode, DeliveryPhoneNumber,
DeliveryPhoneOther1, DeliveryPhoneOther2, DeliveryFaxNumber,
DeliveryMobileNumber, DeliveryEmailAddress, DeliveryWebAddress,
IsHeldOrder, StockCommitted, OrderComments, OrderTotal, OrderTax,
OrderWeight, OrderPerimeter, DespatchLocation, DespatchMethod,
DateDespatching, DateDespatched, DateDelivered, DateInvoiced,
DateCancelled, DateCreated, WhoCreated, DateModified, WhoModified) VALUES
(?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?)

MESSAGE

Invalid character value for cast specification

SQL TABLE SCRIPT

USE [MyBizData]
GO
/****** Object: Table [dbo].[PurchaseOrderEntry] Script Date:
04/02/2007 15:22:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PurchaseOrderEntry](
[BranchCode] [varchar](1) NOT NULL,
[ReferenceNumber] [varchar](8) NOT NULL,
[SupplierLinkKey] [int] NOT NULL,
[PurchaseFinancialYear] [int] NOT NULL,
[PurchaseActiveMonth] [int] NOT NULL,
[PurchaseActiveMonthFlag] [varchar](1) NOT NULL,
[PurchaseActiveMonthDate] [datetime] NOT NULL,
[TransactionType] [varchar](12) NOT NULL,
[TransactionGroup] [varchar](2) NOT NULL,
[TransactionStatus] [varchar](1) NOT NULL,
[SupplierLedgerCode] [varchar](1) NULL,
[SupplierPaymentTerms] [varchar](1) NULL,
[SupplierTradingName] [varchar](40) NULL,
[CustomerOrderNumber] [varchar](15) NULL,
[SupplierTaxFlag] [bit] NOT NULL,
[CustomerLinkKey] [int] NOT NULL,
[CustomerTradingName] [varchar](50) NULL,
[DeliveryContactName] [varchar](40) NULL,
[DeliveryAddressLine1] [varchar](50) NULL,
[DeliveryAddressLine2] [varchar](40) NULL,
[DeliveryAddressLine3] [varchar](40) NULL,
[DeliveryAddressLine4] [varchar](40) NULL,
[DeliverySuburb] [varchar](30) NULL,
[DeliveryState] [varchar](5) NULL,
[DeliveryPostcode] [varchar](6) NULL,
[DeliveryCountry] [varchar](5) NULL,
[DeliveryDespatchCode] [varchar](4) NULL,
[DeliveryLocationCode] [varchar](1) NULL,
[DeliveryFreightCode] [varchar](1) NULL,
[DeliveryPhoneNumber] [varchar](24) NULL,
[DeliveryPhoneOther1] [varchar](24) NULL,
[DeliveryPhoneOther2] [varchar](24) NULL,
[DeliveryFaxNumber] [varchar](24) NULL,
[DeliveryMobileNumber] [varchar](24) NULL,
[DeliveryEmailAddress] [varchar](50) NULL,
[DeliveryWebAddress] [varchar](50) NULL,
[IsHeldOrder] [bit] NOT NULL,
[StockCommitted] [bit] NOT NULL,
[OrderComments] [varchar](50) NULL,
[OrderTotal] [money] NOT NULL CONSTRAINT
[DF_PurchaseOrderEntry_OrderTotal] DEFAULT ((0)),
[OrderTax] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderEntry_OrderTax]
DEFAULT ((0)),
[OrderWeight] [float] NOT NULL CONSTRAINT
[DF_PurchaseOrderEntry_OrderWeight] DEFAULT ((0)),
[OrderPerimeter] [float] NOT NULL CONSTRAINT
[DF_PurchaseOrderEntry_OrderPerimeter] DEFAULT ((0)),
[DespatchLocation] [varchar](20) NULL,
[DespatchMethod] [varchar](20) NULL,
[DateDespatching] [datetime] NULL,
[DateDespatched] [datetime] NULL,
[DateDelivered] [datetime] NULL,
[DateInvoiced] [datetime] NULL,
[DateCancelled] [datetime] NULL,
[DateCreated] [datetime] NULL,
[WhoCreated] [varchar](12) NULL,
[DateModified] [datetime] NULL,
[WhoModified] [varchar](12) NULL,
CONSTRAINT [PK_PurchaseOrderEntry] PRIMARY KEY CLUSTERED
(
[BranchCode] ASC,
[ReferenceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Click to expand...
 
Top