CODEWITHSUNDEEP
c#wpflinq-to-sqlwindows-phone-7sql-server-ce
Tim Gabrhel
Tim Gabrhel

Reputation: 446

WP7 SQL CE - An overflow occurred while converting to datetime

My issue seems to be the same as this post and this post but neither seem to apply to my situation.

I have an entity defined as the following (modified to show columns and definitions only):

public partial class Payee
{
    [Column(Storage = "_PayeeId", IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false)]
    public int PayeeId { get; set; }

    [Column(Storage = "_PayeeName", DbType = "NVarChar(20)", CanBeNull = false)]
    public string PayeeName { get; set; }

    [Column(Storage = "_Active")]
    public System.Nullable<bool> Active

    [Column(IsVersion = true)]
    private Binary _version;

    [Association(Storage = "_Transactions", ThisKey = "PayeeId", OtherKey = "PayeeId")]
    public EntitySet<Transaction> Transactions { get; set; }
}

From App.xaml.cs, when the app initially starts, I am loading some sample data into the database:

Payee pay1 = new Payee { PayeeName = "My Bank" };
App.ViewModel.SavePayee(pay1);

And the SavePayee method is as follows:

public void SavePayee(Payee payee)
{
    Datacontext.Payees.InsertOnSubmit(payee);
    Datacontext.SubmitChanges();
}

This works fine when I create my 'sample data' when it is called from App.xaml.cs. Now, when I try to create a new Payee later on in the application (txtPayee.Text DOES have a value):

Payee payee = txtPayee.SelectedItem as Payee;
if (payee == null)
{
    payee = new Payee { PayeeName = txtPayee.Text };
    App.ViewModel.SavePayee(payee);
}

I'm given the error: An overflow occurred while converting to datetime.

What could be causing this error?

Upvotes: 0

Views: 3724

Answers (2)

fotisgpap
fotisgpap

Reputation: 397

In my case, I was trying to insert an uninitialized "System.DateTime" and my Database field was not nullable. As far as I can see from your log, TransDate is System.DateTime.MinValue. It would also give an overflow error even on a nullable datetime. You have to set it to null so the driver wil convert it to System.DBNull.Value.

Upvotes: 0

Tim Gabrhel
Tim Gabrhel

Reputation: 446

Turns out, in this case, the error message was SPOT ON. Here's how I found my error. Hopefully it helps others in the future:

My entity was being inserted without issue, and actually, the error message was correct. How did I figure out what was going on? I wrapped the contents of my save method with this code:

System.IO.IsolatedStorage.IsolatedStorageFile iso = System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication(); 
using (checkbookDB.Log = new System.IO.StreamWriter(iso.CreateFile("log.txt"))) 
{ 
}

So when an error would occur in that method, it would write out the results to the text file. After I got the error message, I downloaded the file from the emulator using the IsolatedStorageExplorerTool that came with the WP7 SDK. What did I find in the text file?

-- CURSOR BASED INSERT [Payees] 
-- [PayeeName] <= [test payee] 
-- [Active] <= [] 

-- AUTOSYNC [_version] <= [System.Byte[]] 
-- AUTOSYNC [PayeeId] <= [13] 
-- CURSOR BASED INSERT [Transactions] 
-- [TransDate] <= [1/1/0001 12:00:00 AM]
-- [TransAmount] <= [0] 
-- [TransType] <= [0] 
-- [RefNum] <= [] 
-- [Memo] <= [] 
-- [PayeeId] <= [0] 
-- [CategoryId] <= [] 
-- [AccountId] <= [1] 
-- [Cleared] <= [False]

Hmm....Ok...Looks like my Payee is inserting properly, and I even have a new PayeeId. But wait...Transaction? Why is there a transaction trying to insert?

Welp: I'm a noob to MVVM. I've been working with it off-hours for the last month and a half, so I'm not familiar with best practices. On my ViewModel, I created a public entity, named account. I used this variable to track the selected account in the application so the correct data is displayed all over the place. In my page that I was attempting to save a new Payee from, when the page is created, I create an instance of a Transaction entity to map data to/from controls on the screen. Well what I was doing, in my OnNavigatedTo method, was setting

transaction.Account = App.ViewModel.account

So when I would go to save the transaction, I knew which account to save it to. Well what this was really doing (I think), was telling my datacontext that Hey! I have this transaction entity that I am changing by adding an account to it. So when I would enter the datacontext.SaveChanges method, it tried to save an empty, BLANK transaction entity.

Why was this line way up in the OnNavigatedTo? I'm not sure. I moved it down to the Save method, where it belongs.

/doh

Upvotes: 1

Related Questions