Newbie Questions

Re: Executing INSERT Stored Procedure w/ Screen params

Patrick Halstead — Wed, 19 Jul 2006 06:59:35 GMT

Hi BonsaiGuy and sorry we have been so busy lately!
Anyway, this is the reason we created the DBXL Web Service - for people who have already created a form and want to connect to SQL but don't want to rebind their form to use the SQL schema. Take a look at the eval. The beta for version 2 ships in a couple weeks.

Re: Executing INSERT Stored Procedure w/ Screen params

Matt Faus — Tue, 18 Jul 2006 16:08:59 GMT

I'm glad you figured this out! I would like to say that you can add your own fields, even when you connect the form directly to the DB. This new fields come in under a new namespace, and you can bind whatever controls you like to them!

Re: Executing INSERT Stored Procedure w/ Screen params

BonsaiGuy — Mon, 17 Jul 2006 16:07:51 GMT

Hi everyone,

I never got a reply from this one but I ended up figuring it out. The problem was since I didn't create an Infopath form directory linked to SQL server I could only recieve data from the DB not submit. However, if you create a new form directly linked to the database you have VERY limited control over the form because you can only add controls that are directly linked to a field in the database.

So in order to run the INSERT stored procedure I had to create the database connection within the .NET code (using Microsoft's .net plugin thing). What I do now is Open and Close the connection within a button's click event when needed and execute my stored procedure in there.

So that's one work around if you have created a form w/o Submit fuctionality available.

Executing INSERT Stored Procedure w/ Screen params

BonsaiGuy — Wed, 28 Jun 2006 19:34:06 GMT

Hello,

Here is my current scenario.

I have Three Text Fields (not repeating) on the screen that a user enters data into. I have a COMMIT button at the bottom.
Note: All three of these fields are from my MAIN data source, not a secondary, and if possible I would like them to be on the MAIN data source.

In SQL Server I have a Stored Procedure that gets executed by:
execute stp_InsertNewAccountRecord 'Test Account','TAC','6/28/2006'

In the COMMIT button's event handler I have the following code that fills Three String Variables.
-----------------------------------------------------------------
AccountName = thisXDocument.DOM.selectSingleNode("//my:myFields/my:sec_AccountDetails/my:txt_AccountName").text
AccountCode = thisXDocument.DOM.selectSingleNode("//my:myFields/my:sec_AccountDetails/my:txt_AccountCode").text
AccountStartDt = thisXDocument.DOM.selectSingleNode("//my:myFields/my:sec_AccountDetails/my:txt_AccountStartDt").text
-----------------------------------------------------------------

I have seen examples where fields were on a Second Data Source and the Stored Procedure was executed using something like:

-----------------------------------------------------------------
thisXDocument.QueryAdapter.Command = "execute stp_InsertNewAccountRecord " + "'" + AccountName + "','" + AccountCode + "','" + AccountStartDt + "'"

thisXDocument.Query()
-----------------------------------------------------------------

How can I execute the Stored Procedure in my scenario.

Thank you in advance to anyone who can help me, this is my 2nd day in InfoPath!