SolvedPrivate

datagrid update back to database using LINQ

Posted on 2014-07-24
19
37 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

16 Experts available now in Live!

Get 1:1 Help Now