Designing a browser-enabled InfoPath form that can store data in a database - Part3

Here's the last part of this blog post.

We will finish creating the form and we will test it.

 

Now create another section named DiscountData, insert a table with title and another table with three columns and four rows as follows:

 

startDate and endDate are date pickers controls, discount rate is a textbox.

This section will contain the discount data that will be stored to the database via our web service.

To make this form more "robust" you could set the visibility of this section to depend on the shopID value: in the Display properties of the discountData section click on conditional formatting and add a rule that hides this section if shopID field is blank.

To send data to our web service, we will configure the “Save!” button to do our job but first we need to create the data connection and store it to the data connection library in SharePoint.

Now in the data source section choose manage data connections and add a data connection. The Data Connection Wizard will start: choose “Create a new connection to”->“Submit data” then click “Next”. Select “To a Web Service” and then click “Next”. Now insert the location of the web service: https://youservername/sql?wsdl and then select the name of the webservice (in our case StoreDiscountData) and click Next. Now you will be able to bind the required parameters to the controls in our InfoPath form: s0:shopID to our shopID textbox and so on (see below).

Now click Next, give a name to this new connection, click on Finish, and OK until you will close all open dialogs. Now, like we did for the other data connection save this data connection to our data connection library, delete the one in the form and re-create it adding it from the sharepoint data connection (you will have to re-bind the parameters to the ones in our form).

Now double click on the button "Save!" and add a rule with an action to submit using our newly created data connection. 

Back to the Rule window: we want the form to be closed after the data is stored therefore choose: Add Action, and select Close this form (no prompt will be automatically selected as we are using Form Services forms). Click OK until you close all windows.

We also need to save this form in a SharePoint document library in order to make it editable with a browser using Form Services.

If you haven’t saved the form so far, then it’s time save the form template locally.

Now select File->Publish and choose “To a SharePoint server…” and click Next. Insert the URL of your SharePoint and click Next. In the next step check the “Enable this form to be filled out by using a browser”, select Document Library and click Next. Select Create a new document library and click Next. Give the document Library a name (like “Discount Forms”) click Next and select the columns you will want to promote as Document Library columns (i.e. you will see the data in each form exposed as columns in the newly created document library). Click Next and click Publish. If everything is OK you will be noticed your form has been successfully published to your SharePoint. 

We are not done yet: we also want to specify the form name therefore we will add another rule to our Save button: double click on the button, click on Rules->Modify->Add Action. Select submit using a data connection and select Add. The data connection wizard will start again, and again we will choose Create a new connection to Submit data and then click Next. Choose “to a document library on a SharePoint site” and click Next. Now insert the URL of the document library where we have just published our form and edit the rule that will generate the name of our form – in my case: concat(shopID, "_", concat(startDate, "_", concat(endDate, "_", discountRate))). Click Next and Finish. Click OK until all windows will close.

Save the template and re-publish the template to our document library (it should be just a Next-Next, this time choose “update the form template”).

One final step: in case you want the form to only e filled out with a browser:

Go to the Document Library where we published our form template. Select Settings->Form Library Settings and click on advanced settings: click on “display as a web page” and click OK. Now every time you will click on “New” the form will open directly in the browser.

Now we are

Now we are lef with testing the form.

Go to back to your document library and click on New. the form should open in the browser.

Insert some data and click on the Save button: the form is saved to SharePoint and the discounts are saved in our Discount table in SQL Server.

Comments

• Anonymous
  January 01, 2003
  sryap: have you double-checked the definition of the endpoint? have you tested it works correctly by typing in your browser the following: http://SQLservername/sql?wsdl

• Anonymous
  January 01, 2003
  Great post, explained really well and I could really understand. Thank you.

• Anonymous
  January 01, 2003
  Followed the instruction but when I have to create a new connection using the the web service I get this errors: WSDLPort:Port discount_endpoint has no operations HRESULT=0x1: Incorrect function.

• WSDLService:Processing service discount_endpoint found no port definitions HRESULT=0x80070057: The parameter is incorrect.
• WSDLReader:Analyzing the WSDL file failed HRESULT=0x80070057: The parameter is incorrect.

• Anonymous
  January 01, 2003
  PingBack from http://eddy.supervidsdigest.info/sharepointformsnoinfopath.html

• Anonymous
  January 01, 2003
  No code at all! that's the nice part of the job! the logic is done at form design time via wizard interfaces in InfoPath. :)

• Anonymous
  November 03, 2008
  please spacify how to collect data through web forms at run time the code please

• Anonymous
  May 12, 2009
  Excelente Tutorial, the best and easy that i find in  web. Thanks, you are a pro!

• Anonymous
  May 13, 2009
  All work properly, but i use a SQL server out of the Active Directory, what can i do? I need to authenticate with the SQL server. Thanks for help.

• Anonymous
  May 13, 2009
  Hi, i check the UDC v2.0 Schema Reference (http://msdn.microsoft.com/en-us/library/ms772017.aspx) and edit manually the udcx file in sharepoint conection list, and add this code for authentication: <udc:Authentication>  <udc:UseExplicit CredentialType="NTLM">  <udc:UserId>login_in_sql_server_machine</udc:UserId>  <udc:Password>password</udc:Password>  </udc:UseExplicit>  </udc:Authentication> I test before, using CredentialType="SQL", that is configurated in my server, but dont work, instead, i use NTLM with my normal user and password in server and work properly!

• Anonymous
  June 04, 2009
  This works great as long as I use the form on Infopath, when i publish the form to a Sharepoint doc library I can no longer submit or receive data unless I am opening the browser form on the server that runs the Sharepoint site? I'm using domain trust and the sharepoint logs have this error: "Client, LoginFailure, AccessDenied  SOAP-1_2-ENV:Sendersqlsoapfaultcode:LoginFailuresqlsoapfaultcode:AccessDeniedThere was an error in the incoming SOAP request packet:" Help! Anyone know how to solve this problem?

• Anonymous
  August 12, 2009
  Thank you very much for this, I used parts of this to help me understand how infopath works and it helped a lot, thx again!

• Anonymous
  December 20, 2011
  It would be very helpful to see the images. Very well done. Thanks

• Anonymous
  February 08, 2012
  Thank you in advance for your wonderful post, eterenzi. I did exactly as you said, and everything goes on well, except when I try to submit a form using browser. Then it shows an error message like this: "The form cannot be submitted because of an error." Recieve connectoion woeks fine, also the submit connection works in infopath filler with no problem, and a record is added to the DISCOUNTS table. but in the browser, NO! I'm really getting mixed up on this.

• Anonymous
  March 09, 2012
  are you using SQL authentication or Windows Authentication?