August 2009 - Posts

• A mutually exclusive radio button in a repeating section

  Here's a nifty trick you can use when you want to add a radio button or checkbox to a repeating section or table that can only be checked in one row of the section or table at any given time. The Scenario You are creating a form for a team roster for teams in a sports tournament.  As a rule, each team may only designate one team captain and one vice-captain.  You could enforce this using custom validation, but let's see if we can't do something a bit fancier. Creating the form We begin by dragging an empty repeating section into the form: By default, this section will be created as my:group2. We then add the fields that we want below my:group2.  We create a my:name field to store each team member's name (this will not serve a real purpose in this demo, but let's include it for good measure), a my:captain field and a my:vice-captain field (let's create both of these fields as Boolean (true/false) fields. Then we drag the fields into the repeating section from the taskpane.  First we drag the name field in and create it as a text box.  Then we drag the captain field with the right mouse button and create it as a checkbox, and drag the vice-captain field with the right mouse button and create it as a radio (option) button.  (Ordinarily you would probably just use one or the other, but for the sake of demonstration we'll use one of each this time. Creating the radio button should create a checked and unchecked radio button with the words Yes and No next to them.  Delete the No radio button and change the "Yes" text to "Vice-captain." The final result should look like this: Adding rules Now that everything's laid out, it's time to add rules to make the fields mutually exclusive.  Right-click the checkbox (the captain field) and select Rules... top open up the Rules dialog box, and then click Add... to add a new rule, and name it "Clear other captains". Click Set condition... to create a condition and set the condition to be captain is equal to TRUE. That is, whenever a user clicks this checkbox to designate a team member as the captain, we want the rule to clear all of the other captain fields.   Now add the rule action.  Make the action "Set a field's value."  Select the captain field itself as the field to set, and for the value, just type false. Please repeat this process for the vice-captain field, replacing captain with vice-captain in the instructions above. Not done yet If you preview the form at this point, you will find that you are unable to check any of the checkboxes or radio buttons. This is to be expected.  As soon as you try to set a value to TRUE, the rule is setting all of the nodes of that field to false, including the field you just checked.  To get around this, we'll need to employ a little trick that Hilary Stoupa blogged here. Editing the manifest Save the form as its source files, close InfoPath and locate the place where you saved the files.  Open the manifest.xsf file in a text editor of your choice. In the xsf:ruleSets section, you should find the definitions for your rules: For the first rule, edit the targetField attribute to have the value: (../preceding-sibling::my:group2 | ../following-sibling::my:group2)/my:captain This will tell InfoPath to set the "false" value to the my:captain field in all preceding and following repeating nodes. Now do the same for the vice-captain rule, replacing my:captain with my:vice-captain. Save the manifest.xsf file, right-click it in Internet Explorer and click Design to open it up in Design mode again. Now preview the form.  If you've done everything right up to this point, you should find that any time you click a captain checkbox, all of the other captain checkboxes become cleared, and the same happens for the vice-captain radio buttons. Posted Aug 05 2009, 11:51 AM by Jimmy with 2 comment(s)

• Executing a stored procedure with OUTPUT parameters

  Many people are aware that you can use InfoPath code or script to perform all sorts of database queries on tables, views, stored procedures and the like.  I won't go into the details here, but you can read more about this at the following links: http://www.infopathdev.com/forums/p/9467/33496.aspx#33496 http://www.infopathdev.com/forums/p/8940/31780.aspx#31780  But what happens when you want to query a stored procedure that uses OUTPUT parameters?  I ran into this issue while helping a forum poster in this thread: http://www.infopathdev.com/forums/t/12506.aspx  Suppose I have this elementary stored procedure, which returns the square and half of the input value: CREATE PROCEDURE [dbo].[SimpleStoredProcedure]      @inputValue int,       @inputSquared bigint output,      @inputHalved int output AS BEGIN      SET NOCOUNT ON;      SET @inputSquared = CAST(@inputValue as bigint) * CAST(@inputValue AS bigint)      SET @inputHalved = @inputValue / 2      -- return some value just for the heck of it      return 7 END If I try to execute this procedure from InfoPath code like this (C# 2007 here): AdoQueryConnection conn = DataConnections["TestDatabase"] as AdoQueryConnection; string originalCommand = conn.Command; string query = "EXECUTE SimpleStoredProcedure 23";  // just pass in 23 as an arbitrary value conn.Command = query; conn.Execute(); conn.Command = originalCommand; RunQuery(query);  I get this error: The query cannot be run for the following DataObject: TestDatabase InfoPath cannot run the specified query. [0x80040E10][Microsoft OLE DB Provider for SQL Server] Procedure or function 'SimpleStoredProcedure' expects parameter '@inputSquared', which was not supplied. this is because my query does not give the stored procedure any place to store the values of its output parameters.  What's more, I have no way of accessing these parameters because by default, InfoPath only receives the value of the return statement from a stored procedure, which would always be 7 in this case. The solution: The thing to remember here is that you don't have to limit yourself to having just one SQL statement in your query.  You can have several.  And that's just what we need here. What we can do is have the query create some SQL variables to hold the output parameters, and then SELECT them so that their values get sent back to InfoPath.  First we create the SQL variables and pass them into the stored procedure: DECLARE @squareOutput bigint, @halfOutput int EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT We can do this by modifying the third line of my code above to:  string query = "DECLARE @squareOutput bigint, @halfOutput int " +     "EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT"; [GOTCHA: Don't forget the space at the end of the first line of the statement or you will wind up with something like intEXECUTE in your statement] This is still incomplete because no values come back from the query, and the secondary data source's contents look like this: <dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"/> To finish this solution off, we need to SELECT the value of the SQL variables so that they will be sent back to InfoPath, like this: SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut I've specified aliases (SquareOut and HalfOut) for the SELECT column names because otherwise they will come back without names and InfoPath will give them meaningless names like c0 and c1. The code looks like this at this point:  string query = "DECLARE @squareOutput bigint, @halfOutput int " +     "EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT " +     "SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut"; [Again, don't forget the space at the end of the second line of the statement] Finally, we run the query, and we get back our result, with our requested values: <dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution">      <dfs:dataFields>           <d:row xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" HalfOut="529" SquareOut="11"/>      </dfs:dataFields> </dfs:myFields> Posted Aug 01 2009, 11:09 AM by Jimmy with 4 comment(s)