InfoPath 2003 and SQL Server 2000 - Part 2

Introduction

It has been around two months since I wrote the

first partof this series, and I was waiting to get some comments on the previous article. Nevertheless, most of the people are still not using InfoPath 2003 as much as me!! Anyway, let me continue with my findings on this new tool. Today we will design a InfoPath Page to retrieve data from a SQL Server 2000 database. Like most of my other articles, I will use the Northwind database for the examples. For the first one I will explain how to do a simple data retrieval from Order and Order Details tables to get order data.

Let's Start

After running InfoPath 2003, click Design a Form option of the File menu. Then from the rights side select New from data source... which will give you the following screen.

Figure 1 : Selecting Data Source

After selecting Database option and press Next >

By pressing the Select Database... combo box, you will get the option to create a new data source or select existing one. By selecting the Northwind database from the databases combo, you will get the following screen. make sure that you have unselected Connect to a specific table, which will allow you to create join queries among the tables.

Figure 2 : Select Database

After selecting the database now it is time to select the tables that we need to get data. By selecting Add table twice select the Orders and Order Details tables. From the window, by selecting or deselecting you can decide which field you need to display or not to display at the running of the report. I have encountered a little problem here. That is I can't modify this SQL query after completing the report. If anybody knows how to do it you are welcome to share the knowledge with us.

Figure 3 : Selecting Tables

After pressing Next > you will get the two tables to give the link. It obvious that you need to select OrderID fields of the both tables to link them.

Figure 4 : Link two tables

After finishing this you will be taken to the Figure 3 screen in which you can edit the SQL query or the select / unselect fields that you want to query. With that just complete the things with Finish button. Make sure that you won't change the default option of Design query view first.

Figure 5 : Basic outcome from the InfoPath 2003

This is the final outcome that you will get from the InfoPath2003. Now it's time to inject some of our thoughts to this to make this a better and user-friendly page.

First we are not going to enter any data from this screen. as a first step we just need to query some data from this page. Select Views from rights side which will give you two sets namely Data Entry (Default)  and Query. Select Data Entry (Default)  right click it and delete it!!

Now you can change the heading caption and the description, about which I won't elaborate. As I mentioned time and time again. we are not going to enter any data from this screen. Therefore no need for a New Record button. Therefore select it and delete it!

Select the other fields and delete them. Yes, I said delete them. Now will be sitting in front of virtually a white screen with button called Run Query. Never mind.

Now select Controls  from the right side and drag and drop the control called Repeating Table. Just before you drag and drop you will see another windows comes up with asking for the table bindings. select datafields and select orders proceed from their with Next > button.

Figure 6 : Select fields you need to attached to the table

It is almost finish now. Click Preview Form and press Run Query.You will see the list of the order list.

Figure  7 : Final Out come

Figure 7 is the final outcome of our work . But now why are different colors seen? That is what you called conditional Formatting in InfoPath 2003. By right clicking the table select Repeating Table Properties and in the Display tab select the Conditional Formatting button where you can different colors to the different rows depending on the data each row contains.

Now let me show you some fantastic screens, which I generated using InfoPath2003.

Figure 8 : Can you Prepare this screen you will be a good learner

This figure I have link Employee table as well. where you can get the employees details as well. But mind you this will take some time to generate the report. Here I haven't delete the InfoPath2003 generated fields which displays above the Run Query button. Those are for the sub query. For example if you enter 1 at the EmployeeID and run the query you will get only the data which has employee id is 1.

With that I have finished with part two. All are welcome to send me your thoughts on InfoPath2003 and your comments on this article.