locked
DataContext.SubmitChanges method throwing exceptions? RRS feed

  • Question

  • Hello All,

    I am learning on how to insert data into the database using Linq and also to the ObservableCollection.

    The data gets inserted into the ObservableCollection but not to the database. Could somebody explain whats going on with the code below. The system throws an unhandled exception on submitchanges method. Please advice.

    public void populateDates(DateTime theWeek)
            {
                 ObservableCollection<theSchedule> theDatesList = new ObservableCollection<theSchedule>();
    
                for (int i = 0; i < 7; i++)
                {
                    theSchedule theShift = new theSchedule 
                    {
                       theDay = (theWeek.AddDays(i).ToString("dd/MM/yyyy")),
                       theTime = (theWeek.AddHours(i).ToString("HH:mm")) + " - " + (theWeek.AddHours(i+8).ToString("HH:mm"))
                    };
                    
                    theDatesList.Add(theShift);
                    //MessageBox.Show(theWeek.AddDays(i).ToString("HH:mm"));
                    shiftsDb.theSchedules.InsertOnSubmit(theShift);
                }
                shiftsDb.SubmitChanges();
                mylistbox.ItemsSource = theDatesList;
              }


    Thanks, Sumesh

    Friday, March 13, 2015 6:04 PM

Answers

  • Hello,

    With your provided code, I reproduce this issue, and it shows you are trying to insert a duplicated record to the database, I suggest that you could first do a check before inserting a record to database as:

    public void populateDates(DateTime theWeek)
    
            {
    
    
                ObservableCollection<theSchedule> theDatesList = new ObservableCollection<theSchedule>();
    
    
                for (int i = 0; i < 7; i++)
    
                {
    
                    theSchedule theShift = new theSchedule
    
                    {
    
                        theDay = (theWeek.AddDays(i).ToString("dd/MM/yyyy")),
    
                        theTime = (theWeek.AddHours(i).ToString("HH:mm")) + " - " + (theWeek.AddHours(i + 8).ToString("HH:mm"))
    
                    };
    
    
                    //code to check if the "theShift" with (theWeek.AddDays(i).ToString("dd/MM/yyyy")) value is already in databbase by querying the theSchedules tables
    
                    if (shiftsDb.theSchedules.Where(t => t.theDay == theShift.theDay).Count() == 0)
    
                    {
    
                        theDatesList.Add(theShift);
    
                        //MessageBox.Show(theWeek.AddDays(i).ToString("HH:mm"));
    
                        shiftsDb.theSchedules.InsertOnSubmit(theShift);
    
                        shiftsDb.SubmitChanges();
    
                    }
    
                }
    
            }
    

    When running your project, I found the populateDates(DateTime theWeek) would be called twice with a same DateTime value, please check it is by designed or you might need to check this part logic.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 9:14 AM

All replies

  • Hello,

    >>The system throws an unhandled exception on submitchanges method

    If it contains the detail exception information, please share it with us and your table. And according to your provided code, I created a test demo which could work fine, you could also have a try:

    The table:

    CREATE TABLE [dbo].[TestTable] (
    
        [TestID]     INT      NOT NULL,
    
        [theDay]   NVARCHAR(50)     NULL,
    
        [theTime] NVARCHAR(50) NULL,
    
        PRIMARY KEY CLUSTERED ([TestID] ASC)
    
    );
    

    The code used to insert data to database:

    using (DataClasses1DataContext db = new DataClasses1DataContext())
    
                    {
    
                        ObservableCollection<TestTable> theDatesList = new ObservableCollection<TestTable>();
    
    
                        for (int i = 0; i < 7; i++)
    
                        {
    
                            TestTable theShift = new TestTable
    
                            {
    
                                TestID = i,
    
                                theDay = (DateTime.Now.AddDays(i).ToString("dd/MM/yyyy")),
    
                                theTime = (DateTime.Now.AddHours(i).ToString("HH:mm")) + " - " + (DateTime.Now.AddHours(i + 8).ToString("HH:mm"))
    
                            };
    
    
                            theDatesList.Add(theShift);
    
                            //MessageBox.Show(theWeek.AddDays(i).ToString("HH:mm"));
    
                            db.TestTables.InsertOnSubmit(theShift);
    
                        }
    
                        db.SubmitChanges();
    
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 9:41 AM
  • Hello Fred Bao,

    Thanks for the reply.

    Here is the table class and the Data Context class.

     [Table]
        public class theSchedule
        {
            [Column(IsPrimaryKey = true)]
            public string theDay { get; set; }
            [Column]
            public string theTime { get; set; }
        }
    
        public class SchedulesDataContext : DataContext
        {
            public static string DBConnectionString = "Data Source=isostore:/Schedules.sdf"; //@"Data Source=D:\OneDrive\Projects\Windows Phone Projects\PivotItems\PivotItems\Schedules.sdf";//"Data Source=isostore:/Schedules.sdf";//
            public SchedulesDataContext(string connectionString) : base(connectionString) { }
            public Table<theSchedule> theSchedules;
        }

    And here is the code that populates the listbox which is bound to an Observablecollection and the database.

    public void populateDates(DateTime theWeek)
            {
               
               ObservableCollection<theSchedule> theDatesList = new ObservableCollection<theSchedule>();
    
                for (int i = 0; i < 7; i++)
                {
                    theSchedule theShift = new theSchedule 
                    {
                      theDay =  (theWeek.AddDays(i).ToString("dd/MM/yyyy")),
                      theTime = (theWeek.AddHours(i).ToString("HH:mm")) + " - " + (theWeek.AddHours(i+8).ToString("HH:mm"))
                    };
                    
                    theDatesList.Add(theShift);
                    //MessageBox.Show(theWeek.AddDays(i).ToString("HH:mm"));
                    shiftsDb.theSchedules.InsertOnSubmit(theShift);
                    shiftsDb.SubmitChanges();
                }
                   
                    mylistbox.ItemsSource = theDatesList;
              }

    Here is the exception that I get when I run it:

    1. SqlCeException was unhandled by user code.

    2. Error message in detail: "A duplicate value cannot be inserted into a unique index. [ Table name = theSchedule,Constraint name = PK_theSchedule ]".

    I am also attaching the link to the project here, just in case if you want to see the whole picture.

    Project Link

    Thanks again Fred Bao.


    Thanks, Sumesh



    Monday, March 16, 2015 1:11 PM
  • Hello,

    With your provided code, I reproduce this issue, and it shows you are trying to insert a duplicated record to the database, I suggest that you could first do a check before inserting a record to database as:

    public void populateDates(DateTime theWeek)
    
            {
    
    
                ObservableCollection<theSchedule> theDatesList = new ObservableCollection<theSchedule>();
    
    
                for (int i = 0; i < 7; i++)
    
                {
    
                    theSchedule theShift = new theSchedule
    
                    {
    
                        theDay = (theWeek.AddDays(i).ToString("dd/MM/yyyy")),
    
                        theTime = (theWeek.AddHours(i).ToString("HH:mm")) + " - " + (theWeek.AddHours(i + 8).ToString("HH:mm"))
    
                    };
    
    
                    //code to check if the "theShift" with (theWeek.AddDays(i).ToString("dd/MM/yyyy")) value is already in databbase by querying the theSchedules tables
    
                    if (shiftsDb.theSchedules.Where(t => t.theDay == theShift.theDay).Count() == 0)
    
                    {
    
                        theDatesList.Add(theShift);
    
                        //MessageBox.Show(theWeek.AddDays(i).ToString("HH:mm"));
    
                        shiftsDb.theSchedules.InsertOnSubmit(theShift);
    
                        shiftsDb.SubmitChanges();
    
                    }
    
                }
    
            }
    

    When running your project, I found the populateDates(DateTime theWeek) would be called twice with a same DateTime value, please check it is by designed or you might need to check this part logic.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 9:14 AM
  • Hi Fred Bao. Thanks for letting me know that populateDates function was getting triggered twice and that was causing all these problems. I never noticed it at all, I found out that it is getting triggered from the MainPage class constructor and from the pivot_selectionchanged event. I am not sure why the pivot_selctionchanged event is getting raised at the start.

    I am satisfied that finally I was able to get pass the exception error with a small logic. But I feel that's not the right way of doing it. Do you have any idea on that?

    Thanks anyway.


    Thanks, Sumesh

    Tuesday, March 17, 2015 2:56 PM
  • Hello Fred,

     if (shiftsDb.theSchedules.Where(t => t.theDay == theShift.theDay).Count() == 0)

    Could you please explain what this line does? I am trying to figure it out, but I am totally confused with the "t" in "

    t => t.theDay == theShift.theDay"

    Please advise

    Thanks.


    Thanks, Sumesh

    Tuesday, March 17, 2015 10:10 PM
  • Hello Sumesh,

    >>But I feel that's not the right way of doing it. Do you have any idea on that?

    As you mentions, this workaround is specifically for this exception, i think the root caused reason is the pivot_selctionchanged event is getting raised at the start, however, since it seems to be a Windows Phone event, this is beyond the scope of our support for this forum, my suggestion is that you could ask it on the Window Phone forum:https://social.msdn.microsoft.com/Forums/windowsapps/en-US/home?category=windowsapps, there are WP experts will help you.

    >>t => t.theDay == theShift.theDay

    This is lambda expression:

    https://msdn.microsoft.com/en-us/library/bb397687.aspx?f=255&MSPPError=-2147217396

    The "t" in this case represents a instance of theSchedules type.

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 18, 2015 8:08 AM
  • Thanks Fred Bao, Thanks for the links, I will have a look into it.

    Thanks, Sumesh

    Wednesday, March 18, 2015 12:56 PM