locked
update all rows that have the same EventID RRS feed

  • Question

  • Hi,

    I'm looking to see what would be the best way to achieve this. I have a table where all our event logs go into and we view them on a very basic C# LightSwitch Application that I have made to open in a web browser.

    My aim is: When a user updates a comment on an event. I want them to click a button that will update ALL other entries with that EventID. 

    I was maybe thinking going about it in SQL but I would really like it to be a button. And once clicked do a count on how many rows were updated, though that's not a massive pain.

    How would this be done? Any links would also be great.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 1:40 PM

Answers

  • Hello

    Without knowing much about the design of your app I would do it one of two ways. The first, and more simple way, would be to create an IEnumerable of the data set and select all the records that have the correct EventId and update them a little like this:-

    IEnumerable<YOUR_TABLE_NAME> tblUpdate = from items in this.YOUR_TABLE_NAME where items.EventId == EventId select items;
    
    int myCount = 0;
    
    foreach(var item in tblUpdate)
    {
        item.YOURFIELD = NEW VALUE;
        myCount += 1;
    }
    
    this.Save();
    
    this.ShowMessageBox(string.Format("{0} rows have been updated for EventId {1}", myCount, EventId));

    The other way would be to do an IDataServiceQueryable where you could write a linq query to do the same thing.

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    • Proposed as answer by Otis Ranger Monday, October 13, 2014 2:18 PM
    • Marked as answer by taylor.l Wednesday, October 15, 2014 7:15 AM
    Monday, October 13, 2014 2:03 PM
  • Thanks for all the help on this! But I've gone a different route and done it on the SQL server itself.

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    • Marked as answer by taylor.l Wednesday, October 15, 2014 3:45 PM
    Wednesday, October 15, 2014 3:45 PM

All replies

  • Hello

    Without knowing much about the design of your app I would do it one of two ways. The first, and more simple way, would be to create an IEnumerable of the data set and select all the records that have the correct EventId and update them a little like this:-

    IEnumerable<YOUR_TABLE_NAME> tblUpdate = from items in this.YOUR_TABLE_NAME where items.EventId == EventId select items;
    
    int myCount = 0;
    
    foreach(var item in tblUpdate)
    {
        item.YOURFIELD = NEW VALUE;
        myCount += 1;
    }
    
    this.Save();
    
    this.ShowMessageBox(string.Format("{0} rows have been updated for EventId {1}", myCount, EventId));

    The other way would be to do an IDataServiceQueryable where you could write a linq query to do the same thing.

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    • Proposed as answer by Otis Ranger Monday, October 13, 2014 2:18 PM
    • Marked as answer by taylor.l Wednesday, October 15, 2014 7:15 AM
    Monday, October 13, 2014 2:03 PM
  • Thank you for your reply I will now add this code and see what happens. Will this update all the rows with the same input by the user?

    Its a comment field where they type in whatever saying they have checked it and if we can ignore it etc. I need that comment to be updated into all the matching EventIDs


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 2:10 PM
  • Do you want the new text to be in the comments field or do you want it appending to? either way if you just replace the code above where it says item.YOURFIELD with the name of the comments field (e.g. if your comments field is called COMMENT you would enter item.COMMENT) and then to append you would replace  = NEW VALUE with += then the name of the object that is holding the typed comment. To replace the text dont put the + in and just have the =

     


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 2:17 PM
  • Currently getting this:

    could not find an implementation of the query pattern for source type 'where' not found

    I have the using system.linq;

    IEnumerable<c_Event> tblUpdate = from items in this.c_Event where items.EventId == EventId select items;
    
    int myCount = 0;
    
    foreach(var item in tblUpdate)
    {
        item.comments = NEW VALUE;  <<<< Here I would like to have the input from the user
        myCount += 1;
    }
    
    this.Save();
    
    this.ShowMessageBox(string.Format("{0} rows have been updated for EventId {1}", myCount, EventId));


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 2:21 PM
  • If you remove the where clause does it work?

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 2:31 PM
  • Nope. Just changes the 'where' to 'Select' cant be found.

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 2:34 PM
  • also make sure you are using System.Collections.Generic;

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 2:34 PM
  • Also just realised that the YOUR_TABLE_NAME may have to be a table that has been added to the Screen (i.e. You selected Add Data Item on the screen designer and added the data set to the screen (so it shows on the left hand side))


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 2:36 PM
  • Yeah thats in there. I think it might be because I'm putting the button in a details screen under this:

    Partial void UpdateAll_Execute()
    {
    
    The code you supplied here
    
    }



    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 2:38 PM
  • Nope, that shouldnt matter as I do something similar with one of my apps. Are you sure that the tablename\dataset that you are updating is called c_Event AND the table is called c_Event?

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 2:41 PM
  • Well the actual table name is Events (in SQL) but VS calls it c_Event

    actual error is this:

    Could not find an implementation of the query pattern for source type 'LightSwitchApplication.c_Event'. 'Where' not found.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by taylor.l Monday, October 13, 2014 2:56 PM
    Monday, October 13, 2014 2:46 PM
  • could you show a screen shot of the left hand side of the design screen so I can see the table names? Of course if it is too sensitive please dont but i need to get an idea of the data sets that you have

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 3:13 PM
  • Machine is off a network so had to use phone. I hope this is what you're after


    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by taylor.l Monday, October 13, 2014 3:19 PM
    Monday, October 13, 2014 3:19 PM
  • can you do the same of the design window? i.e. not the database screen, where you lay out the buttons etc.

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 3:32 PM
  • Hope this is what you're after :)


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 3:35 PM
  • That is exactly what I wanted to see but it hasnt helped :(

    THe code really should work but lets try something else. Can you please create a query based on the Events Table called OrderedEvents and just add an order onto the table. Then add this query to your screen and change the earlier code from YOUR_TABLE_NAME to OrderedEvents and see what happens.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 3:47 PM
  • Okay that has seemed to have worked. No red lines or anything. I can go into the application and click update all button and it gives me a window saying 0 records have been updated. I assume that's because I've changed some stuff:

    IEnumerable<c_Event> tblUpdate = from items in this.OrderedEvents where items.EventId == c_EventId select items;
    
    int myCount = 0;
    
    foreach(var item in tblUpdate)
    {
        item.comments = c_Event.Comments;
        myCount = 1;
    }
    
    this.Save();
    
    this.ShowMessageBox(string.Format("{0} rows have been updated for EventId {1}", myCount, c_EventId));


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 3:56 PM
  • Right, what we now need to do is put breakpoints on the query and from within the foreach loop. This way we can make sure that the query is returning something and it is going round the loop.

    Also you need to change the myCount = 1; line to myCount += 1;

    Also not sure about the c_Event.Comments; part that you are updating item.comments to. but see how the breakpoints go first.

    We are getting there :)


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 4:00 PM
  • The thing is it tries to update on the actual ID of the record i.e. 11321 not EventID 1112.

    Let me google break points real quick


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Monday, October 13, 2014 4:04 PM
  • A breakpoint is where you click to the left of the code and a small red dot appears and highlights all the code. When you run the code it will stop at that line so you can see what variables etc are being displayed.

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 4:09 PM
  • an I put that on the foreach right?

    EDIT: IT JUST LOCKS UP IE :(

    EDIT 2: I'll have to pick this up tomorrow morning. Thank you for all the help


    If this is helpful please mark it so. Also if this solved your problem mark as answer.



    • Edited by taylor.l Monday, October 13, 2014 4:35 PM
    Monday, October 13, 2014 4:11 PM
  • Edit 1 answer: That is the code hitting the break point. You need to look at the editor and whn you are at the breakpoint hover the mouse pointer over the variable names and see what values show. i am sure that this will help

    edit 2 answer: sure.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, October 13, 2014 7:24 PM
  • You can use LINQ to update the set more directly using a lambda.

    // assuming c_EventID is already initialized and you want
    // to apply a comment with a value of newComment (also set)
    // TRY this approach... replacing MyDataSourceName with 
    // the name of the data source you use to access the 
    // OrderedEvents entity.
    
    var matchedEvents = 
       from c_Event matchingEvents in 
                DataWorkSpace.MyDataSourceName.OrderedEvents 
       where matchingEvents.c_EventID == c_eventID
       select matchingEvents;
    
    // This doesn't return a value - the select and .ToList()
    // force the execution of the lambda.
    
    matchedEvents.Select ({ 
           c => c.Comments = newComment; return c
       }).ToList();
    
    matchedEvents.ApplyChanges();

    Try the approach above.  It's a more efficient approach and should give you fewer round-trips to the database.

    Source:  Stack Overflow

    Tuesday, October 14, 2014 5:25 AM
  • This part of the code:

    IEnumerable<c_Event>

    When I highlight over the <c_Event> section. I shows me the data inside the LEVEL column e.g. "Warning" or "Error"

    I wonder with how I have this setup is messing about with this? 


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Tuesday, October 14, 2014 7:53 AM
  • Depending on your source recordset type, you might be returning an IQueryable<c_Event> rather than an IEnumerable<c_Event> - if this is the case, "var" will implicitly assign the correct type, and you can still use LINQ against the target.  If you SPECIFICALLY need to work with IEnumerable<c_Event> then you could wrap your LINQ query with parentheses and use the .AsEnumerable<c_Event>() function to convert the LINQ result to IEnumerable. :)

    Tuesday, October 14, 2014 8:05 AM
  • This part of the code:

    IEnumerable<c_Event>

    When I highlight over the <c_Event> section. I shows me the data inside the LEVEL column e.g. "Warning" or "Error"

    I wonder with how I have this setup is messing about with this? 


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    This, i dont think, is anything to worry about. More often than not the first value you see is the default or summary value for the table and I suspect that this is the LEVEL column of your table.

    Below is an image of what I was expecting to see. This is from one of my own apps where you will see the similarity in the code

    The first part of the image is with the breakpoint on the query. I then hit F11 to move the code on one step. You can then see in the second picture I have hovered over the updRec variable and this is where i am getting all teh data. You can then expand this to see the data that is from the query.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Tuesday, October 14, 2014 8:43 AM
  • Sorry for the late reply I got caught up in another project and now finally have time to focus on this. 

    Otis when I run mine with a breakpoint I do not get a results view at all. As you can see in the image.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.



    • Edited by taylor.l Tuesday, October 14, 2014 12:12 PM
    Tuesday, October 14, 2014 12:01 PM
  • Hello

    If you hovered ove the items part in the query line what do you get?

    I have a feeling that the query isnt returning any records as you are expecting the c_EventID to be a different value than it is. If you replace c_EventID with tblEvents.SelectedItem.EventId see what happens.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Tuesday, October 14, 2014 12:40 PM
  • If you replace c_EventID with tblEvents.SelectedItem.EventId see what happens.

    Well I get this 'LightSwitchApplication.tblEvents' does not contain a definition for 'SelectedItem'

    And noting pops up when hovering over items

    This is becoming a troublesome problem. I just made a Stored Procedure in SQL would it be easier to call this on the button click?


    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by taylor.l Tuesday, October 14, 2014 1:43 PM
    Tuesday, October 14, 2014 1:40 PM
  • My bad, i assumed that the results were in a grid :$ Shouldn't assume!!

    If you add a dot after the c_EventID does it then give you a list of the column names? If yes then select the EventID column and that should give you what you are looking for.

    I am sorry that this hasnt been as quick a fix as you would like.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Tuesday, October 14, 2014 2:44 PM
  • No, no column names just CompareTo, Equals, GetHashCode.... etc

    I thought of that but it wasn't having any of it. The length of time to get it fixed is fine. Just wish I understood it more to be honest.

    Like why is it still able to know what c_EventID is when the table is no longer that name.

    EDIT: Good news I've got it to update 2 records.. but not all. What I did was. Delete the whole screen and create a new one. Once there I added a new query for tblEvents and changed the code slightly. And it actually updated to comments! 

    So. With this knowledge now I need to figure out why only 2 are being updated.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.




    • Edited by taylor.l Tuesday, October 14, 2014 3:43 PM
    Tuesday, October 14, 2014 2:50 PM
  • Think I found out why its only finding 2. It could be due to paging on the screen.

    EDIT: Yup this is due to paging on the screen. If I disable the paging it crashes and doesn't work. Any ideas?


    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by taylor.l Wednesday, October 15, 2014 9:43 AM
    Wednesday, October 15, 2014 8:33 AM
  • Hello

    Good news that you have something working.......almost! :)

    The paging could well be an issue if you are using the same dataset to show the records as you are trying to loop through. I know that this could be a bad thing but we could disable and then enable the paging on the recordset in order to get all the records updated.

    First, in your screens _Created() event put the following:-

    var btnUpdate = this.FindControl("YOUR_BUTTON_NAME");
    btnUpdate.ControlAvailable += UpdateButton_ControlAvailable;

    Then copy and paste the code below elsewhere in the page (i.e. NOT in the _Created() event or any other.

            void UpdateButton_ControlAvailable(object sender, ControlAvailableEventArgs e)
            {
                this.FindControl("YOUR_BUTTON_NAME").ControlAvailable -= UpdateButton_ControlAvailable;
                var Button = (Button)e.Control;
                Button.Click += UpdateButton_Click;
            }
    
            void setPagingToZero()
            {
                Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty collectionProperty = this.Details.Properties.YOUR_SCREEN_QUERY;
                collectionProperty.PageSize = 0;
            }
    
            void resetPaging()
            {
                Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty collectionProperty = this.Details.Properties.YOUR_SCREEN_QUERY;
                collectionProperty.PageSize = 45;	//This value should be your original page size. Can be set with variable elsewhere
            }
    
            void UpdateButton_Click(object sender, System.Windows.RoutedEventArgs e)
            {
    	    setPagingToZero();
    	    // INSERT YOUR BUTTON CODE ALL IN HERE!!
    	    resetPaging();
    	}
    You will, of course, have to take all the code out from your button event otherwise you'll have more troubles that you dont need :)

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)


    • Edited by Otis Ranger Wednesday, October 15, 2014 9:50 AM forgot something!
    Wednesday, October 15, 2014 9:49 AM
  • Seem to be getting some red lines. Any ideas?

     

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, October 15, 2014 10:16 AM
  • Ha ha ha yes! You have put the UpdateButton_ControlAvailable code etc a little too low down in your code :)

    If you can move the last two curly braces "}" from above the UpdateButton_ControlAvailable line to under the resetPaging() code snippet you should be in business


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Wednesday, October 15, 2014 10:24 AM
  • That worked a treat :P

    Just one more


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, October 15, 2014 10:39 AM
  • You will need to add the following to the top of your code:-

    using System.Windows.Controls;


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Wednesday, October 15, 2014 10:55 AM
  • That worked. But now I am back to only updating 2 records which to me seems like the set paging to 0 is not kicking in. 

    EDIT: Maybe due to editing the wrong collection. I will have a play about and post my findings.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by taylor.l Wednesday, October 15, 2014 11:15 AM
    Wednesday, October 15, 2014 11:02 AM
  • Just to introduce a little fudge :) within the UpdateButton_Click event under the setPagingToZero() line write:-

    resetPaging();
    setPagingToZero();

    So you are in effect changing it and then changing it back to then change it again. This *should* give it enough time to actually change and work for you

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Wednesday, October 15, 2014 11:25 AM
  • Sadly this didn't work either :(

    If I actually disable paging on the "query" it crashes IE and sends the memory usage to 800mb+ 


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, October 15, 2014 12:36 PM
  • If your table contains hundreds of records then disabling paging for this may not be the best idea. Perhaps stepping through the pages would be a better idea. I cannot come up with code right now but will see what i can do later.

    Although at this point it might be better to suggest the other way to do it which i will try and code later as well


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)


    • Edited by Otis Ranger Wednesday, October 15, 2014 12:58 PM added an although
    Wednesday, October 15, 2014 12:57 PM
  • I was trying to google this. How to step through pages to check as my Table contains thousands of records, which I thought was the case for the freezing and high memory usage. Are you able to provide me some links on this code?

    Just so I can compare it with anything you might kindly supply.


    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, October 15, 2014 1:03 PM
  • Thanks for all the help on this! But I've gone a different route and done it on the SQL server itself.

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    • Marked as answer by taylor.l Wednesday, October 15, 2014 3:45 PM
    Wednesday, October 15, 2014 3:45 PM