none
SubmitChanges() doesn't submit updates RRS feed

  • Question

  • Hello,

    I'm trying to learn LinQ. Tried some select statements - works for me fine Smile But tried update statement - it doesn't. Can't figure it out why. Here's my whole test code.

    namespace TestLinQ
    {
        public partial class Form1 : Form
        {
            private string connString1 = @"server=.\sqlexpress;integrated security=true;database=TestDB";
            private DataContext db1;
            private Table<Products> products;

            public Form1()
            {
                InitializeComponent();
                db1 = new DataContext(connString1);
                products = db1.GetTable<Products>();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                Products product = products.Single(prod => prod.ID == 2);
                product.ProductName = "Updated product name";
                product.ProductNotes = "Updated product notes";
                try
                {
                    db.SubmitChanges();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                var queryResul = products.Single(prod => prod.ID == 2);
                textBox1.Text += String.Format("Product name -> {0}; Product notes -> {1}", queryResul.ProductName, queryResul.ProductNotes) + "\r\n";
            }
        }

        [Table]
        public class Products
        {
            [Column]
            public Int32 ID;
            [Column]
            public string ProductNotes;
            [Column]
            public string ProductName;
        }
    }
    Friday, December 5, 2008 1:19 PM

Answers

  •  St8-15 wrote:

        [Table]
        public class Products
        {
            [Column]
            public Int32 ID;
            [Column]
            public string ProductNotes;
            [Column]
            public string ProductName;
        }
    }

     

    Try:

     

    Code Snippet

    [Column(IsPrimaryKey = true)]

    public int ID { get; set; }

     

     

    Monday, December 8, 2008 5:17 AM
    Answerer
  •  St8-15 wrote:
    And here I come again

    Now insert new record doesn't work for me
    It throws an excption - "Cannot insert value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF. But I can't find anywhere how I can set it on.

     

    If the column is an identity column you need to also set "IsDbGenerated" to true in the column attribute:

     

    Code Snippet

    [Column(IsPrimaryKey=true, IsDbGenerated=true)]

    public Int32 ID { get; set; }

     

     

     

    ...and for consistency (and to avoid running into problems in case some internal mechanism expects a property instead of a field) you may want to change all members to properties instead of fields.
    Monday, December 8, 2008 9:06 AM
    Answerer

All replies

  • You are probably suffering from the 'copy the database to the runtime directory' problem that happens by default.  Is is a property on the DBML file in the project.  Every time you run under debug you get a new copy of the database.

     

    Saturday, December 6, 2008 6:44 PM
    Moderator
  •  St8-15 wrote:

        [Table]
        public class Products
        {
            [Column]
            public Int32 ID;
            [Column]
            public string ProductNotes;
            [Column]
            public string ProductName;
        }
    }

     

    Try:

     

    Code Snippet

    [Column(IsPrimaryKey = true)]

    public int ID { get; set; }

     

     

    Monday, December 8, 2008 5:17 AM
    Answerer
  • Thank you, your code solved it Smile
    I did try

    [Column(IsPrimaryKey = true)]
    myself, but it didn't work either... Until you posted that it should be

    public int ID { get; set; } instead of public Int32 ID;


    Thank you again Smile

    Monday, December 8, 2008 7:54 AM
  • And here I come again Smile

    Now insert new record doesn't work for me Smile
    It throws an excption - "Cannot insert value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF. But I can't find anywhere how I can set it on.
    The code is basicly the same, except it uses insert statement:


    namespace TestLinQ
    {
        public partial class Form1 : Form
        {
            private string connString1 = @"server=.\sqlexpress;integrated security=true;database=TestDB";
            private DataContext db1;
            private Table<Products> products;

            public Form1()
            {
                InitializeComponent();
                db1 = new DataContext(connString1);
                products = db1.GetTable<Products>();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                Products product = new
    Products();
                product.ProductName = "New product name";
                product.ProductNotes = "New product notes";
                try
                {
    products.InsertOnSubmit(product);
                    db.SubmitChanges();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                var queryResul =
    products.ToArray<Products>();
    foreach (var resultRow in queryResul)
                      textBox1.Text += String.Format("Product name -> {0}; Product notes -> {1}", queryResul.ProductName, queryResul.ProductNotes) + "\r\n";
            }
        }

        [Table]
        public class Products
        {
            [Column]
            public Int32 ID { get; set; }
            [Column]
            public string ProductNotes;
            [Column]
            public string ProductName;
        }
    }

    Monday, December 8, 2008 8:37 AM
  •  St8-15 wrote:
    And here I come again

    Now insert new record doesn't work for me
    It throws an excption - "Cannot insert value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF. But I can't find anywhere how I can set it on.

     

    If the column is an identity column you need to also set "IsDbGenerated" to true in the column attribute:

     

    Code Snippet

    [Column(IsPrimaryKey=true, IsDbGenerated=true)]

    public Int32 ID { get; set; }

     

     

     

    ...and for consistency (and to avoid running into problems in case some internal mechanism expects a property instead of a field) you may want to change all members to properties instead of fields.
    Monday, December 8, 2008 9:06 AM
    Answerer
  • Thank you once again.

    You're making more developers to discover LinQ Smile
    Monday, December 8, 2008 10:17 AM