Pass Infopath data to SQL Server stored procedure

[Tracey]

I'm troubleshooting an Infopath form that was created by a person who is no
with our company. The data is entered by users into the Infopath form, sent
to a SQL Server 2005 stored procedure, and then saved (inserted or updated)
in a table in a SQL Server 2005 database. I've programmed in SQL Server
using stored procedures along with varied interfaces/front-ends, but this is
my first exposure to Infopath, and I'm in kindergarten all over again. I'm
looking at the stored procedure that is used, and I can see there are several
parameters passed in which are user entered into the Infopath form. I can
open the Template.xsn file in design mode, however I cannot see the script
which passes the parameter values from the Infopath form to the stored
procedure. Can someone please point the way for me to look at the code which
passes the user entered values from the Infopath form to the SQL Server
stored procedure? I'm having a bit of trouble getting off the ground with
this. Thanks in advance.

 

[S.Y.M. Wong-A-Ton]

Have you already taken a look at the code via the "Tools > Programming >
Microsoft Script Editor" menu option? Or if the form was built with Visual
Studio open up the solution file with Visual Studio and take a look at the
code there.

 

[Tracey]

I've looked at the code in the Microsoft Script Editor and in Visual Studio.
There's a parameter value, @transactionTypeId, that is set before it's passed
to the stored procedure. To get this value from the user, there's a label on
the Infopath form asking a question. Next to this question are two option
buttons, one for YES and the other for NO. They are both booleans, true or
false, and they are bound to a "myfield" called CPValue. The parameter,
@transactionTypeId, is assigned the value in CPValue and passed into the
stored procedure. What I can't find is how CPValue comes to equal a value of
1 or 2 before it's passed into the stored procedure (depending on which
option button was selected.) For the life of me I cannot find where this
CPValue is set to 1 or 2. The value of @transactionTypeId is not changed
inside the stored procedure -- it is passed in as either a 1 or a 2. I'm
completely new to Infopath, so it could very well be right in front of me.
Thanks for your help.

 

[Tracey]

I didn't mention that the CPValue field is a boolean. It's assigned to
@transactionTypeId which is passed into the stored procedure. There it
updates a field in a table and somehow magically equals a 1 or a 2, not a
true/false or 0/1...

 

[S.Y.M. Wong-A-Ton]

Double-click on the option button to open its Properties dialog box. On the
"Data" tab you'll see a field with the label "Value when selected". The value
that is given in the corresponding textbox behind this label is the value
that will be used for the option button when it is selected.

 

[Tracey]

I looked at the option button Data tabs and I found that the "value when
selected" for each option were booleans, True and False, not 1 or 2 as being
passed into the stored procedure. I looked again at where the parameter
value is set and saw that I had overlooked the answer when I looked at this
earlier. The CPValue (boolean) is passed to a function called
TranslateTransactionType, which returns the integer value 1 or 2 and assigns
it to the parameter, which is then passed to the stored procedure.

params(3) = New SqlParameter("@transactionTypeID",
TranslateTransactionType(loanReview.ReviewInfomation.ComplianceQuestions.ConsumerPurpose.CPValue))

Thanks again for all of your help.