Hello,
I have a problem with insert variables types from DateTime.
I have the following Table in a DB in SQL Server Express 2005:
USE [SCCS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Log](
[TimeID] [datetime] NOT NULL,
[LogType] [int] NULL,
[LogDesc] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[TimeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Then I have the following Stored Procedure in the DB:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsertLog](@UpdatedProdData nText)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData
INSERT INTO [SCCS].[dbo].[Log]
([TimeID]
,[LogType]
,[LogDesc])
SELECT
XMLProdTable.TimeID,
XMLProdTable.LogType,
XMLProdTable.LogDesc
FROM OPENXML(@hDoc, 'ArrayOfLog/Log', 2)
WITH (
TimeID datetime,
LogType int,
LogDesc nvarchar(50)
) XMLProdTable
EXEC sp_xml_removedocument @hDoc
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Then in my C# Solution I include in my LINQ to SQL Class (*.DBML File) the Table [Log] and the Stored Procedure dbo.spInsertLog. Then I will include records to this Table with the following code:
using (TestDataContext oDataBase = new TestDataContext())
{
Log[] aRecords = new Log[2];
for (int i = 0; i < 2; i++)
{
Log oRecord = new Log();
oRecord.TimeID = DateTime.Now.AddDays(Convert.ToDouble(i));
oRecord.LogType = i;
oRecord.LogDesc = "Desc: " + i.ToString();
aRecords[i] = oRecord;
}
StringBuilder sBuilder = new StringBuilder();
System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
XmlSerializer serializer = new XmlSerializer(typeof(Log[]));
serializer.Serialize(sWriter, aRecords);
oDataBase.spInsertLog(sBuilder.ToString());
}
When I call the line oDataBase.spInsertLog(sBuilder.ToString()); I receive a Exception:
SqlException
Conversion error when converting a string to datetime
Anyone can tell me I'm doing wrong?? Because if you change the DateTime type to long it works.