Conversion failed when converting datetime from character string. Linq To SQL & OpenXML
- by chobo2
Hi
I been following this tutorial on how to do a linq to sql batch insert.
http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
However I have a datetime field in my database and I keep getting this error.
System.Data.SqlClient.SqlException was
unhandled Message="Conversion failed
when converting datetime from
character string." Source=".Net
SqlClient Data Provider"
ErrorCode=-2146232060 Class=16
LineNumber=7 Number=241
Procedure="spTEST_InsertXMLTEST_TEST"
Server="" State=1 StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler,
SqlDataReader dataStream,
BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject
stateObj)
I am not sure why when I just take the datetime in the generated xml file and manually copy it into sql server 2005 it has no problem with it and converts it just fine.
This is my SP
CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData
INSERT INTO UserTable(CreateDate)
SELECT XMLProdTable.CreateDate
FROM OPENXML(@hDoc, 'ArrayOfUserTable/UserTable', 2)
WITH (
CreateDate datetime
) XMLProdTable
EXEC sp_xml_removedocument @hDoc
C# code
using (TestDataContext db = new TestDataContext())
{
UserTable[] testRecords = new UserTable[1];
for (int count = 0; count < 1; count++)
{
UserTable testRecord = new UserTable()
{
CreateDate = DateTime.Now
};
testRecords[count] = testRecord;
}
StringBuilder sBuilder = new StringBuilder();
System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
serializer.Serialize(sWriter, testRecords);
db.spTEST_InsertXMLTEST_TEST(sBuilder.ToString());
}
Rendered XML Doc
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfUserTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<UserTable>
<CreateDate>2010-05-19T19:35:54.9339251-07:00</CreateDate>
</UserTable>
</ArrayOfUserTable>
