none
Linq to entities / changes done to datagridview not reflected in database RRS feed

  • Question

  • Hello,

    I am new to Linq and experience troubles getting following done.

    I have a datagridview(grdpricing) which I bind to the result of a Linq to entities query.  When I edit the datagridview, the changes are reflected on the grid but as soon as I call a db.savechanges(), it reloads the original content and the updates never reached the database.  I have been reading forums for hours but can't seem to pinpoint what I am doing wrong.  It seems pretty straigtforward seen I only want to write changes done in a databound grid back to the db. 

    Thanks for your help.

    VS 2010 / mysql 

    Here I load data into the grid:

    Dim qryPricing = From objProperty In dcVilla.tblpropertydetail
                         Join objPricing In dcVilla.tblpricing
                         On objProperty.prop_det_index Equals objPricing.prop_id
                         Where objProperty.prop_det_Status = True
                         Where objPricing.prop_state = True
                         Order By objPricing.prop_rental_single
                         Select New With {.prop_id = objPricing.prop_id, .prop_rental_single = objPricing.prop_rental_single, .prop_rental_double = objPricing.prop_rental_double, .prop_rental_triple = objPricing.prop_rental_triple, .prop_rental_Quadruple = objPricing.prop_rental_quadruple, .prop_state = objPricing.prop_state, .prop_date_active = objPricing.prop_date_active, .prop_date_inactive = objPricing.prop_date_inactive}
    
    
    
            grdPricing.DataSource = qryPricing
            grdPricing.AutoGenerateColumns = True

    and this code is used on the click event of a button:

    grdPricing.EndEdit()
    dcVilla.SaveChanges()

    Thanks a lot,

    Stijn

    Thursday, May 31, 2012 1:22 PM

Answers

  • Hi Stijn;

    To your statement, "The problem now is that the grid is editable and the savechanges() event passes without errors, tough the changes are not reflected in the database.", Your database is it a SQL Express or a SQL Ce database that is a db file located in your project? If so please read this Microsoft documentation on local databases.

    Debugging with Local Database File


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, June 3, 2012 7:33 PM

All replies

  • Hi Novayazemlya;

    The issue you are having is that the query that you are using returns objects of Anonymous type, which are NOT tracked by the ObjectContext. Only entities of the data model, those entities that are mapped to tables in the database, are tracked and updateable. Your options are to bring down the complete entities that you fill your DataGridView and load the controls with those values or as items are changes in the DataGridView control query the database for that record and make the changes to it.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, May 31, 2012 3:57 PM
  • Hello Fernando,

    First of all thanks for helping out.

    You offer 2 solutions

    1) Bring down the complete entities that fill the DGV, do you mean I have to mention all fields from the table in the select section of the query?  e.g. 

    Dim qryPricing = From objProperty In dcVilla.tblpropertydetail

    Join objPricing In dcVilla.tblpricing

    On objProperty.prop_det_index Equals objPricing.prop_id

    Where objProperty.prop_det_Status = True

    Where objPricing.prop_state = True

    Order By objPricing.prop_rental_single

    Select objPricing.prop_id, objPricing.prop_rental_single, objPricing.prop_rental_double, bjPricing.prop_rental_triple, objPricing.prop_rental_quadruple, objPricing.prop_state, objPricing.prop_date_active, objPricing.prop_date_inactive}

    2) This seems to be the more complicated way to do it but I will give it a try.

    Thanks for clarifying option 1

    Stijn

    Thursday, May 31, 2012 4:14 PM
  • Hi Novayazemlya;

    No, what you want to do is have the query return the complete row of data for each selected row so that Entity Framework creates a trackable entity in the ObjectContext. So the query select statement changes to this as shown in the below snippet.

    Dim qryPricing = From objProperty In dcVilla.tblpropertydetail
                     Join objPricing In dcVilla.tblpricing
                     On objProperty.prop_det_index Equals objPricing.prop_id
                     Where objProperty.prop_det_Status = True
                     Where objPricing.prop_state = True
                     Order By objPricing.prop_rental_single
                     Select objPricing

    Now you may need to hide the columns you do not want displayed in the DataGridView.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, May 31, 2012 5:27 PM
  • Hello Fernando,

    That works partially, I seem to be missing something else.

      Dim qryPricing = From objProperty In
      dcVilla.tblpropertydetail
      Join objPricing In dcVilla.tblpricing
      On objProperty.prop_det_index Equals objPricing.prop_id
      Where objProperty.prop_det_status = True
      Where objPricing.prop_state = True
      Order By objPricing.prop_rental_single
      Select objPricing
    
      gridtest.DataSource = qryPricing.ToList
      gridtest.AutoGenerateColumns = True

    Push save button:

     dcVilla.SaveChanges()

    The problem now is that the grid is editable and the savechanges() event passes without errors, tough the changes are not reflected in the database.

    Thanks again for your help.

    Stijn

    Sunday, June 3, 2012 7:13 PM
  • Hi Stijn;

    To your statement, "The problem now is that the grid is editable and the savechanges() event passes without errors, tough the changes are not reflected in the database.", Your database is it a SQL Express or a SQL Ce database that is a db file located in your project? If so please read this Microsoft documentation on local databases.

    Debugging with Local Database File


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, June 3, 2012 7:33 PM