How to work with stored procedures by using script in an InfoPath form

The top-level XDocument object for InfoPath exposes the QueryAdapter property. The QueryAdapter property returns a reference to the data adapter object that is associated with an InfoPath form. At run time, you can use the QueryAdapter property to access the primary data source of your form and to then run commands. You can use the QueryAdapter property to run a stored procedure that returns records or submits records on your form. This is described in the following sections.

Design a form that uses a stored procedure to query data

Create a new form from a data source

1. Start InfoPath.
2. In InfoPath 2003, follow these steps:
   1. On the File menu, click Design a Form.
   2. In the Design a Form task pane, click New from Data Source.
      
      The Data Source Setup Wizard starts.
   3. Choose Database for the data source, and then click Next.
   4. Click Select Database.
      
      The Select Data Source dialog box appears.
   5. Click New Source.
      
      The Data Connection Wizard appears.
   In InfoPath 2007, follow these steps:
   1. On the File menu, click Design a Form Template.
   2. In the Design a Form Template window, click Blank, and then click OK.
   3. On the Tools menu, click Data Connections, and then click Add.
      
      The Data Connection Wizard appears.
   4. Click to select Create a new connection to, click to select Receive data, and then click Next.
3. In InfoPath 2003, click Microsoft SQL Server, and then click Next.
   
   In InfoPath 2007, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
4. Provide your server name and your logon credentials, and then click Next.
5. Choose the Pubs database, choose the titleauthor table, and then click Next.
6. Click Finish to complete the Data Connection Wizard.
   
   You are returned to the Select Data Source dialog box.
7. In the Select Data Source dialog box, click Open.
   
   You are returned to the Data Source Setup Wizard.
8. Click Next.
9. Select Design data view first, and then click Finish to complete the Data Source Setup Wizard.

Design the query for your form

1. In the Data Source task pane, right-click myFields, and then click Add.
   
   The Add Field or Group dialog box appears.
2. Name the new field myPercentage, and then click OK.
3. Move the myPercentage field to your form.
4. In the Controls task pane, double-click the Button control to insert a button.
5. In the Layout task pane, double-click Two-Column Table to add a table to your form.
6. In the Data Source task pane, click dataFields, and then click d:titleauthor.
7. Move :au_id to the first column of your table. Move :title_id to the second column of your table.
8. Right-click the button on your form, and then click Button Properties.
   
   The Button Properties dialog box appears.
9. In the Label box, type Query, and then click Apply.
10. In InfoPath 2003, click Microsoft Script Editor.
    
    In InfoPath 2007, click Edit Form Code.
    
    Microsoft Script Editor opens.
11. Add the following code to the OnClick event handler for your query button:

    //Retrieve the value of the field that you want to use as a parameter
    //to the stored procedure.
    var percentageValue = XDocument.DOM.selectSingleNode("/dfs:myFields/my:myPercentage").text;
     
    //Set the Command for the Query Adapter of the Data Source. Incorporate the 
    //parameter values that you want to use. 
    XDocument.QueryAdapter.Command = 'execute "dbo"."byroyalty" ' + percentageValue; 
     
    //Query the Data Source. 
    XDocument.Query();

12. Save your form template.

Test the query function for the form

1. In InfoPath 2003, click Preview Form.
   
   In InfoPath 2007, click Preview, and then click Form.
2. Type 100 in the My Percentage box, and then click the query button.
   
   The records that match the royalty percentage of 100% appear.
3. Repeat the previous step with other percentage amounts.
   
   For example, you can try 25, 50, and 75 to find the records that match the royalty percentage for 25%, 50%, and 75%.

Design a form that uses a stored procedure to submit data

Create a new form from a Data Source

Follow the steps in the Design a form that uses a store procedure to query data section to create a new form.

Create the stored procedure

1. Start SQL Query Analyzer.
2. Provide your server name and your logon credentials, and then click OK.
3. Execute the following SQL statement:

   use pubs
   GO
   sp_helpconstraint titleauthor
   GO

   The constraints information for table titleauthor appears.
4. Execute the following SQL statement:

   ALTER TABLE titleauthor DROP CONSTRAINT ForeignKeyConstraints

   
   Note ForeignKeyConstraints is the constraint for FOREIGN KEY constraint definition that you obtain in step3.
5. Create the addnewauthor stored procedure by using the following SQL statement:

   create PROCEDURE addnewauthor @id varchar(11),@title varchar(6),@order tinyint,@royaltyper int
   AS
   SET IMPLICIT_TRANSACTIONS off
   INSERT INTO titleauthor values (@id,@title,@order,@royaltyper)

Design the Submit function for your form

1. In the Controls task pane, double-click the Button control to insert a button for executing the stored procedure.
   
2. In the Data Source task pane, right-click d:titleauthor, and then click More.
   
3. In the Select a Control box, click Controls in Layout Table, and then click OK.
   
4. Right-click the button on your form, and then click Button Properties.
   
   The Button Properties dialog box appears.
   
5. In the Label box, type Submit, and then click Apply.
6. In InfoPath 2003, click Microsoft Script Editor.
   
   In InfoPath 2007, click Edit Form Code.
   
   Microsoft Script Editor opens.
7. Add the following code to the OnClick event handler for your submit button:

   //Retrieve the value of the field that you want to use as a parameter
   //to the stored procedure.
   var id = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:titleauthor/@au_id").text;
   var title = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:titleauthor/@title_id").text;
   var order = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:titleauthor/@au_ord").text;
   var royaltyper = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:titleauthor/@royaltyper").text;
   
   //Set the Command for the Query Adapter of the Data Source. Incorporate the 
   //parameter values that you want to use. 
   XDocument.QueryAdapter.Command = "execute addnewauthor " + "'" + id + "',"+"'"+title+"',"+"'"+order+"',"+"'"+royaltyper+"'";
   
   //Query the Data Source. 
   XDocument.Query(); 
   

8. Save your form template.

Test the Submit function for the form

1. Type 172-32-1172 in the Au Id box, type BU1031 in the Title Id box, type 2 in the Au Ord box, and type 120 in the Royaltyper box.
2. Click the submit button.
3. In SQL Query Analyzer, execute the following SQL statement:

   select * from titleauthor where au_id = '172-32-1172'

   The data is inserted into the titleauthor table.