Which of the Following Tasks Can You Perform Using a Data Input Form?

By:   |   Updated: 2016-04-11   |   Comments (17)   |   Related: More > Application Development

Problem

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

Solution

This article introduces Microsoft InfoPath, an application that is role of Microsoft Office Professional Plus 2013, or available aslope Office 365 (ProPlus subscription but). It is used to pattern and share forms between user groups and integrates fully with SharePoint and SQL Server together with being able to produce standalone forms with nix code exposed to the user and no cognition of SQL required. This commodity volition as well guide you through creating an interactive form continued 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 chosen upon to diagnose SQL Server issues; a poorly-performing query, unusual fault message, an alert from the monitoring tools or some other database assistants problem. Occasionally, I become asked to implement a feature - for example, setting up monitoring, replication or log aircraft, or reviewing the fill-in strategy and making some changes. There's the twenty-four hours-to-day DBA work, reviewing and approving (or rejecting) requests from development teams, pushing changes into production and and so on. In my role every bit a contractor, I have a huge variety of requests that are unremarkably fairly predictable and most fall within my field of expertise.

Recently, notwithstanding, I had a request to provide a information 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 practice. I mean, SQL Server is for applications, right? It's a middleware layer, non something your everyday John Q. User would need to know about, permit alone interact with?

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

O...kay. So now I'm showtime 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 .Cyberspace in anger beyond VBScript and PowerShell, and I'grand certainly not up to the task of producing a solid and robust awarding within a web interface, not on my own or in the brusk timescale I was given.

And then, what to do? Clearly I needed some other form-creating tool ... SharePoint has some nuts, but nix that met all the criteria above. Here's a great opportunity then to introduce Microsoft InfoPath. In this tip, I'm using Microsoft InfoPath 2022 with SQL Server 2008 Standard Edition.

Some training - in this demonstration, I will be reading and writing data to and from the aforementioned table, dbo.Person, and as well reading data from table dbo.lookup_animals in database 'msst_test'. You can find an object creation script hither if you would like to follow forth.

Preparation

Run this on your SQL Server installation (non production!) to follow along with the tip. Brand certain you take permission to practise this from your DBA showtime.

CREATE DATABASE msst_test  Get   Employ msst_test  GO   CREATE Table dbo.Person (  	PersonID INT IDENTITY(1,1) PRIMARY Primal,  	FirstName VARCHAR(100),  	Surname VARCHAR(100),  	DateOfBirth DATE,  	CurrentAge AS DATEDIFF(YEAR, DateOfBirth, GETDATE()),  	AnimalPreference1 INT,  	AnimalPreference2 INT,  	AnimalPreference3 INT,  	LastUpdated DATETIME DEFAULT GETDATE() ) Go   CREATE TABLE dbo.lookup_Animals (  	AnimalID INT Principal KEY,  	AnimalName VARCHAR(100) )  GO  INSERT INTO dbo.lookup_Animals  	SELECT ane, 'Elephant' Matrimony ALL  	SELECT 2, 'Bear' Marriage ALL  	SELECT 3, 'Cat' Matrimony ALL  	SELECT 4, 'Dog' UNION ALL  	SELECT 5, 'Rabbit' Get

Introducing InfoPath

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

And so permit'south dive in and accept a look at how to create a basic class, and connect this form upward to a SQL Server database.

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

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

Configuring the Master Database Connection

So, outset matter - let'south create a new Data Connectedness to our SQL Server database. My database is chosen 'msst_test' and stores customer information. When you create the course, the Data Connections Sorcerer will be displayed automatically. Begin by clicking 'Select Database', and so 'New Source':

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

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

Now in the resulting box, you tin salvage the connectedness as a information connection file. Check with your DBA first earlier 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 chosen 'Test Connexion'.

Now click Finish, Next and Finish. Ignore the warning virtually spider web browser grade incompatibility, nosotros volition change the type of form now.

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

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

Finally, delete every element in the grade by clicking on it and pressing 'Delete'. Y'all should be left with a completely bare class. Save this form.

Designing the InfoPath 2022 Form

We now accept a basic working connection to SQL Server and a bare grade to play with. Let'due south begin by calculation a title. In the same mode as yous would in Word, create a championship by typing near the tiptop of the page. Play with the size and font until you are happy with the result. Hither is mine, quite plain:

Now we need to use Controls to fetch the information 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 nosotros desire to brandish.

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

The Department volition testify up on your form. Resize the section to suit your form (I expanded mine down the page to make room for more Controls). Y'all can see the binding on the top-right of the Control:

Now, let's add the columnar data to the Section. For our example, we just want to show 1 record at a time - remember, information technology has to exist user-friendly, so dumping the information out as a tabular array is probably not a skillful idea! We'll need labels for each field too, and nosotros'll add ScreenTips to signal what is required. First, let'southward add a Text Box. Click the Text Box control in the Controls department of the Ribbon and select the 'FirstName' cavalcade under dataFields -> Person, then click OK. You lot'll go the following warning message:

This warning bulletin tin be ignored for now - information technology is explaining that because the container you have called (Section) is non-repeating, only the first tape in the ready (the first row) volition exist shown. This is logical - if nosotros're only making room for one record, it can just bear witness i record! We will counter this later past calculation interactive buttons so ignore information technology for now. After you lot click Yes, the control will appear in the Department.

Now repeat this procedure for the columns Surname, CurrentAge and LastUpdated. Feel gratis to experiment with the layout and font tools which are much similar Discussion. Tip: If you're struggling to reach a neat layout, use a table to prepare indentation bug and arrange your fields.

Hither is my example - yours will nigh certainly vary:

You'll notice I've left a gap between Surname and Electric current Age. Here, I'g going to add a different sort of Control - a Appointment Picker. Position the cursor where you lot'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 commencement of this tip, yous may take noticed that the 'Current Age' column is computed as a function of Appointment of Nascence and the electric current date/fourth dimension. It follows, then that we do not desire this field to be editable by the user - it should automatically populate, based on the entry in Date of Nascence. Right-click on the field and click Text Box Backdrop, then click the Display... tab. Make full the 'Read-but' checkbox to gear up this field to read-but.

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

Adding More Data Sources to the InfoPath 2022 Form

At present, we need to add the remaining columnar information to our grade. We're non interested in displaying the PersonID every bit this is but for internal use, simply nosotros would like to display data on each person'due south favorite animals. These are represented in columns AnimalPreference1, AnimalPreference2 and AnimalPreference3. However, the information in this columns is of type INT, and we tin can gauge (although there is no explicit key defined - nosotros could add a foreign cardinal without difficulty) that these will represent to the AnimalID for each animal listed in tabular array lookup_animals.

So, we have a few things to do:

1. Add the lookup_animals table to the grade as a Secondary Data Connection
2. Create some drop-downward lists for these preferences
3. Brand sure the drop down lists evidence the animals, not the integers
4. Make certain 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)'. Past at present you should take this window displayed:

Now, in the next Data Connection Sorcerer window, you tin select the data connection file you want to utilize. Every bit our connexion remains the same (only the table varies), select the 'Exam Connectedness' file and click OK. The Person table will populate automatically - click the 'Remove Tabular array' push and 'Add Table' button, then select 'lookup_Animals'. You should exist left with the following window:

Click Next, Next, Terminate 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. Every bit a prepare of query fields merely, it means the data in this table is never updated or added to past our form - information technology is only referenced (SELECTed past the form) where needed.

Build Drop Down Lists in InfoPath 2013

Let's at present address 2) above and create some drop-downwardly lists to show the animals. Use the 'Drop Down Listing' command from the Controls department of the Ribbon. Exist CAREFUL - when setting the binding, make sure you lot bind to the dataFields -> Person -> AnimalPreference fields, non the AnimalID or AnimalName query fields we just created! We need to tell the class that we are *storing* the result to the AnimalPreference field, non retrieving it from in that location.

Solving 3) and 4) above is a fleck tricky. One time you've placed your first drop-down, right-click on information technology and go to 'Drop Down Listing Box Properties'. In here, nether Listing Box Choices, you lot'll see three radio buttons enabling you to specify the source for the display values in the drop down box. Now we don't desire the user to accept to choose between a set up 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 gear up of lookup values.

Click 'Get Choices from an External Data Source'. Select 'lookup_Animals' for the Data Source, so 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 post-obit:

Echo the procedure for the AnimalPreference2 and AnimalPreference3 fields.

Adding Buttons to the InfoPath 2022 Form

Now, permit'south put some buttons on the form. Nosotros demand to be able to exercise four separate things in our case - gyre betwixt records (left and right) based on the PersonID primary cardinal, submit our changes to existing records, and create new records.

Utilize the 'Button' control in the Controls department and identify iv buttons on your class. It doesn't matter whether these are inside or outside your section. Use the right-click -> Button Properties... choice to rename them, and place them appropriately.

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

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

Adding Interactive Controls in InfoPath 2013

Remember at the beginning of the form design we received a warning well-nigh using the Department control? Well, we at present demand to put in some components to make certain that we tin can retrieve a specific record when called. By retrieving a specific record, we can so motion on to traverse records and create new records.

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

Mine's hither:

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

At present, we need to put some piece of work in so we tin can create a button that creates a new record. InfoPath does have functionality for this congenital into the Rules - close a class, or open a new form to fill out. Neither is what nosotros want - nosotros need to exist able to skip to the final record in the gear up, so increment the PersonID ready for the details to be filled out and submitted. Because InfoPath isn't groovy at supporting SQL querying (unless you want to write it in code, or exercise some inline SQL querying) nosotros tin 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 then:

USE msst_test GO  CREATE VIEW LatestRecord AS  	SELECT MAX(PersonID) [MaxPersonID] FROM dbo.Person  Become   CREATE VIEW AllRecords As  	SELECT PersonID FROM dbo.Person  Get          

Now we demand to utilise the output of one of these views to bind to the new PersonID field we created. Hit 'Manage Data Connections' once again and this time add a new information connection to 'Receive' (not submit) information. Select the aforementioned server connexion, and click Alter... to change the table. You'll encounter 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 cavalcade). Click OK.

What we've simply achieved is to tell InfoPath that we want the PersonID field to display the output of the view. Any number happens to be in the field volition likewise exist used to populate all the other fields on the course (as it is of type 'QueryField' not 'DataField').

Let's break hither. This is a really catchy concept and worth going over once again. The QueryFields list in the Fields window is separate into ii trees, the QueryFields tree and the DataFields tree. The full general rule is that any fields on the form spring to a Query field is the value the form will use to populate any other fields on the grade. 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!

At present nosotros have done the groundwork, allow'due south outset with setting up the Previous Tape button. Click once on the push to highlight it so in the Ribbon, on the right under the Rules section click Manage Rules.

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

Now click Add again and select 'Query for Information'. In the resulting window prepare the Data Connexion every bit 'Main connection' and click OK. The button should accept two rules.

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

Let'south do the third push 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 drib-downwards carte.

Finally, nosotros volition do the quaternary button, 'New Record'. This is a catchy one. We are going to utilize the 'LatestRecord' view we created above to fetch dorsum the maximum tape number, and add 1 to it to create a new tape. And so, when the user populates the fields and hits Submit, this record data will be stored to the database.

Start, click Manage Information Connections. Add, Receive Data, Database so Select Database and select the server connexion file we have been using throughout. Select the 'LatestRecords' view, Side by side, Next and Cease. The new exposed column in QueryFields will exist called MaxPersonID.

Now Click the New Tape push. In the Rules pane, hit Add and select 'Set a field'southward value'. For the Field value, select Chief -> QueryField -> PersonID. For the Value column, select the LatestRecords view from the drib-down and the MaxPersonID cavalcade and enter @MaxPersonID + 1 equally the formula.

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

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

And ... that'southward information technology! We now take a functional working form.

Use the 'Preview' push on the top-right of InfoPath to examination it out.

InfoPath 2022 Known Problems and Further Work

Of grade, this is a straightforward example without much in the fashion of customization, odd workarounds or hacks. At that place are some limitations with the model I've demonstrated - some are listed below.

• Using Previous / Adjacent Tape buttons on values < one yields 'Not a number (NaN)' errors - we could gear up past adding hard limits in the formula using mathematics or CASE-like statements
• LastUpdated field doesn't update to the latest engagement when any other field is updated - we could fix using a trigger on INSERT, or some custom VB code
• The aesthetics could use some piece of work. I've found InfoPath to exist in some ways more versatile than other tools in this regard. Background images, fonts, borders, behavior - all can exist changed to fit the requirements. Play with the tools and meet what's possible.
• SECURITY! Brand sure you secure your database connection files. If using Windows authentication, conduct in listen the users of the course must have acceptable permission to use the SQL Server database or connection to the data source will fail.

Preview the InfoPath 2022 Form

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

Publication Options with InfoPath 2013

Of class, it's all very well creating the platonic form set but if no-one tin can use information technology, 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 mode, not the full Designer. In SharePoint, of course, no special tooling is needed.

Information technology is across the scope of this article to guide you through SharePoint publication, testing and troubleshooting stride-past-step, but information technology's adequately straightforward - check the File -> Publish link in InfoPath for the options, and please cheque the Further Reading section for further assist.

Next Steps

This is an anarchistic article almost SQL Server - not and then much nearly SQL Server, merely about the tools i can use to blueprint a basic user interface for information technology. I accept recently implemented vi of these interfaces using InfoPath and SharePoint with some circuitous customer-side validation and logic, and for those without the time, resources or patience to gyre your ain solution, I would highly recommend it.

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

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

Please as well check out these other related tips from MSSQLTips:

• Ray Barley - Accessing SharePoint Lists with SQL Server Integration Services
• Ray Barley - Using a SharePoint List as a Data Source in SQL Server Reporting
• Jeremy Kadlec - Insert, Update or Delete Data in SQL Server from Excel

Related Articles

Popular Manufactures

About the writer

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

Commodity Final Updated: 2016-04-11

lewisstonclused1959.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4246/data-entry-for-sql-server-building-quick-efficient-data-input-forms-using-infopath/

Share this post