Solved

How can I Insert/Update datetime into SQL Server 2005 datetime column in Database from my C# application code?

Posted on 2008-10-27
6
9,994 Views
Last Modified: 2013-11-12
First of all, here is the architecture I am using:
--SQL Server 2005 database (production), 2008 database (development)
--C# (Silverlight) code on client/application side
--Entity Framework (Model that wraps database objects)
--ADO.NET Data Services (connect Entity Framework to Client commands to execute on DB)

In my development database (SQL Server 2008), everything is working fine when I insert a new DateTime into the one of my datetime columns in the DB.  This datetime is generated from the C# client side code (ie. DateTime.Now statement).  My Entity Framework model maps the C# DateTime to the Database datetime type.

However, when I try to run the Insert against my production database (SQL Server 2005), I get an error response that says:  "The version of SQL Server in use does not support datatype 'datetime2'."  But I know that I am not using 'datetime2' anywhere in my C# code or Entity Framework mapping or SQL Server DB's.  So I don't know why it is giving me this error response.  Does anyone have any idea what might be going on here?
0
Comment
Question by:robbersrow
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Can you post the code where you are declaring your datetime datatype in your C# code as well as the procedure code where you are executing it against.  I'm thinking somehow it thinks it's a date data type which is new to SQL 2008 but not in SQL 2005 and lower.
0
 

Author Comment

by:robbersrow
Comment Utility
COSProxy.COSEntities proxy = new COSProxy.COSEntities(new Uri("COSDataService.svc", UriKind.Relative));

COSProxy.Session s = new COSProxy.Session();

proxy.AddObject("Session", s);

DateTime tempDate = DateTime.Now;

s.StartTime = new DateTime(tempDate.Year, tempDate.Month, tempDate.Day, tempDate.Hour, tempDate.Minute, tempDate.Second, 0);

s.RefreshTime = new DateTime(tempDate.Year, tempDate.Month, tempDate.Day, tempDate.Hour, tempDate.Minute, tempDate.Second, 0);

// Now Save to database.

proxy.BeginSaveChanges(SaveChangesOptions.None, (asyncResult) =>
{
    proxy.EndSaveChanges(asyncResult);
},
null);

// Now it returns an error here, saying:  "The version of SQL Server in use does not support datatype 'datetime2'."

// My SQL Server 2005 DB and 2008 DB are both using "datetime" data type for the StartTime and RefreshTime mappings.  In other words, the database "datetime" data type corresponds to a C# "DateTime" object in the way they are mapped in the Entity Framework.  I believe the Entity Framework takes care of any procedure code necessary in the mapping between C# objects and database datatypes.
0
 

Author Comment

by:robbersrow
Comment Utility
Also, you had mentioned above:  "somehow it thinks it's a date data type which is new to SQL 2008 but not in SQL 2005 and lower."

But the error response I am getting indicates that it my be thinking it's a 'datetime2' data type (not a date data type).  The 'datetime2' data type is also new to SQL Server 2008.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Same thing applies in that it is an unsupported datatype for sql 2005.
0
 

Author Comment

by:robbersrow
Comment Utility
I realize that datetime2 data type is unsupported for SQL Server 2005.  But nowhere in my code, databases, or Entity Framework do I make any sort of reference to "datetime2" data type.  So I have no idea why this error would be occurring.  All my definitions, mappings, DB columns, etc. are set up to use the basic supported "datetime" data type, that is available in SQL 2005 and 2008.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
Comment Utility
I'm not very familiar with .NET but I would imagine that it build your DB references based upon the SQL 2008 instance.  Had you pointed it to 2005 to begin with it would have never thought it could use datetime2.

I think it would be best to get more advice for this from the .NET side.  In order to get some attention to this, since people may not want to jump in now that there are 6 comments, click the request attention link at the top and it will send a question alert to the .NET, SQL Server 2005 and WPF and Silverlight zone subscribers.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now