Update vs Insert... - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Update vs Insert...

Last post 11-09-2004 11:43 AM by sakieboy. 6 replies.
Page 1 of 1 (7 items)
Sort Posts: Previous Next
  • 11-08-2004 01:03 PM

    Update vs Insert...

    I have a scenario that I'm using 2 tables. The record always exists in Table 1, but, may or may not exist in Table 2. My query dropdown is based on Table 1. When this is selected the Data Information is populated. The Data Information may or may not consist of data from Table 2, depending on whether or not data has been entered into Table 2. OK...Item is selected and the fields that come from Table 2 are not populated. All this is OK and what I expect. But, when I submit, it's running the UPDATE routine, instead of the INSERT routine. I would like the new information inputted into the form to be INSERTED into Table 2.
  • 11-09-2004 02:24 AM In reply to

    • jonbrave
    • Top 200 Contributor
    • Joined on 10-15-2004
    • United Kingdom
    • Posts 59
    Dunno what you mean here. I have no trouble with this situation. What do you mean: "it's running the UPDATE routine" ??

    Oh, OK, you mean you have a 1-to-1 mapping (or, rather, 1-to-0-OR-1)? (I have 1-to-many, and user actually inserts into Table2 via IP, so I get added records in Table2).

    Seems to me it's going to treat this as an update to Table1. Are you using a webservice? If so, unless anyone says otherwise, I think you will have to recognise this case in code and deal with explicitly.
  • 11-09-2004 05:11 AM In reply to

    I have no problem doing this within the code, but I'm not sure of where and what to put into the code. I'm using VB.Net. I have a section already set up for the Submit -->

    <InfoPathEventHandler(MatchPath:="CTRLSubmit", EventType:=InfoPathEventType.OnClick)> _
    Public Sub CTRLSubmit_OnClick(ByVal e As DocActionEvent)
    ' Write your code here.
    thisXDocument.Submit()

    If e.ReturnStatus = True Then
    thisXDocument.UI.Alert("The form was submitted successfully.")
    Else
    thisXDocument.UI.Alert("Submit failed, please contact your system administrator.")
    End If
    End Sub

    Do I need to replace this with something else? Is there any sample code out there that will catch the Insert (if MyID is Null) and Update (if MyID is not NULL)?
  • 11-09-2004 06:11 AM In reply to

    • jonbrave
    • Top 200 Contributor
    • Joined on 10-15-2004
    • United Kingdom
    • Posts 59
    What I meant here is: do it in *WebService* code. That's why I asked you if you were using one. When you go:
    thisXDocument.Submit()
    is your submit attached to a webservice or direct to the SQL db?

    I do *not* claim to be an expert in this area. It's not a situation I have to deal with.

    But I'm thinking it's too hard to do at the IP side, whereas a WS could handle it.

    Alternatively, two other thoughts:

    1. I think you have a 1-to-0-or-1 (Table1 to Table2) relationship. I assume the correct "model" for this in IP is that Table2 should be an "Optional Section". Have you tried that?

    2. IP allows multiple Submit adapters. Perhaps you need one for Table1 and a different one for Table2? That may "divorce" the update from the insert?

    I don't really know how you're representing your tables & their relationship in IP, which doesn't help...
  • 11-09-2004 06:52 AM In reply to

    is your submit attached to a webservice or direct to the SQL db?
    Web Service

    1. I think you have a 1-to-0-or-1 (Table1 to Table2) relationship. I assume the correct "model" for this in IP is that Table2 should be an "Optional Section". Have you tried that?
    Thoughts: I actually have a stored procedure that gets the contents of one table (TABLE1) and MAY/MAY NOT get the contents of another table (TABLE2), depending upon whether that table (TABLE2) has information within it. When I select my Dropdownlist, the contents of TABLE1 will be populated into IP, but the contents of TABLE2 MAY/MAY NOT be populated depending upon whether there is information within TABLE2, which in turn will bring up the UPDATE or INSERT issue. TABLE1 will never be UPDATED or INSERTED into. TABLE2, on the other hand, can be either, depending upon whether MyID exists or not, if it does, UPDATE, if it doesn't INSERT. As I mentioned before the Optional Section, when I added it, appears to be operating in the same manner as the normal section

    2. IP allows multiple Submit adapters. Perhaps you need one for Table1 and a different one for Table2? That may "divorce" the update from the insert?
    Thoughts: As mentioned above Table1 will neither be INSERTED into nor UPDATED.
    Table2 can be either.


    I don't really know how you're representing your tables & their relationship in IP, which doesn't help...
    Explained above, Web Service calls stored procedure.

    Thanks for all your help...
  • 11-09-2004 09:23 AM In reply to

    • jonbrave
    • Top 200 Contributor
    • Joined on 10-15-2004
    • United Kingdom
    • Posts 59
    I'm trying, but let's be honest, I don't understand your situation clearly.

    I would have *thought*, though, that if you say you're submitting to a webservice then you can code there to recognise your situation and do whatever before handing onto SQL?

    One bit I don't get is you say:
    >>But, when I submit, it's running the UPDATE routine, instead of the >>INSERT routine.
    Who/what? "running"? "routine"? For me, the Submit sends the form as a dataset to my WS, I can do what I like with it there before I issue my SQL commands... I think you're maybe not using dataset/dataadapter for updates to SQL (I am), but aren't you in control of deciding what command to issue...
  • 11-09-2004 11:43 AM In reply to

    The first question would be when InfoPath submits the form, how does it know if it will be an Update or Insert?


    Rambling ---> Please don't say, because you selected the "Insert Item" option on the form. This definately will not be good enough, or maybe it could be if I knew what the name of the flag was so that I could turn it on if MyID is blank (implying an INSERT). ---> End Rambling.

    I've tried to capture it in the Web Service by attempting to run the insert, but since they both do an UPDATE, and pass the dataset, it still sees it as an UPDATE.

    <WebMethod()> Public Function UpdateEmploymentOfferRequest(ByVal myDataSet As DataSet)
    InsertEmploymentOfferRequest(myDataSet)
    'SqlDataAdapter1.Update(myDataSet)
    End Function
    <WebMethod()> Public Function InsertEmploymentOfferRequest(ByVal myDataSet As DataSet)
    SqlDataAdapter1.Update(myDataSet)
    End Function


    I'm to the point where I would like to programmactically call a Web Service to either UPDATE or INSERT. How can this be done in C# or VB.Net.

    My code looks like this:

    If IsNothing(myNode) Then
    'INSERT
    Else
    'UPDATE
    End If

    thisXDocument.Submit()


    I know that the "thisXDocument.Submit()" is misplaced but how would I handle this.


    SOLUTION: Decided to handle this in the Stored Procedure...
    Oracle calls it an Upsert, not sure if SQL has a version of it. I check the primary key in the table. If it exists, it's an UPDATE, if it does not exist, it's an INSERT.
Page 1 of 1 (7 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.