Using the Database Connection Interview Service in an interview routing script

This example demonstrates an interview that is working with an external database. Based on the response to the Name question, the example queries the PeopleDetails table in the PeopleDetailsDB database. If a database entry with a matching name is found, the responses to subsequent questions are pre-populated with database information. If a database entry is not found, a new row is added to the recordset. The example can be used to insert a new entry into the database after the respondent has supplied answers to questions.

' Prepopulate question responses with existing (when data exists)
Set RS = CreateObject("ADODB.Recordset")
Set DBService = IOM.Services["PeopleDetailsDB"]
RS.Open("SELECT * FROM PeopleDetails WHERE Name='" + Name.Response + "'", _
DBService.GetConnection(), 2, 3, 1) ' Dynamic, Optimistic Lock, Text
If RS.EOF Then
  RS.AddNew()
  ' Create a new record that will later be populated
Else
  Country.Response = RS["Country"]
  Phone.Response = RS["Phone"]
  Income.Response = RS["Income"]
End If

Interview routing scripts should generally not store the Connection object, that is returned from IOM.Services["PeopleDetailsDB"].GetConnection(), in a local variable. They should instead call GetConnection() each time the connection is required. This ensures the connection is persistent and available for use.