none
Problems Updating data using Linq to SQL RRS feed

  • Question

  • Hi all,  I am hoping someone can help me understand why the following code does not work to update data in a SQL Server Database using Linq to SQL.  Note that the following code snippet does work and the rows that have been changed are flaged with //<<<<<<<<<<<<<<<<<<<<

    So this is the code that does NOT work

            string pD_AbbrevDictLookUp(string Abbrev)
            {
                string expandedAbbrev = "";
                TTDataClassesDataContext dc = new TTDataClassesDataContext();
                string[] resultWords;
                char[] spaceSeparator = new char[] { ' ' };
                char[] commaSeparator = new char[] { ',' };
                resultWords = Abbrev.Split(spaceSeparator, StringSplitOptions.RemoveEmptyEntries);
                foreach (string resultWord in resultWords)
                {
                    var tT_PD_Abbrevs = (from a in dc.TT_PD_AbbrevDicts
                                        where a.Abbreviation == resultWord
                                        select new TT_PD_Abbrev_Data
                                        {
                                            Abbreviation = a.Abbreviation,
                                            AbbrevExpanded = a.AbbrevExpanded,
                                            AbbrevDescription = a.AbbrevDescription,
                                            ConversionOK = a.ConversionOK,
                                            Notes = a.Notes,
                                            ExampleUse = a.ExampleUse
                                        }
                               );
                    foreach (TT_PD_Abbrev_Data tT_PD_Abbrev in tT_PD_Abbrevs)    // Only Expecting ONE !!!
                    {
                        if (tT_PD_Abbrev != null)
                        {
                            expandedAbbrev += tT_PD_Abbrev.AbbrevExpanded.ToString().Trim();  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.ExampleUse = Abbrev;  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.Notes = "Updated: ";  // <<<<<<<<<<<<<<<<
                           
                            dc.SubmitChanges();
                        }

    WHERE AS this is the code that DOES work

     

            string pD_AbbrevDictLookUp(string Abbrev)
            {
                string expandedAbbrev = "";
                TTDataClassesDataContext dc = new TTDataClassesDataContext();
                string[] resultWords;
                char[] spaceSeparator = new char[] { ' ' };
                char[] commaSeparator = new char[] { ',' };
                resultWords = Abbrev.Split(spaceSeparator, StringSplitOptions.RemoveEmptyEntries);
                foreach (string resultWord in resultWords)
                {
                    var tT_PD_Abbrevs = (from a in dc.TT_PD_AbbrevDicts
                                        where a.Abbreviation == resultWord
                                        select new TT_PD_Abbrev_Data
                                        {
                                            Abbreviation = a.Abbreviation,
                                            AbbrevExpanded = a.AbbrevExpanded,
                                            AbbrevDescription = a.AbbrevDescription,
                                            ConversionOK = a.ConversionOK,
                                            Notes = a.Notes,
                                            ExampleUse = a.ExampleUse
                                        }
                               );
                    foreach (TT_PD_Abbrev_Data tT_PD_Abbrev in tT_PD_Abbrevs)    // Only Expecting ONE !!!
                    {
                        if (tT_PD_Abbrev != null)
                        {
                            expandedAbbrev += tT_PD_Abbrev.AbbrevExpanded.ToString().Trim();

                            var tT_PD_Abbrev_2 = dc.TT_PD_AbbrevDicts.Single(p => p.Abbreviation == resultWord);    // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev_2.ExampleUse = Abbrev;  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev_2.Notes = "Updated: ";  // <<<<<<<<<<<<<<<<

                            dc.SubmitChanges();
                        }


    Note that  TT_PD_Abbrev_Data is defined as follows:

    public class TT_PD_Abbrev_Data
    
        {
       
    public string Abbreviation { get; set; }
    
    public string AbbrevExpanded { get; set; }
    
    public string AbbrevDescription { get; set; }
    
    public string ConversionOK { get; set; }
    
    public string Notes { get; set; }
    
    public string ExampleUse { get; set; }
    
        }
    
    
    
    

    Any help understanding this would be appreciated.

    Thanks

    Terry Clancy

    ClanceZ


    Terry Clancy

    Thursday, February 9, 2017 5:53 AM

Answers

  • Hi Terry Clancy,

    Based on your description and related code, I create a simple demo and reproduce your issue on my side, because you retrieve the records from database and assign them to a TT_PD_Abbrev_Data object, which does not map to database. so it only change the value in object and does not change your database.

    Please modify your code like this:

     string expandedAbbrev = "";
                TTDataClassesDataContext dc = new TTDataClassesDataContext();
                string[] resultWords;
                char[] spaceSeparator = new char[] { ' ' };
                char[] commaSeparator = new char[] { ',' };
                resultWords = Abbrev.Split(spaceSeparator, StringSplitOptions.RemoveEmptyEntries);
                foreach (string resultWord in resultWords)
                {
                    var tT_PD_Abbrevs = from a in dc.TT_PD_AbbrevDicts
                                        where a.Abbreviation == resultWord
                                        select a; 
                    foreach (var tT_PD_Abbrev in tT_PD_Abbrevs)    // Only Expecting ONE !!!
                    {
                        if (tT_PD_Abbrev != null)
                        {
                            expandedAbbrev += tT_PD_Abbrev.AbbrevExpanded.ToString().Trim();  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.ExampleUse = Abbrev;  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.Notes = "Updated: ";  // <<<<<<<<<<<<<<<<
    
                            dc.SubmitChanges();
                        }
                    }
                }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 7:49 AM
    Moderator
  • Hi Terry Clancy,

    >> And that the NEW created a new object which broke the link to the database so I could then not update the database ?

    Yes, you need to attach your object to Dbcontext.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Terry Clancy Tuesday, March 7, 2017 8:46 AM
    Tuesday, March 7, 2017 8:44 AM
    Moderator

All replies

  • Hi Terry Clancy,

    Based on your description and related code, I create a simple demo and reproduce your issue on my side, because you retrieve the records from database and assign them to a TT_PD_Abbrev_Data object, which does not map to database. so it only change the value in object and does not change your database.

    Please modify your code like this:

     string expandedAbbrev = "";
                TTDataClassesDataContext dc = new TTDataClassesDataContext();
                string[] resultWords;
                char[] spaceSeparator = new char[] { ' ' };
                char[] commaSeparator = new char[] { ',' };
                resultWords = Abbrev.Split(spaceSeparator, StringSplitOptions.RemoveEmptyEntries);
                foreach (string resultWord in resultWords)
                {
                    var tT_PD_Abbrevs = from a in dc.TT_PD_AbbrevDicts
                                        where a.Abbreviation == resultWord
                                        select a; 
                    foreach (var tT_PD_Abbrev in tT_PD_Abbrevs)    // Only Expecting ONE !!!
                    {
                        if (tT_PD_Abbrev != null)
                        {
                            expandedAbbrev += tT_PD_Abbrev.AbbrevExpanded.ToString().Trim();  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.ExampleUse = Abbrev;  // <<<<<<<<<<<<<<<<
                            tT_PD_Abbrev.Notes = "Updated: ";  // <<<<<<<<<<<<<<<<
    
                            dc.SubmitChanges();
                        }
                    }
                }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 7:49 AM
    Moderator
  • Thank you VERY much for your great answer Cole.

    So is it true to say that my main problem is that in my first example I did a Select NEW.   And that the NEW created a new object which broke the link to the database so I could then not update the database ?

    Thanks again,

    Terry Clancy

    ClanceZ


    Terry Clancy

    Thursday, February 23, 2017 8:42 AM
  • Hi Terry Clancy,

    >> And that the NEW created a new object which broke the link to the database so I could then not update the database ?

    Yes, you need to attach your object to Dbcontext.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Terry Clancy Tuesday, March 7, 2017 8:46 AM
    Tuesday, March 7, 2017 8:44 AM
    Moderator
  • Great thanks Cole

    Terry Clancy

    Tuesday, March 7, 2017 8:46 AM