Professional InfoPath 2003 [Electronic resources] نسخه متنی

previous section you saw how to link an InfoPath template to database tables. If you want to use stored procedures instead of tables, you lose some ease of use and have to write a little bit of code.

Consider the scenario where you have to add a new employee to the Northwind database through a simple stored procedure:

CREATE PROCEDURE AddEmployee 
 @LastName nvarchar(20),
 @FirstName nvarchar(10)
AS
 INSERT INTO Employees(LastName, FirstName) VALUES(@LastName, @FirstName)
GO

Then create a simple InfoPath template as shown in Figure 7-7.

Figure 7-7: AddEmployee InfoPath template.

Select the button properties and define the action as Submit. The Submitting forms box appears, as shown in Figure 7-8, and then you enable the submit to custom script option box.

Figure 7-8: Submitting Forms dialog box.

If you click the OK button, the Microsoft Script Editor opens and makes the OnSubmitRequest handler ready to implement. You should now implement the code to execute the stored procedure through ADO:

function XDocument::OnSubmitRequest(eventObj)
{
 var lastName = XDocument.DOM.selectSingleNode("//my:LastName").text;
 var firstName = XDocument.DOM.selectSingleNode("//my:FirstName").text;
 
 try
 {
  var cn = new ActiveXObject("ADODB.Connection");
  var sqlSP = "EXEC AddEmployee ‘" + lastName + "‘, ‘" + firstName + "‘";
  cn.ConnectionString = XDocument.DataObjects("Employees").QueryAdapter.Connection;
  cn.ConnectionTimeout = XDocument.DataObjects("Employees").QueryAdapter.Timeout;
  cn.Open();
  cn.Execute(sqlSP);
  cn.Close();
  eventObj.ReturnStatus = true;
 }
 catch(e)
 {
  XDocument.UI.Alert(e.description);
  eventObj.ReturnStatus = false;
 }
}

First, you have to get the fields value from the document. Then you can create a new ADODB.Connection object and build the SQL statement for a new employees database insert. Next, you get the connection string. Finally, you execute the SQL statement command. If you don’t have errors, you inform InfoPath that the submission was fine; otherwise, you show the error and return false.

Since you created a template from a blank form, you don’t have any DataAdapter available and the sample doesn’t work. You have two choices to make it work:

Create a secondary data source, disabling “Connect to the secondary data source when the form is opened,” as described in Chapter 5.

Create an InfoPath form from a database data source and a fictitious table.

In both cases you can inherit the connection string from the DataAdapter. In the previous sample, we used a secondary data source named “Employees”. If you used the alternative, the code should be as follows:

function XDocument::OnSubmitRequest(eventObj)
{
 var lastName = XDocument.DOM.selectSingleNode("//my:LastName").text; ;
 var firstName = XDocument.DOM.selectSingleNode("//my:FirstName").text;
 
 try
 {
  var cn = new ActiveXObject("ADODB.Connection");
  var sqlSP = "EXEC AddEmployee ‘" + lastName + "‘, ‘" + firstName + "‘";
  cn.ConnectionString = XDocument.QueryAdapter.Connection;
  cn.ConnectionTimeout = XDocument.QueryAdapter.Timeout;
  cn.Open();
  cn.Execute(sqlSP);
  cn.Close();
  eventObj.ReturnStatus = true;
 }
 catch(e)
 {
  XDocument.UI.Alert(e.description);
  eventObj.ReturnStatus = false;
 }
}