locked
how to get id for newly created record but not jet saved RRS feed

  • Question

  • Hello there,

    This is my problem. I have 2 unrelated tabels but when I create a new record in table 1 a new record also should be created in table 2 with different values from table 1. the code I used is this:

            Private Sub PositieFilter_Changed(e As NotifyCollectionChangedEventArgs)
                If (e.Action = Collections.Specialized.NotifyCollectionChangedAction.Add) _
                    And (Me.PositieGroep IsNot Nothing) Then
                    For Each pos As Positie In e.NewItems
                        pos.Project_Id = Me.PositieGroep.SelectedItem.Project_Id
                        pos.PosGrp_Id = Me.PositieGroep.SelectedItem.Id
                        pos.Prijs = 0
                    Next
                    If Me.PositieFilter IsNot Nothing Then
                        Dim calc = Me.DataWorkspace.ApplicationData.Calculaties.AddNew()
                        calc.Project_Id = Me.PositieGroep.SelectedItem.Project_Id
                        calc.PosGrp_Id = Me.PositieGroep.SelectedItem.Id
                        calc.Pos_Id = Me.PositieFilter.SelectedItem.Id
                    End If
                End If
            End Sub

    the problem is that everything is written except the calc.pos_Id whitch is the new Id from thePositieFilter table.

    So how can I get the ID from a newly unsafed record at screen level?

    Tuesday, February 11, 2014 11:46 AM

Answers

  • I see. Then I think you have a limited number of options because you cannot obtain the ID automatically. Lightswitch uses a Save pipeline that pretty much does the complete transaction for you, so it will not be available. The problem is the data is not really related.

    I think the options here are to:

    • Have a predetermined ID to give each
    • Use a trigger on the database level
    • Use a stored procedure called through Lightswitch (Better if it is only one entry)

    Stored procedures are sort of a hack, but it is possible and I have used it before in a LS project.

    • Marked as answer by Angie Xu Monday, February 24, 2014 12:53 AM
    Wednesday, February 12, 2014 11:06 PM

All replies


  • Sql Server provides the Id. So, you can only get the id when the entity is submitted to the database.

    What you can do is move the logic to the server side and do the save in 2 steps in such a way step2 has the ID from step 1. The advantage of moving it server side is that things are done in a transactional way automatically.


    paul van bladel

    • Proposed as answer by babloo1436 Tuesday, February 11, 2014 6:12 PM
    Tuesday, February 11, 2014 2:08 PM
  • The issue I am getting from your scenario is that you say "unrelated tables", but then say the ID references the other, which implies that they are, in fact, related after all.

    If they are related, you can reference the new item that is created before it is saved in the database by creating a new item and storing it in a variable and assigning it to the new table. (Lightswitch will then create the reference automatically after it is saved, like you mention).

    If they are, in fact, not related, then what I would do is save the unsaved items first. Because like Paul has mentioned, it is impossible to have an id before an item is saved in the database. Unless of course... you create a complicated procedure to return the next available ID, which I do NOT recommend if more than one person is using the database at a time anyway.

    • Edited by agmarkis Tuesday, February 11, 2014 5:54 PM
    Tuesday, February 11, 2014 5:53 PM
  • Ok my bad,

    I thought the Id was provided after creating a record on screen level by SQL, but looking at your replies, it's only provided after the save? So what I have to do is a query after the save to get the ID of the last record ?

    @agmarkis

    it looks like a relation but it isn't. it's a one 2 one situation. I'm programming in lightswitch, and creating a relation in my situation would create some disadvanteges.

    The problem is the my "relations" go 5 levels (tables) deep and lightswitch is not build for this kind of complexity. After 2 levels on one screen (based on relations) it loses focus. So I have to filter the tables based on selection to go 5 levels deep and to display it on one screen.

    I didn't find any other solution yet, but I am a newbie so I could be wrong.

    I only use this ID for rapports that I want to generate.


    Wednesday, February 12, 2014 11:44 AM
  • I see. Then I think you have a limited number of options because you cannot obtain the ID automatically. Lightswitch uses a Save pipeline that pretty much does the complete transaction for you, so it will not be available. The problem is the data is not really related.

    I think the options here are to:

    • Have a predetermined ID to give each
    • Use a trigger on the database level
    • Use a stored procedure called through Lightswitch (Better if it is only one entry)

    Stored procedures are sort of a hack, but it is possible and I have used it before in a LS project.

    • Marked as answer by Angie Xu Monday, February 24, 2014 12:53 AM
    Wednesday, February 12, 2014 11:06 PM
  • hello,

    I had the same requirement but couldn't achieve it without using the saved method, all I did was to add a code into the _Saved() method of my CreateNewItem screen.

    let say that you are creating a new product as follows:

    partial void CreateNewOrderTransfer_Saved()

    {

    Shipment sp = this.DataWorkspace.ApplicationData.Shipment.AddNew();

    sp.ProductID = this.OrderProperty.Id.ToString();

    this.Close(false);

    Application.Current.ShowDefaultScreen(this.OrderProperty);

    }


    mohammad Samaha


    Wednesday, April 9, 2014 8:03 PM