datagrid update back to database using LINQ

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
Joe RuderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
is it Entity Framework? try db.SaveChanges()
0
Joe RuderAuthor Commented:
No...it is linq to sql.

savechanges does not exist for that.
0
Éric MoreauSenior .Net ConsultantCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Joe RuderAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
you cannot update a view, JOINed query can normally update only the main table
0
Joe RuderAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
"l.BinLocation.BinDesc" implies a JOIN
0
Joe RuderAuthor Commented:
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
Joe RuderAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
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
Joe RuderAuthor Commented:
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
Éric MoreauSenior .Net ConsultantCommented:
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
Joe RuderAuthor Commented:
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
Joe RuderAuthor Commented:
Can I maybe just remove the columns that I don't need after the databinding?

Just reaching here...
0
Éric MoreauSenior .Net ConsultantCommented:
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
Joe RuderAuthor Commented:
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
Joe RuderAuthor Commented:
You have resolved what the first problem was with me, just trying to work out a solution now.
0
Éric MoreauSenior .Net ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe RuderAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.