SolvedPrivate

datagrid update back to database using LINQ

Posted on 2014-07-24
19
40 Views
Last Modified: 2016-02-15
Hello;

Very simple need - I have a unbound datagrid, at runtime I set its datasource to the results of a LINQ query.
Allow user to move around/update.
Click a "save" button and cannot get database to update - no error however.
 setup datacontext:
 Public Class Import_items
    Dim db As New DataClasses1DataContext
 

Open in new window


 setup query and assign:

   Dim reportData = From l In db.Inventory_Items _
                  Select New Inventory_items With {.Item_Desc = l.Description, .Item_ID = l.Item_ID, .Qty_On_Hand = l.Qty_On_Hand _
                                                  , .Weight = l.Weight, .UOM = l.UOM, .Oversized_Flag = l.Oversized_Flag, _
                                                    .MIR_Flag = l.MIR_Flag, .BinLocationID = l.BinLocationID, .Bin_Desc = l.BinLocation.BinDesc}

        ExcelDataGridView.DataSource = reportData

Open in new window


Finally to save changes:
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        ' ExcelDataGridView.EndEdit() -- tried this also
        db.SubmitChanges()
    End Sub

Open in new window


I am obviously missing something very very basic, but about 8 hours into this now and ready to switch back to ADO!

Thank you,

Joe
0
Comment
Question by:Joe Ruder
  • 11
  • 8
19 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40217524
is it Entity Framework? try db.SaveChanges()
0
 

Author Comment

by:Joe Ruder
ID: 40217542
No...it is linq to sql.

savechanges does not exist for that.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40217563
SubmitChanges (as shown in http://msdn.microsoft.com/en-us/library/vstudio/bb399378(v=vs.100).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1) should work then.

Is is that your transformation (joined tables, query of a view, calculated fields, ...) is too complex?

Try with a plain old "select * from table" as your in input and test the SubmitChanges.

You might also start the SQL Profiler to check which queries are sent to the server.
0
 

Author Comment

by:Joe Ruder
ID: 40217631
I will test that shortly and let you know, but it certainly does not look too complex to me.

Is that an issue sometimes?

Joe
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40217648
you cannot update a view, JOINed query can normally update only the main table
0
 

Author Comment

by:Joe Ruder
ID: 40217662
I'm not using a join, just this:

 Dim reportData = From l In db.Inventory_Items _
                  Select New Inventory_items With {.Item_Desc = l.Description, .Item_ID = l.Item_ID, .Qty_On_Hand = l.Qty_On_Hand _
                                                  , .Weight = l.Weight, .UOM = l.UOM, .Oversized_Flag = l.Oversized_Flag, _
                                                    .MIR_Flag = l.MIR_Flag, .BinLocationID = l.BinLocationID, .Bin_Desc = l.BinLocation.BinDesc}

Open in new window

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40217676
"l.BinLocation.BinDesc" implies a JOIN
0
 

Author Comment

by:Joe Ruder
ID: 40218520
OK...getting closer.  Nothing to do with the join (it seems).

This works:

Dim reportdata2 = From t In db.Inventory_Items Select t

Open in new window


This will let you edit it but will not save it:

  Dim reportdata2 = From t In db.Inventory_Items Select New Inventory_items With {.Item_Desc = t.Description}

Open in new window


This won't even let you edit it -- acts like it is read only -- wth is going on?
Dim reportdata2 = From t In db.Inventory_Items Select t.Item_ID, t.Customer_ID, t.Description

Open in new window


I really don't want to have to return my entire dataset everytime.

Thank you,

Joe
0
 

Author Comment

by:Joe Ruder
ID: 40218530
I have even created a simple 1 member class:

Public Class testInventory
    Private _Item_Desc
    Public Property Item_Desc As String
        Get
            Return _Item_Desc
        End Get
        Set(value As String)
            _Item_Desc = value
        End Set
    End Property

End Class

Open in new window


then tried this:
Dim reportdata2 = From t In db.Inventory_Items Select New testInventory With {.Item_Desc = t.Description}

Open in new window


Same result -- lets me edit it, but will NOT save to the database.

Does anybody have a answer?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40219344
in your samples above, one is working - the one where you do only "select t".

in the other samples, do you retrieve the primary key?
0
 

Author Comment

by:Joe Ruder
ID: 40219404
I have updated it so that it does, same thing however.

The grid let you make changes, but then if you click save it does not write it out to the database.

Updated code:

Public Class testInventory
    Private _Item_Desc
    Private _Item_ID
    Private _Customer_ID

    Public Property Item_id As String
        Get
            Return _Item_ID
        End Get
        Set(value As String)
            _Item_ID = value
        End Set
    End Property
    Public Property Customer_ID As Integer
        Get
            Return _Customer_ID
        End Get
        Set(value As Integer)
            _Customer_ID = value
        End Set
    End Property

Open in new window


The query:
        Dim reportdata2 = From t In db.Inventory_Items Select New testInventory With {.Item_id = t.Item_ID, .Customer_ID = t.Customer_ID, .Item_Desc = t.Description}

Open in new window

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40219453
wait a minute! if you copy the result of your query into a new structure (here a list of testInventory), all the links to the database are lost. Why isn't "Select t" ok for you?
0
 

Author Comment

by:Joe Ruder
ID: 40219479
Good question,

T returns about 30 fields total (the entire dataset) - that is why I have the select new with and my own class.

Even if I do something like:

    Dim reportdata3 = From tt In db.Inventory_Items Select New testInventory

Open in new window


If I don't specify every item I get nothing returned, just a empty grid.

It  seems like we are soooo close!
0
 

Author Comment

by:Joe Ruder
ID: 40219498
Can I maybe just remove the columns that I don't need after the databinding?

Just reaching here...
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40219542
but if you do :
Dim reportdata3 = From tt In db.Inventory_Items

you have something in your grid, it is updatable and the the database gets updated right?

The way to do it would be to hide columns like this:
ExcelDataGridView.Columns(0).Visible = false
0
 

Author Comment

by:Joe Ruder
ID: 40219577
Dim reportdata3 = From tt In db.Inventory_Items

Yes...entire table drops in, everything is editable and savable.

...do you work this way, or do you use ADO for most of these type of needs?

Should I close this out as resolved and open up a new question since the scope has changed so much
0
 

Author Comment

by:Joe Ruder
ID: 40219579
You have resolved what the first problem was with me, just trying to work out a solution now.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 40219611
I don't use LINQ-to-SQL as this technology won't ever evolved. It was a test made by a team but the effort since then moved to Entity Framework.

Even with EF, we normally bind a grid to a full table and hide the columns not required. EF also let you create specialized queries on an entity which returns only the wanted columns (much like a view).
0
 

Author Closing Comment

by:Joe Ruder
ID: 40219748
Although I wish we had come up with a better solution, I think that biting the bullet now and switching to EF is the right move.

Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Visual Fox Pro commands 15 39
C#: need to import an entire CSV file 4 51
DB Mail Export to Excel some row wrap to new row 30 108
Slow process to read Excel 15 110
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now