Fun with Sequential Form IDs, SQL and Sprocs! - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

Fun with Sequential Form IDs, SQL and Sprocs!

I may be exagerating when I say 'fun'. Forgive me for that.

There are a variety of ways that you can create a unique, human readable form Id. This blog post is for those of you who prefer not to have to write or buy a web service, and don't want to write more than a little code. I'm warning you -- you will have to write some stored procedures. Or, you know, copy / paste them. It sounds scarier than it is, so let's soldier on.

The Problem

You have a form that you want to give an Id field, for whatever reason. You want your Ids to start with a particular number (we are going to start with 435, just because) and for each subsequently saved form to increment by 1. Now, if your form submits to a database, you can just use an auto incrementing Id column and sleep soundly knowing your form will get the next Id when it is submitted. You don't have a problem. But if we have a form that we save in a network folder, or submit to SharePoint, or do something else with ... well, you have a problem.

The Solution

Actually, a solution. There are a lot. This one involves creating a single SQL table to create and track Id numbers, and two stored procedures to help us make sure the right form gets the right Id. While I am going to add this code to the save request in the form, you can add it to another event if you prefer. We will be adding a couple of data connections to the form, so it will need domain trust, but it shouldn't require full trust.

The Steps

The SQL

Create a new table in a SQL database called 'FormIdNumbers' with two columns: GUID, which will be a unique identifier, and ID, which will be an auto incrementing identity column.
Id Table design mode

Next, create two stored procedures. The first, called 'GenerateGUID' simply creates a new GUID we can use to uniquely identify our form while we create and retrieve its Id number:

Create Procedure GenerateGUID
As
Begin
 
      Set NoCount On;
 
      Select NewID() as GUID;
 
End

The second procedure will take a GUID for a parameter, and insert a new row in our FormIdNumbers table:

Create Procedure FormIdInsertRow
      @GUID uniqueidentifier
As
Begin

     Set implicit_transactions off     

     Insert  into FormIdNumbers (GUID)
     Values (@GUID)
End

The Form 

Create a simple form with a field on it that you want to populate with your Id number. I'm just using a text field, so I don't have to fuss about with the xsi:nil attribute. You may be more particular and wish to use an int.

First, we will add a receive data connection that we are going to modify to call our GenerateGUID sproc. Walk through the data connection wizard, and connect to the FormIdNumbers table. When you get to the screen that shows your table and columns:
Data Connection Wizard
click the EditSql button.

Inside the Edit SQL field, type Execute GenerateGUID:
Edit SQL window

Select OK. You'll get a warning, you can select Yes on that:
Tree view warning

As an aside, all that means is you won't see a view of your data structure when the dialog box closes and you are back in the Data Connection Wizard.

When you select Next in the Data Connection Wizard, you'll get another warning:
Sproc Warning

It is true. InfoPath does not know if the query is safe. We do, though. Select Yes again. No need to select the "Store a copy of the data in the form template" checkbox on the next screen. On the last screen, rename the data connection GenerateGUID and decide if you'd like to retrieve the data when the form opens. I'm not going to -- I'm going to run the query from my code. If you run the query on load, you won't need to run it in code, so keep that in mind later....
GenerateGUID data connection

Create another receive data connection in your form to the FormIdNumbers table -- this time, leave the SQL alone, so we have a normal old connection to the table. Deselect the "Automatically retrive data when the form is opened" checkbox, as we won't want the data until we've run our FormIdInsertRow sproc and modified this data connection to include a 'where' clause. Name the connection FormIdNumbers:
FormIdNumbers data connection

The Code 

You need to make an architectural decision at this point. When do you want to generate the Id number? As I mentioned earlier, I plan to generate my Id and populate the Id field in the Save Request event for my form. You may wish to add a button, or put the code in a particular field's Changed event -- it is up to you.

First I have to set my form to use custom code for its save behavior. Go to Tools > Form Options and select the "Save using custom code" checkbox:
Form Options

Click the Edit button to generate the event handler. You may wish to check your programming language before clicking the Edit button to generate the event handler. The following code sample is C# for the InfoPath 2007 Object Model. Replace this line in the FormEvents_Save method:

// Write your code that will run before a Save or Save As operation here.

With this:

            //get the value of the Id node. You'll need to replace "/my:myFields/my:IdNumber" with the XPath to your node
            XPathNavigator idNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:IdNumber", this.NamespaceManager);
            //if not blank, don't bother, we already have an Id
            if (idNode.Value.Equals(""))
            {
                try
                {
 
                    //set up the secondary data sources
                    XPathNavigator domGuid = this.DataSources["GenerateGUID"].CreateNavigator();
                    XPathNavigator domFormId = this.DataSources["FormIdNumbers"].CreateNavigator();
 
                    //set up the connections
                    AdoQueryConnection genGUID = (AdoQueryConnection)DataConnections["GenerateGUID"];
                    AdoQueryConnection formId = (AdoQueryConnection)DataConnections["FormIdNumbers"];
 
                    //query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
                    genGUID.Execute();
 
                    //get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
                    string guid = domGuid.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID", this.NamespaceManager).Value;
 
                    //set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
                    string origGuidCommand = genGUID.Command;
                    genGUID.Command = "Execute FormIdInsertRow '" + guid + "'";
                    genGUID.Execute();
 
                    //get the original command for the FormIdNumbers data connection
                    string origFormIdCommand = formId.Command;
 
                    //add the 'where' clause
                    formId.Command = origFormIdCommand + " where GUID = '" + guid + "'";
                    //execute the query
                    formId.Execute();
 
                    //set the commands back to their original states
                    genGUID.Command = origGuidCommand;
                    formId.Command = origFormIdCommand;
 
                    //set the field to the ID
                    idNode.SetValue(domFormId.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID", this.NamespaceManager).Value);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }

Of course, if you changed the names of the data connections, stored procedures, or anything else as you walked through this, you will need to modify the code to address those changes.

If you are using InfoPath 2003, here is a JScript version:

      //get the value of the ID node. If blank, skip
      var idNode = XDocument.DOM.selectSingleNode("/my:myFields/my:IdNumber");
      if (idNode.text == "")
      {
            try
            {
     
                  var domGuid = null;
                  var domFormId = null;
                 
                  //set up the secondary DOMs
                  domGuid = XDocument.GetDOM("GenerateGUID");
                  domGuid.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
 
                  domFormId = XDocument.GetDOM("FormIdNumbers");
                  domFormId.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
                 
                  //get the data objects 
                  var genGUID = XDocument.DataObjects("GenerateGUID");
                  var formId = XDocument.DataObjects("FormIdNumbers");
                 
                  //query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
                  genGUID.Query();
                 
                  //get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
                  var guid = domGuid.selectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID").text;
 
                  //set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
                  var origGuidCommand = genGUID.QueryAdapter.Command;
                  genGUID.QueryAdapter.Command = "Execute FormIdInsertRow '" + guid + "'";
                  genGUID.Query();
 
                  //get the original command
                  var origFormIdCommand = formId.QueryAdapter.Command;
 
                  //add the 'where' clause
                  formId.QueryAdapter.Command = origFormIdCommand + ' where "GUID" = ' + "'" + guid + "'";
                  //execute the query
                  formId.Query();
 
                  //set the commands back to their original states
                  genGUID.QueryAdapter.Command = origGuidCommand;
                  formId.QueryAdapter.Command = origFormIdCommand;
 
                  //set the field to the ID
                  idNode.text = domFormId.selectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID").text;
            }
            catch (err)
            {
                  XDocument.UI.Alert(err.description);
            }
      }

And now....

The Results

Preview your form to test it. When I save my form, since this is the first time my form will connect to the data sources I've created, I get a warning:
Data Connection warning

If I click Yes, my Id field will be populated before the Save Location dialog opens:
PopulatedId

Addendum 

Those of you who are stored procedure savvy will recognize that if the FormIdInsertRow sproc contained a 'Select' we could return our result set with that instead of running another query.

I ran into an exciting InfoPath feature when it comes to auto incrementing identity columns. When InfoPath uses the data connection to create the schema, it rolls back the transaction, but not the identity. This means that if I used a sproc that added my new row and returned the results so I would have my new Id number for my form, I would effectively get every other Id number. This thread on a different site describes this issue a bit further.

Because of the double call issue, the solution above executes a sproc that takes a GUID as a parameter, then uses the same GUID to retrieve a row from our data table to get the ID number associated with that GUID.

Originally, this blog post had an unbalanced 'Commit' statement in the suggested sproc. That was because when I modified an InfoPath receive data connection command to execute a sproc with a parameter, it only worked if the unbalanced commit was there. Jimmy Rishe saved the day (again) and found out that if we set implicit_transactions to off in our stored procedures, everything runs like NASCAR.

Comments

 

Jess573 said:

This is a very good example of how to generate a unique ID.  I was able to use it with only one tweak.  (I had to include "tbl" in front of my table references.  RIght now, I have the form ID being generated when the form opens; however, I'd like this to happen when the submit button is clicked.  The only problem is that the Submit Rules occur before the code executes.  Any ideas on how to fix this?

August 24, 2009 3:08 PM
 

Hilary Stoupa said:

Rules always run before code. There are some blog posts out there on how to call a code function from a rule that you could try... or you can handle your submit in code as well.

September 27, 2009 6:32 AM
 

giangy11 said:

there is a problem:

i'm following the guide you give me, in a passage she says:

"Go to Tools > Form Options and select the "Save using custom code" checkbox"

I can't check that checkbox because it's not browser compatible...i need the infopath forms open with browser...what can i do?

October 12, 2009 12:33 AM
 

Hilary Stoupa said:

Yes, that is a limitation of browser forms. Perhaps you could use Submit instead of Save? I believe you can use custom code for Submit in a browser form.

October 13, 2009 8:00 AM
 

stuartward said:

This was exactly what I was looking for. Thanks for posting a detailed description of how to create incrementing form IDs.

Thanks, Stuart

September 3, 2010 8:40 PM
 

daniloraf said:

Hi Hillary,

I followed the post exactly, but still getting an error message when I try to save the form; perhaps you could point me to what i´m doing wrong or a solution?

Infopath tells me:

System.NullReferenceException

Object reference not set to an instance of an object.

  at OrdemRetirada.FormCode.FormEvents_Save(Object sender, SaveEventArgs e)

  at Microsoft.Office.InfoPath.Internal.FormEventsHost.OnSaveRequest(SaveEvent pEvent)

  at Microsoft.Office.Interop.InfoPath.SemiTrust._XDocumentEventSink2_SinkHelper.OnSaveRequest(SaveEvent pEvent)

Using Infopath 2007, moss 2007, sql 2005

I avoid code, since i don´t have the c# skill (usually copy-paste)

Thanks!

Dan, from Brazil

September 12, 2010 6:38 PM
 

daniloraf said:

got it working; it was a syntax issue on the xpath .

now im getting the error: "schema-validation-found-non-data-type-errors/" on save; looking it up i found it to be something to do with xsi:null attribute..  any hints?

September 12, 2010 6:48 PM
 

daniloraf said:

got it working by changing the data type on the formid field.. i had left it as integer, and when i switched back to text, it worked fine.  but, will it submit properly to a webservice that connects to a sql server and has to insert this id to a integer column in sql table?

September 12, 2010 6:51 PM
 

daniloraf said:

sorry for all the replies

in case later i have trouble submiting the formid as text to an integer sql column (due to the xsi:nil), i have checked out this post, and it seems to be a solution, in case somone else needs it.

blogs.msdn.com/.../the-xsi-nil-attribute.aspx

Again, Thanks Hilary!

September 12, 2010 7:06 PM
 

rimonabantexcellence site title said:

Pingback from  rimonabantexcellence site title

June 13, 2013 11:09 PM
 

Sapnesh sharma said:

Hi Hilary,

I am sure this post has been locked and finished as it was opened in 2009 however I still find it very useful and trying to incorporate steps being advised. I have created the Stored procs. however when executing "GenerateGUID" it returns GUIDE as below:

GUID

3107A100-F109-4668-973F-3692B67D15BD

However Return Value is coming as '0' (Zero) hence when executing stroed proc. "FormIdInsertRow" getting error stating:

'FormIdInsertRow' expects parameter '@GUID', which was not supplied.

Can you please advise (If possible) if there is something I have missed.

March 14, 2016 7:17 PM

About Hilary Stoupa

I wandered into development after working as a business process analyst for a global manufacturing company. I create InfoPath solutions for our clients as well as work as a developer on company tools that extend InfoPath. I've also been instrumental in creating the InfoPath Master Class training provided by Qdabra.

Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.