Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Data Entry for SQL Server - building quick, efficient data input forms using InfoPath

By:   |   Read Comments (12)   |   Related Tips: More > Application Development

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Most requests sent to the DBA are for Database Administration issues. Sometimes, however, something out of the ordinary comes along - in my case, a request for the design of some user input forms to be connected to SQL Server. Assuming that the solution is to be built without code, what options are available?


This article introduces Microsoft InfoPath, an application that is part of Microsoft Office Professional Plus. It is used to design and share forms between user groups and integrates fully with SharePoint and SQL Server together with being able to produce standalone forms with zero code exposed to the user and no knowledge of SQL required. This article will also guide you through creating an interactive form connected to SQL Server, a template that could be used to produce more sophisticated solutions.

Introduction to Microsoft InfoPath

In the course of my work for clients, I often get called upon to diagnose SQL Server issues; a poorly-performing query, unusual error message, an alert from the monitoring tools or some other database administration problem. Occasionally, I get asked to implement a feature - for example, setting up monitoring, replication or log shipping, or reviewing the backup strategy and making some changes. There's the day-to-day DBA work, reviewing and approving (or rejecting) requests from development teams, pushing changes into production and so on. In my role as a contractor, I have a huge variety of requests that are normally fairly predictable and most fall within my field of expertise.

Recently, however, I had a request to provide a data input mechanism for SQL Server for the use of non-technical users. This stopped me in my tracks; this isn't a DBA activity, nor something that a regular BI developer would do. I mean, SQL Server is for applications, right? It's a middleware layer, not something your everyday John Q. User would need to know about, let alone interact with?

Now you may be thinking what I'm thinking - Microsoft Excel. It's a straightforward, no-nonsense approach using a common framework to SQL Server (.NET) with built-in support for native SQL Server connections to local or remote servers. Once I gathered a little more information, I realized this wasn't to be the solution - this system both needed to be centralized (via SharePoint), easy-to-use via a web browser with an uncluttered interface, ScreenTips (help on mouse-over), options to add client-side validation, server-side validation in the future, ensure supportability (so no obscure VBA code) - oh, and with great aesthetics too. Excel ticks maybe half of these boxes.

O...kay. So now I'm beginning to panic - when I do play with software development outside the context of the BI stack, it's normally in Python, TKinter, R, Matlab and other assorted tooling to work on projects completely unrelated to databases (I don't dream in SQL!). I've never seriously used .NET in anger beyond VBScript and PowerShell, and I'm certainly not up to the task of producing a solid and robust application within a web interface, not on my own or in the short timescale I was given.

So, what to do? Clearly I needed some other form-creating tool ... SharePoint has some basics, but nothing that met all the criteria above. Here's a great opportunity then to introduce Microsoft InfoPath. In this tip, I'm using Microsoft InfoPath 2013 (part of the Microsoft Office Professional Plus package) with SQL Server 2008 Standard Edition.

Some preparation - in this demonstration, I will be reading and writing data to and from the same table, dbo.Person, and also reading data from table dbo.lookup_animals in database 'msst_test'. You can find an object creation script here if you would like to follow along.


Run this on your SQL Server installation (not production!) to follow along with the tip. Make sure you have permission to do this from your DBA first.


USE msst_test 

CREATE TABLE dbo.Person ( 
	FirstName VARCHAR(100), 
	Surname VARCHAR(100), 
	DateOfBirth DATE, 
	CurrentAge AS DATEDIFF(YEAR, DateOfBirth, GETDATE()), 
	AnimalPreference1 INT, 
	AnimalPreference2 INT, 
	AnimalPreference3 INT, 

CREATE TABLE dbo.lookup_Animals ( 
	AnimalName VARCHAR(100) ) 

INSERT INTO dbo.lookup_Animals 
	SELECT 1, 'Elephant' UNION ALL 
	SELECT 5, 'Rabbit'

Introducing InfoPath

Wikipedia sums up InfoPath far better than I can: "Microsoft InfoPath is a software application for designing, distributing, filling and submitting electronic forms containing structured data".

So let's dive in and take a look at how to create a basic form, and connect this form up to a SQL Server database.

Microsoft InfoPath Blank Form

When we open InfoPath Designer 2013, we are presented with a choice of forms to use. Select New -> Advanced Form Templates -> Database to open a new database-compatible form. You'll immediately recognize the form layout as similar to PowerPoint - the title area is clearly displayed, and there's an area to add tables below.

What's new is the 'Fields' section to the right of the screen. Here is where references to the fields we are using are stored, and underneath you'll see 'Manage Data Connections...' - this is where Data Connections (the BI equivalent of Connection Managers) are stored.

Configuring the Main Database Connection

So, first thing - let's create a new Data Connection to our SQL Server database. My database is called 'msst_test' and stores customer information. When you create the form, the Data Connections Wizard will be displayed automatically. Begin by clicking 'Select Database', then 'New Source':

Configuring the Main Database Connection in InfoPath 2013

Now select SQL Server and click Next. Enter the server name and login credentials if needed.

Specify the SQL Server Name and Log on Credentials

Now select the msst_test database from the drop-down menu. Two tables will be displayed, Person and lookup_Animals, both in the dbo schema. Select 'Person' and make sure the Connect to a Specific Table checkbox is filled:

Select the Database and Tables in InfoPath 2013

Now in the resulting box, you can save the connection as a data connection file. Check with your DBA first before saving this file as unauthorized access to these files can mean the breach of your login credentials. Give the connection a friendly name - mine is called 'Test Connection'.

Save Data Connection File and Finish with InfoPath 2013

Now click Finish, Next and Finish. Ignore the warning about web browser form incompatibility, we will change the type of form now.

Click File -> Advanced Form Options. Click the Compatibility tab and in the 'Form type' drop-down box, select InfoPath Filler form. Click OK to close the window.

Specify the Form Type in InfoPath 2013

Now click Manage Data Connections in the bottom-right of the form window, under 'Fields'. You'll see the connection listed there - click Modify... then Next. In this window, click the 'Enable submit for this connection' checkbox to fill it, then Finish and Close.

Manage Data Connections in InfoPath 2013

Finally, delete every element in the form by clicking on it and pressing 'Delete'. You should be left with a completely blank form. Save this form.

Designing the InfoPath 2013 Form

We now have a basic working connection to SQL Server and a blank form to play with. Let's begin by adding a title. In the same manner as you would in Word, create a title by typing near the top of the page. Play with the size and font until you are happy with the result. Here is mine, quite plain:

Designing the InfoPath 2013 Form

Now we need to use Controls to fetch the data from the database. Controls are dynamic objects in InfoPath that need to be bound. Each control is bound to one field. Thus, one control is (in our example) necessary for each field we want to display.

Let's create a Section first. This Section is a section of form that will contain controls, and that will display data from a record (row) in the database table. Click the 'Section' button in the 'Controls' section of the Home tab in the Ribbon. In the resulting window, we need to select the binding. Select 'Main -> myFields -> dataFields' and click OK. This means that we are binding the section to all columns under 'dataFields' (i.e. the whole Person table):

Section Binding for each field in InfoPath 2013

The Section will show up on your form. Resize the section to suit your form (I expanded mine down the page to make room for more Controls). You can see the binding on the top-right of the Control:

Resize the section for your form

Now, let's add the columnar data to the Section. For our example, we only want to show one record at a time - remember, it has to be user-friendly, so dumping the data out as a table is probably not a good idea! We'll need labels for each field too, and we'll add ScreenTips to indicate what is required. First, let's add a Text Box. Click the Text Box control in the Controls section of the Ribbon and select the 'FirstName' column under dataFields -> Person, then click OK. You'll get the following warning message:

Warning message for non repeating controls in InfoPath 2013

This warning message can be ignored for now - it is explaining that because the container you have chosen (Section) is non-repeating, only the first record in the set (the first row) will be shown. This is logical - if we're only making room for one record, it can only show one record! We will counter this later by adding interactive buttons so ignore it for now. After you click Yes, the control will appear in the Section.

Now repeat this process for the columns Surname, CurrentAge and LastUpdated. Feel free to experiment with the layout and font tools which are much like Word. Tip: If you're struggling to achieve a neat layout, use a table to fix indentation problems and arrange your fields.

Here is my example - yours will almost certainly vary:

Text box properties in InfoPath 2013

You'll notice I've left a gap between Surname and Current Age. Here, I'm going to add a different sort of Control - a Date Picker. Position the cursor where you'd like your Date Picker to go and click the Date Picker control from the Controls section of the Ribbon.

Now, if you reviewed the preparatory script referenced at the beginning of this tip, you may have noticed that the 'Current Age' column is computed as a function of Date of Birth and the current date/time. It follows, then that we do not want this field to be editable by the user - it should automatically populate, based on the entry in Date of Birth. Right-click on the field and click Text Box Properties, then click the Display... tab. Fill the 'Read-only' checkbox to set this field to read-only.

Incidentally, this is where you would set your ScreenTips (mouse-over text) - simply populate the ScreenTip field in this dialog box.

Labels and fields added to the InfoPath 2013 form

Adding More Data Sources to the InfoPath 2013 Form

Now, we need to add the remaining columnar data to our form. We're not interested in displaying the PersonID as this is only for internal use, but we would like to display data on each person's favorite animals. These are represented in columns AnimalPreference1, AnimalPreference2 and AnimalPreference3. However, the data in this columns is of type INT, and we can guess (although there is no explicit key defined - we could add a foreign key without difficulty) that these will correspond to the AnimalID for each animal listed in table lookup_animals.

So, we have a few things to do:

  1. Add the lookup_animals table to the form as a Secondary Data Connection
  2. Create some drop-down lists for these preferences
  3. Make sure the drop down lists show the animals, not the integers
  4. Make sure the value fetched/submitted is the integer, not the animal

To add in the second table, let's click on Manage Data Connections and Add... Then click 'Receive Data', Next, and select 'Database (Microsoft SQL Server only)'. By now you should have this window displayed:

Data Connection Wizard for the Animal Data in InfoPath 2013

Now, in the next Data Connection Wizard window, you can select the data connection file you want to use. As our connection remains the same (only the table varies), select the 'Test Connection' file and click OK. The Person table will populate automatically - click the 'Remove Table' button and 'Add Table' button, then select 'lookup_Animals'. You should be left with the following window:

Animals Lookup for the field selection in InfoPath 2013

Click Next, Next, Finish and Close. Now, if you expand 'QueryFields' in the Fields dialog on the right side of the screen, you'll see the lookup_Animals table listed in there. As a set of query fields only, it means the data in this table is never updated or added to by our form - it is only referenced (SELECTed by the form) where needed.

Build Drop Down Lists in InfoPath 2013

Let's now address 2) above and create some drop-down lists to show the animals. Use the 'Drop Down List' control from the Controls section of the Ribbon. BE CAREFUL - when setting the binding, make sure you bind to the dataFields -> Person -> AnimalPreference fields, not the AnimalID or AnimalName query fields we just created! We need to tell the form that we are *storing* the result to the AnimalPreference field, not retrieving it from there.

Solving 3) and 4) above is a bit tricky. Once you've placed your first drop-down, right-click on it and go to 'Drop Down List Box Properties'. In here, under List Box Choices, you'll see three radio buttons enabling you to specify the source for the display values in the drop down box. Now we don't want the user to have to choose between a set of integers instead of animals, but we do want to populate the table with integers (as it saves space!) that are keyed to the lookup_Animals table. Here is where we can specify a different set of lookup values.

Click 'Get Choices from an External Data Source'. Select 'lookup_Animals' for the Data Source, then for Entries navigate to the QueryFields -> lookup_Animals table. In Value, select '@AnimalID' and in Display Name, select '@AnimalName'. The result should look like the following:

InfoPath 2013 Drop-Down List Box Properties

Repeat the process for the AnimalPreference2 and AnimalPreference3 fields.

Adding Buttons to the InfoPath 2013 Form

Now, let's put some buttons on the form. We need to be able to do four separate things in our example - scroll between records (left and right) based on the PersonID primary key, submit our changes to existing records, and create new records.

Use the 'Button' control in the Controls section and place four buttons on your form. It doesn't matter whether these are inside or outside your section. Use the right-click -> Button Properties... option to rename them, and place them appropriately.

Now InfoPath doesn't come with much 'out-of-the-box' support for buttons - you can manage them using either Rules (which can either Validate, Format or perform an Action) or VB code. For the purposes of this example, we've committed to a codeless solution, and there aren't any Rules which allow us to traverse records easily. So we'll have to be creative and put a Rule in place with multiple Actions that enable us to do this.

At this point, our form should have some fields representing our columnar data and four buttons. Here are mine:

Work in progress InfoPath 2013 Form

Adding Interactive Controls in InfoPath 2013

Remember at the beginning of the form design we received a warning about using the Section control? Well, we now need to put in some components to make sure that we can retrieve a specific record when called. By retrieving a specific record, we can then move on to traverse records and create new records.

Let's put in a new Text Box somewhere in the form which will contain the PersonID. It doesn't matter where we put it since in reality, we'd make it invisible to the user.

Mine's here:

@PersonID for Traverse the Records

Don't bind the field just yet - let it be, we'll come back to it in a moment.

Now, we need to put some work in so we can create a button that creates a new record. InfoPath does have functionality for this built into the Rules - close a form, or open a new form to fill out. Neither is what we want - we need to be able to skip to the last record in the set, then increment the PersonID ready for the details to be filled out and submitted. Because InfoPath isn't great at supporting SQL querying (unless you want to write it in code, or do some inline SQL querying) we can shoehorn in our own SQL using a view, which at least can be version-controlled and reside database-side.

Create two views in SQL Server like so:

USE msst_test

CREATE VIEW LatestRecord AS 
	SELECT MAX(PersonID) [MaxPersonID] FROM dbo.Person 

	SELECT PersonID FROM dbo.Person 

Now we need to use the output of one of these views to bind to the new PersonID field we created. Hit 'Manage Data Connections' again and this time add a new data connection to 'Receive' (not submit) data. Select the same server connection, and click Modify... to change the table. You'll see the new view names in the list. Select the 'AllRecords' view and click OK, then OK to close the window.

On the PersonID field, right-click and Change Binding... Under the QueryFields tree, select the PersonID column (this is the view output column). Click OK.

What we've just achieved is to tell InfoPath that we want the PersonID field to display the output of the view. Whatever number happens to be in the field will also be used to populate all the other fields on the form (as it is of type 'QueryField' not 'DataField').

Let's pause here. This is a really tricky concept and worth going over again. The QueryFields list in the Fields window is split into two trees, the QueryFields tree and the DataFields tree. The general rule is that any fields on the form bound to a Query field is the value the form will use to populate any other fields on the form. Any form bound to a Data field will be updated when the form is submitted. This holds true for any *primary* connections (i.e. enabled for submission) but not for secondary connections. However, if any InfoPath gurus are reading this and disagree with my summary, please leave a comment and I'll be happy to stand corrected!

Now we have done the groundwork, let's start with setting up the Previous Record button. Click once on the button to highlight it then in the Ribbon, on the right under the Rules section click Manage Rules.

Defining InfoPath 2013 Rules for a Button

Now in the Rules pane on the right side of the screen, click the Add button and select 'Set a field's value'. Once selected (see screenshot below), in the dialog box that appears, use the drop-down under Field to select the AllRecords secondary data source and set Field as @PersonID (from QueryField) and Value as @PersonID - 1. This means that when the button is clicked, the value of the PersonID field (the field we just created) will decrease by 1.

InfoPath 2013 Rule Details

Now click Add again and select 'Query for Data'. In the resulting window set the Data Connection as 'Main connection' and click OK. The button should have two rules.

Do the same for the Next Record button. First, add the rule to increment the PersonID field by 1 (@PersonID + 1) and make sure the PersonID is from the AllRecords secondary data source, or it won't work. Then, add the Query for Data step to the rule list.

Let's do the third button. Click the 'Submit' button and in the Rules pane, select 'Add' then 'Submit data'. Make sure in the dialog box that the 'Main connection' is selected in the drop-down menu.

Finally, we will do the fourth button, 'New Record'. This is a tricky one. We are going to use the 'LatestRecord' view we created above to fetch back the maximum record number, and add 1 to it to create a new record. Then, when the user populates the fields and hits Submit, this record data will be stored to the database.

First, click Manage Data Connections. Add, Receive Data, Database then Select Database and select the server connection file we have been using throughout. Select the 'LatestRecords' view, Next, Next and Finish. The new exposed column in QueryFields will be called MaxPersonID.

Now Click the New Record button. In the Rules pane, hit Add and select 'Set a field's value'. For the Field value, select Main -> QueryField -> PersonID. For the Value column, select the LatestRecords view from the drop-down and the MaxPersonID column and enter @MaxPersonID + 1 as the formula.

Select a Field or Group in InfoPath 2013

Finally, use the Add button in the Rules dialog to add the 'Query for Data' action afterwards.

What we have done is tell InfoPath than when the button is clicked, fetch the latest record number and go to the record *after* this one. This means that a new record is created with the PersonID incremented.

And ... that's it! We now have a functional working form.

Use the 'Preview' button on the top-right of InfoPath to test it out.

InfoPath 2013 Known Issues and Further Work

Of course, this is a straightforward example without much in the way of customization, odd workarounds or hacks. There are some limitations with the model I've demonstrated - some are listed below.

  • Using Previous / Next Record buttons on values < 1 yields 'Not a number (NaN)' errors - we could fix by adding hard limits in the formula using mathematics or CASE-like statements
  • LastUpdated field doesn't update to the latest date when any other field is updated - we could fix using a trigger on INSERT, or some custom VB code
  • The aesthetics could use some work. I've found InfoPath to be in some ways more versatile than other tools in this regard. Background images, fonts, borders, behavior - all can be changed to fit the requirements. Play with the tools and see what's possible.
  • SECURITY! Make sure you secure your database connection files. If using Windows authentication, bear in mind the users of the form must have adequate permission to use the SQL Server database or connection to the data source will fail.

Preview the InfoPath 2013 Form

Here's a screenshot of our form in Preview mode:

InfoPath 2013 form Preview

Publication Options with InfoPath 2013

Of course, it's all very well creating the ideal form set but if no-one can use it, there's not much point, right? Luckily, InfoPath is compatible with SharePoint and forms can be published too by e-mail, or to a shared network location with a single click. Users only need InfoPath Filler to view a form in this way, not the full Designer. In SharePoint, of course, no special tooling is needed.

InfoPath 2013 Publishing Options

It is beyond the scope of this article to guide you through SharePoint publication, testing and troubleshooting step-by-step, but it's fairly straightforward - check the File -> Publish link in InfoPath for the options, and please check the Further Reading section for further assistance.

Next Steps

This is an unconventional article about SQL Server - not so much about SQL Server, but about the tools one can use to design a basic user interface for it. I have recently implemented six of these interfaces using InfoPath and SharePoint with some complex client-side validation and logic, and for those without the time, resources or patience to roll your own solution, I would highly recommend it.

For further reading about integrating InfoPath with SharePoint, I can also highly recommend the following book:

Mann, S., InfoPath with SharePoint 2010 How-To: Real Solutions for Using InfoPath with SharePoint 2010, 2010 1st Ed., Published by SAMS, ISBN 978-0672333422

Please also check out these other related tips from MSSQLTips:

Last Update:

signup button

next tip button

About the author
MSSQLTips author Derek Colley Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

SQL tips:

*Enter Code refresh code     

Thursday, December 07, 2017 - 9:03:24 AM - Greg Kotecki Back To Top

Great article Derek!  Was just wondering if you could make available the Infopath *.XSN form you used.  The directions around the rules settings didn't match the graphic, and it would be nice to see what your view of the rules were to see if they match up to what I had created.


Thanks -



Friday, May 19, 2017 - 10:05:26 AM - Stephen Back To Top

 Great read. Thanks for posting it

I am trying to submit data to a SQL table with a primary key while simultaneously saving to a sharepoint library (which I have done), but then be able to open the version saved on the sharepoint library and make modifications to the form and then update those changes in SQL.

The only problem that I have had so far is that this creates an append to SQL rather than an Update to SQL. In other words it tells me that I can't have 2 records with identical primary keys. Is it possible to update the row rather than always create a new one in SQL?





Saturday, February 11, 2017 - 1:52:30 PM - Kevin Back To Top


 Just finished this tutorial, I am haveing 2 issues with the final product.

1. New Record does advance to a new record but it appears to be read only (cannot edit).

2. the Next button flashes the Database connection at the bottom but does not advance to the next record ( I have created 2 records)

3. both the previous and the submit butoon works.

Any idea what I did wrong, I have redid those buttons several times now.


Thanks for your time and effort in this tutorial and my question

Friday, February 03, 2017 - 5:04:44 PM - Le Roy Back To Top

I have spent 4 hours trying to make this work and either I am missing steps or you left something out.  This is so simple yet I have missed something. 

 This is where your doc has left me hanging.

On the PersonID field, right-click and Change Binding... Under the QueryFields tree, select the PersonID column (this is the view output column). Click OK.


Tuesday, November 15, 2016 - 3:16:15 AM - Kim Ford Back To Top

 This was an excellent tutorial however, I cannot get my buttons to work. Here is where I stumble. When I added my new connections (mind you, I'm using my own database but my views are working as they are supposed to work), I added AllRecords (using the View). However, the only place I see query fields is in the main data connection, the only thing that is in my AllRecords data connection are dataFields. In fact, none of my secondary connections have queryFields, only dataFields. 

The form is capturing information in my SQL server upon Submit but I cannot get it to open a new record, go to previous or next record. 

What am I missing for the queryFields?

Thank you for any guidance you can give and again, this was wonderful, it was exactly what I was looking for all day today and then finally found it earlier this evening.  


Thursday, September 22, 2016 - 5:19:35 PM - Paul Kanterman Back To Top

Great post.  Very helpful.


I've got one question, I cannot get the "New Record" button to work.  I click it and see some action happening, but @PersonID does not advance to the next number.

Any thoughts?


Sunday, April 17, 2016 - 1:49:08 PM - Derek Colley Back To Top



Not as far as I know.  Sharepoint Designer uses pre built web and app parts and a different design. It is difficult to do anything custom with Sharepoint unless you want to dive into coding.



Saturday, April 16, 2016 - 7:42:35 PM - Gary Dimesky Back To Top


 Will this work on Sharepoint Designer 2010?

Monday, April 11, 2016 - 6:21:34 PM - Derek Colley Back To Top

 Hi Jacque

I have not, but InfoPath connects via ordinary ODBC connection files.  However, as far as I know there is no official support for anything other than MSSQL and Sharepoint objects.  Please give it a try and let me know how you get on!



Monday, April 11, 2016 - 2:23:18 PM - Jacque Back To Top

 Hi Derek, have you ever used InfoPath to connect to and update a mysql database table?  


Monday, April 11, 2016 - 10:25:28 AM - Derek Colley Back To Top


That is correct, InfoPath Designer is not available as part of Office 2016.  However, it is available in Office 2013 Professional Plus Edition.

Further, although no new versions of InfoPath are planned, it will continue to be supported until April 2023, InfoPath forms services will be supported in SharePoint Server 2016, and it remains supported in Office 365.

Obsolence should not be a real issue for the next 3-5 years at least.  Once forms are published to SharePoint, support is planned well into the future.


Monday, April 11, 2016 - 6:24:47 AM - Dimitry Back To Top

InfoPath is NOT included in the Office 2016.

Learn more about SQL Server tools