Tutorial: MSSQL- Windows Form Development
Multi-User Application With Custom Record Lockout #2
 [Query Set Views & Navigation Buttons & Filters & Order By]
Dr. Thomas E. Hicks
 Computer Science Department
Trinity University

Purpose

The purpose of this series of tutorials is to show how to configure a Windows Application Form, using Visual Studio, to process a multi-form, multi-user network application. This tutorial will briefly review many of the concepts demonstrated in some of the other tutorials.

Prerequisite Condition: each of our major database tables must have a unique, auto-incremented, integer field called ID.

This portion of the tutorial will examine and configure query sets, navigation buttons, combobox filters and combobox orderby.
About Part 1 Of Tutorial

This tutorial is fourth in a series of Windows Form Development Multi-User Application With Custom Record Lockout #1 [Database Configuration, Database Authentication, Generic Database Utilities, Etc.]. The topics in part 1 are as follows:

 Sections In This Tutorial Part 2

Part II-A:  Utilities Specific To Users - Non Generic - LoadToolTips (Buttons) & Configure MenuStrip ToolTips

Part II-B:  Select Filter & OrderBy ComboBoxes

Part II-C:  Database Views

Part II-D:  Generic Table Function Utilities - QueryTableLength

Part II-E:  Database Query Language Limitations & Functionality

Part II-F:  Generic Table Function Utilities - ReadQueryRecord ReadCurrentRecord

Part II-G:  Generic Table Navigation Utilities - LoadNextRecord, LoadPreviousRecord, LoadFirstRecord, & LoadLastRecord

Part II-H:  Generic Utilities - SetLogRecNo, LoadFirstLetterRecord

Part II-I:  Non-Generic Utilities OrderBy & Select ComboBoxes

Part II-J:  For You To Do

 

 If You Have Difficulty With This Tutorial,  You Might Find These Help Prepare You For This Tutorial
I Recommend That My Students Complete The Tutorials In This Order!
 

Tutorial: MSSQL TU Database-Construction Using Microsoft SQL Server

Tutorial: MSSQL-Forms - Windows Application & Basic Form Properties

Tutorial: MSSQL-Forms - Windows Application - Labels & Textboxes

Tutorial: MSSQL-Forms - Windows Application - Buttons

Tutorial: MSSQL-Connection\MSSQL-Connections

Tutorial: MSSQL-Forms - Windows Application - Menu - Multiple Forms

Tutorial: MSSQL-Visual Studio MenuStrip Application With Multiple Windows

MSSQL-Forms - Windows Application - Web Browser

MSSQL-Windows-Forms-ComboBox.html
Copy Project

1] Copy folder Library-Net-Multi-User-1 to folder Library-Net-Multi-User-2. I always recommend making a backup copy about ever hour or so.

Part II-A:
Utilities Specific To Users - Non Generic
LoadToolTips (Buttons) & Configure MenuStrip ToolTips

1] It goes without saying that clear, concise button and menustrip titles are an absolute must. It is often the case that users of a database system vary in abilities and familiarity with the system. Tool tips can reduce the need for manuals and on-line help by expanding upon the functionality of buttons and menustrip items.

2] This portion of the tutorial is going to add some additional testing functionality and then help you to use tool tips to increase the usability of your library application.
 
Update TestingMaster In Users.cs

1]  Change the UserClassDiagnosticLevel to 12. (See Below!)

2] Open the User.cs form. Replace function TestingMaster with a pasted copy of the block of code below!

        //================================================================================//
        //                                  TestingMaster                                 //
        //================================================================================//
        //  Purpose: Testing Master which evokes TestModule1, TestModule2, ...            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public void TestingMaster()
        {
            int UserClassDiagnosticLevel = 0;

            if (UserClassDiagnosticLevel == 0)
                TestAll();
            if (UserClassDiagnosticLevel == 1)
                TestModule1();
            if (UserClassDiagnosticLevel == 2)
                TestModule2();
            if (UserClassDiagnosticLevel == 3)
                TestModule3();
            if (UserClassDiagnosticLevel == 4)
                TestModule4();
            if (UserClassDiagnosticLevel == 5)
                TestModule5();
            if (UserClassDiagnosticLevel == 6)
                TestModule6();
            if (UserClassDiagnosticLevel == 7)
                TestModule7();
            if (UserClassDiagnosticLevel == 8)
                TestModule8();
            if (UserClassDiagnosticLevel == 9)
                TestModule9();
            if (UserClassDiagnosticLevel == 10)
                TestModule10();
            if (UserClassDiagnosticLevel == 11)
                TestModule11();
            if (UserClassDiagnosticLevel == 12)
                TestModule12();
            if (UserClassDiagnosticLevel == 13)
                TestModule13();
            if (UserClassDiagnosticLevel == 14)
                TestModule14();
            if (UserClassDiagnosticLevel == 15)
                TestModule15();
            if (UserClassDiagnosticLevel == 16)
                TestModule16();
            if (UserClassDiagnosticLevel == 17)
                TestModule17();
            if (UserClassDiagnosticLevel == 18)
                TestModule18();
            if (UserClassDiagnosticLevel == 19)
                TestModule19();
            if (UserClassDiagnosticLevel == 20)
                TestModule20();
            if (UserClassDiagnosticLevel == 21)
                TestModule21();
            if (UserClassDiagnosticLevel == 22)
                TestModule22();
            if (UserClassDiagnosticLevel == 23)
                TestModule23();
        }

2] Add function TestAll.

        //================================================================================//
        //                                  TestingAll                                    //
        //================================================================================//
        //  Purpose: Test all of the functions: TestModule11, TestModule12, ...           //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public void TestAll()
        {

            TestModule11();
            TestModule12();
            TestModule13();
            TestModule14();
            TestModule15();
            TestModule16();
            TestModule17();
            TestModule18();
            TestModule19();
            TestModule20();
            TestModule21();
            TestModule22();
            TestModule23();
        }
Include Testing Code In Users.cs

1] Add the following Test function shells to Users.cs. 

        //================================================================================//
        //                                  TestModule12                                  //
        //================================================================================//
        public void TestModule12()
        {
            MessageBox.Show("TestModule12");
        }

        //================================================================================//
        //                                  TestModule13                                  //
        //================================================================================//
        public void TestModule13()
        {
            MessageBox.Show("TestModule13");
        }

        //================================================================================//
        //                                  TestModule14                                  //
        //================================================================================//
        public void TestModule14()
        {
            MessageBox.Show("TestModule14");
        }

        //================================================================================//
        //                                  TestModule15                                  //
        //================================================================================//
        public void TestModule15()
        {
            MessageBox.Show("TestModule15");
        }

        //================================================================================//
        //                                  TestModule16                                  //
        //================================================================================//
        public void TestModule16()
        {
            MessageBox.Show("TestModule16");
        }

        //================================================================================//
        //                                  TestModule17                                  //
        //================================================================================//
        public void TestModule17()
        {
            MessageBox.Show("TestModule17");
        }

        //================================================================================//
        //                                  TestModule18                                  //
        //================================================================================//
        public void TestModule18()
        {
            MessageBox.Show("TestModule18");
        }

        //================================================================================//
        //                                  TestModule19                                  //
        //================================================================================//
        public void TestModule19()
        {
            MessageBox.Show("TestModule19");
        }

        //================================================================================//
        //                                  TestModule20                                  //
        //================================================================================//
        public void TestModule20()
        {
            MessageBox.Show("TestModule20");
        }

        //================================================================================//
        //                                  TestModule21                                  //
        //================================================================================//
        public void TestModule21()
        {
            MessageBox.Show("TestModule21");
        }

        //================================================================================//
        //                                  TestModule22                                  //
        //================================================================================//
        public void TestModule22()
        {
            MessageBox.Show("TestModule22");
        }

        //================================================================================//
        //                                  TestModule23                                  //
        //================================================================================//
        public void TestModule23()
        {
            MessageBox.Show("TestModule23");
        }
Function LoadToolTips

1]  Change the UserClassDiagnosticLevel to 12. (See Below!)

2] Using your mouse, drag a ToolTip from the ToolBox to your User.cs form. Name the ToolTip toolTip1.

3] Write function LoadToolTips to to configure the buttons on the User.cs form.

4] Copy the code from TestModule11 to TestModule12 and make the following changes.

5] Execute the Users Test Module. You should get the following output!  Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above. Move your mouse over the buttons to see that all of the tool tips are working properly. The screen capture below shows the mouse over the M button. (See Below!)


 
Configure ToolTips For Users MenuStrip1

1]  Go to the properties tab of the Users.cs form. Select MenuStrip1. Change the ShowItemToolTips property to True. (See Below!)

2]  Using the mouse, select the Items Collection tab on the properties tabl of this same form. (See Below!)
3]  Configure the ToolTipText property for the Add menustrip item. (See Below!)

4]  Configure the ToolTipText property for the Delete menustrip item. (See Below!)

5]  Configure the ToolTipText property for the Edit menustrip item. (See Below!)
6]  Configure the ToolTipText property for the Save menustrip item. (See Below!)
7]  Configure the ToolTipText property for the Cancel menustrip item. (See Below!)
8]  Configure the ToolTipText property for the UnDelete menustrip item. (See Below!)
9]  Configure the ToolTipText property for the Search menustrip item. (See Below!)
10]  Configure the ToolTipText property for the Reports menustrip item. (See Below!)
11]  Configure the ToolTipText property for the Add menustrip item. (See Below!)
12] Execute the Users Test Module. Check the tooltip for each of the menustrip items. The mouse is over Delete below.
Update Function Users_Load

1] As we approach a solution for cycling through the records, we are going to define four additional variables that shall be available to the user class methods. Add the following to your Users class. We will gradually use and initialize them throughout this tutorial.

2] Update the Users_Load function as illustrated below. Form Users.cs represents a view into the Users database table. If we define a few variables at the top of the form, we shall be able to make many of our functions more generic.

  1. The TableName used on this form shall be "Users".
     

  2. The PrimaryKey on the Users Table is the "ID" field.
     

  3. The WhereClause Records will serve as a filter that identifies that portion of the Users table which shall be available to navigate any given moment in time.

    1. We are going to be tagged for deletion, as opposed to physically removing items.

    2. This will enable us to undelete records later.

    3. If we were to make the WhereClause = "(Deleted != 'T')",  we would make all undeleted records available for navigation.

    4. If we were to make the WhereClause =  "(Deleted != 'T') AND (Administrators = 'T')", we would make all undeleted administrative records available for navigation.

    5. We shall start by making the WhereClause = "(Deleted != 'T')"
       

  4. When we first launch our table, we will have the order in

 

Part II-B:
Select Filter & OrderBy ComboBoxes

1] Almost every database application should enable users to determine the order in which they process the data; we shall do this in our application by using a ComboBox, called cbOrderBy. Although real-life applications will provide users with more selections, the ones we use in this tutorial shall be sufficient to demonstrate the principles.

2] Almost every database application should enable users to select subsets of the data that they wish to process; when looking for a book on Religion, the user should not have to walk through books on Science, History, etc. We shall do this in our application by using a ComboBox, called cbSelect. Although real-life applications will provide users with more selections, the ones we use in this tutorial shall be sufficient to demonstrate the principles.
 
Add A Filter To The Users Form
 
1] Almost all application forms should use filters to help users select the ideal subset of data.
2] Add a label, whose text is Select, to your Users form.

3] Add a ComboBox, named cbSelect, to your Users form. The items collection of the ComboBox is to include All Users, Administrators, Non-Administrators, CS Majors, and History Majors for choices. (See Below!)

4] We shall add code for the ComboBoxes later in this tutorial.
 

Part II-C:
Database Views

1] The various forms on our system shall enable our users to get a glimpse, or view, of the data at some snapshot in time.

2] The Library Application will consist of forms for Users, Books, Check-Out Transactions, etc.

3] Most of the View-Oriented forms, such as Users, Books, Check-Out Transaction, etc. shall contain a Selection Filter to allow the user to customize the view and quickly locate those items of interest. These select filters will determine the what portion of data the user shall process and the order by combo will determine the order in which they process that data.

 
The Users Form View

1] The users form shall reflect a view into the database. The filter choices for a real Libray Application would be more extensive, but these shall suffice for educational purposes. We shall offer five selection filter choices.

2]  The order choices for a real Library Application would also be more extensive, but these shall suffice for educational purposes. We shall offer three order choices.

3] For the moment, suppose our data were ordered by ID. As you look at the data below, we can clearly see that four records have been tagged for deletion at this snapshot in time. They are records with ID = 8, 9, 16, and 50. Make sure that your database has these same records tagged for deletion.



4] Suppose that the user chooses to order the data by "Name". Suppose that the user chooses to select "All Users". In database we could build our query set with the following query. Note that the deleted users are filtered out! As the user navigates through the users with the Next Button, we would like them  to go from Joshua to Reid to Kenneth, etc.

 

5] Suppose that the user chooses to order the data by "Name". Suppose that the user chooses to select "Administrators". In database we could build our query set with the following query. Note that the deleted users and non-administrators are filtered out! As the user navigates through the users with the Next Button, we would like them  to go from Tom to Steven to Drew to John to Crisanto to Michael and then back to Tom, etc.

6] Suppose that the user chooses to order the data by "Name". Suppose that the user chooses to select "CS Majors". In database we could build our query set with the following query. Note that the deleted users and non-cs-majors are filtered out! As the user navigates through the users with the Next Button, we would like them  to go from Dennis to Amy to Joseph, etc.

7] Suppose that the user chooses to order the data by "Major". Suppose that the user chooses to select "Administrators". In database we could build our query set with the following query. Note that the deleted users and non-administrators are once more filtered out! Note that the order by alters the order in which the records would be processed. As the user navigates through the users with the Next Button, we would like them  to go from Crisanto to Steven to Michael to Drew to John to Tom and then back to Crisanto.

8] Suppose that the user chooses to order the data by "Name". Suppose that the user chooses to select "History Majors". In database we could build our query set with the following query. Once the deleted users and non-history-majors are filtered out, the solution set is empty. This can happen as records are added and deleted. It shall be our program responsibility to display a message to the users and return to the previous query set state.

9] The Select and Order By shall determine our query set views throughout this series of tutorials.

        

Part II-D:
Generic Table Function Utilities
QueryTableLength

1] The TableLength function provides the number of items in the entire table, but our query set view will often be just a subset of that original table.

2] Since we are often going to be looking at a subset of that total data (maybe the undeleted portion - maybe those users that are administrators), we need a function to that returns the number of records in our specific query set.
Function QueryTableLength 

1]  Change the UserClassDiagnosticLevel to 13. (See Below!)

2] Write function QueryTableLength which shall explicity return the number of items in the query set.

3]   Copy the code from TestModule12 to TestModule13 and make the following changes.

4] Execute the Users Test Module. You should get the following output!  Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

 

5] Open Microsoft SQL Server Management Studio Express and view the Users; verify the query results above.

6] Add the call to QueryTableLength in the Users_Load. (See Below!)
Functions ReadQueryLogRecKeys &  DisplayLogRecKeys

1] Now to functionalize this. Change the UserClassDiagnosticLevel to 14. (See Below!)

2] Write function ReadQueryLogKeys which fills DataTable LogRecKeys with only those primary keys in the current view's query set; these keys are sorted in order as dictated by the Order By combobox. The NoLogRecs is also filled within this function. This set of logical record keys shall be used to navigate within our query view.

3] If we are going to use DataTable LogRecKeys to navigate our system, it only seems appropriate to be able to take a look at the structure in a graphical format. Write function DisplayLogRecKeys which will render a graphical view of the DataTable.

4]   Copy the code from TestModule13 to TestModule14 and make the following changes.

5] Execute the Users Test Module. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

 

 

 

 

 6] You can see, from the query below, that the Logical Record Query Set for the Non-Deleted Administrators (Above) directly corresponds with the database query below.

7] When the Next Button is pushed, the LogRecNo shall be incremented from 6 (Tom) to 2 (Steven) to 4 (Drew), etc.

Part II-E:
Database Query Language Limitations & Functionality

1] In the ideal world, database has the exact query language and this query language has all the functionality ever needed. Although we have had several generations of standards for SQL, each database application will continue to try to increase their functionality.

2] As you read through this section, you will note that MySQL has a really neat Limit attribute.

3] You will also find that Microsoft SQL Server does not support this Limit attribute; since I want the functionality, I will have to come up with a "work-around".
MySQL Database Has A Limit Query Option!

1] Different databases enhance/enrich the standard query language by providing additional functionality; the Limit functionality provided by MySQL is such an enhancement. Suppose we do the Query : Select * From Users Order By FullName Limit 3,5 (See Below!)

2]  The Query : Select * From Users Order By FullName Limit 3,5 would display 5 records in logical order -  starting with the RecNo 3. The first record is RecNo = 0.

3] The Query : Select * From Users Order By FullName Limit 0,1 would display RecNo = 0.

4] The Query : Select * From Users Order By FullName Limit 4,1 would display RecNo = 4.

5] Limit makes it extremely easy to cycle through records in a MySQL database using both web pages or stand-alone applications. The limit provides a truly generic solution that will work with any table in any order.
Microsoft SQL Server Has No Limit Option!

1] After hours of research, I have not been able to find a truly generic solution for MSSQL. I do have something that will work for tables that contain a Primary Key. There are lots of hack solutions on the Internet, but I think the solution below offers the most generic approach for MSSQL.

2] In order for the solution below to work, the table must have a Primary Key. The PrimaryKey for the Users table is ID.

3] Once again we are going to return to the collection of undeleted administrators.  Try this query with your database!

4] Examine the results of the following query. It give us the first [LogRecNo = 0] record in the undeleted administrators collection set. Try this query with your database!

5] Examine the results of the following query. It give us the second [LogRecNo = 1] record in the undeleted administrators collection set.  Try this query with your database!

6] Examine the results of the following query. It give us the third [LogRecNo = 2] record in the undeleted administrators collection set.  Try this query with your database!

7] Examine the results of the following query. It give us the fourth [LogRecNo = 3] record in the undeleted administrators collection set.  Try this query with your database!

8] Examine the results of the following query. It give us the fifth [LogRecNo = 4] record in the undeleted administrators collection set.  Try this query with your database!

9] Examine the results of the following query. It give us the sixth [LogRecNo = 5] record in the undeleted administrators collection set.  Try this query with your database!

10] Examine the results of the following query. Since there is no seventh [LogRecNo = 6] record, the solution set is empty.  Try this query with your database!

 

Part II-F:
Generic Table Function Utilities
ReadQueryRecord & ReadCurrentRecord

1] Function ReadQueryRecord will be our "work-around" that enables us to fill the dataset object by LogRecNo as taken from our current, ordered query set.

2] This function is more flexible because we allow the class to specify the PrimaryKey, but it must have one!
Function ReadQueryRecord

1] Now to functionalize this. Change the UserClassDiagnosticLevel to 15. (See Below!)

2] Write function ReadQueryRecord which fill the dataset (dt) in accordance the LogRecNo of the current query set.

3]   Copy the code from TestModule14 to TestModule15 and make the following changes.

4] Execute the Users Test Module. You should get the following output!  Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above. The loop goes from -1 to 6; values -1 and 6 should prove unsuccessful since there are only 6 valid administrators in the query set. Note that they are being displayed in the proper order. Both the filter and the order are working correctly!
Function ReadQueryRecord

1] Now to functionalize this. Change the UserClassDiagnosticLevel to 15. (See Below!)

2] Write function ReadQueryRecord which fill the dataset (dt) in accordance the LogRecNo of the current query set.

3]   Copy the code from TestModule14 to TestModule15 and make the following changes.

4] Execute the Users Test Module. You should get the following output!  Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above. The loop goes from -1 to 6; values -1 and 6 should prove unsuccessful since there are only 6 valid administrators in the query set. Note that they are being displayed in the proper order. Both the filter and the order are working correctly!
Function ReadCurrentRecord

1] Now to functionalize this. Change the UserClassDiagnosticLevel to 16. (See Below!)

2] Write function ReadCurrentRecord which fill the dataset (dt) in accordance the LogRecNo of the current query set.

3]   Copy the code from TestModule15 to TestModule16 and make the following changes. Note that, in addition to the output, the form changes from Tom, to Steven to Drew, etc.

4] Execute the Users Test Module. You should get the following output!  Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above. The loop goes from through the first six logical records in the query set. Note that they are being displayed in the proper order. Both the filter and the order are working correctly!




 
 

5] You can verify the data above with the database query below.

 

Part II-G:
Generic Table Navigation Utilities
LoadNextRecord, LoadPreviousRecord, LoadFirstRecord, & LoadLastRecord

1] We often have to design systems for a variety of users. Power users often want to do lots and move their mouse little. I am using the control above to enable the user to quickly move to the next record, the previous record, the first record, and the last record in the current query set.

2] There is a control on the left and the right to reduce necessary mouse movement. Adding control key functionality would be beneficial and easy, but we are not going to do it in this tutorial.
Function LoadNextRecord

1] Change the UserClassDiagnosticLevel to 17. (See Below!)

2] Write function LoadNextRecord which shall load the next logical record, in the query set, into the users form. NoLogRecs is repeatedly filled because any user on a concurrent system might add or delete records at any time.

3]   Copy the code from TestModule16 to TestModule17 and make the following changes.

4] Execute the Users Test Module. There are only 6 records in the record set. The loop simulates the pushing of the Next button 21 times. The records should cycle from the last record (Warner) back tot he first record (Hicks). In addition to the trace, you should see the record data change every second for a twenty second period. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

  

5] Double click on btnNextRight   and btnNextLeft of your Users form.  Add a call to LoadNextRecord for both btnNextRight and btnNextLeft. (See Below!)
Function LoadPreviousRecord

1] Change the UserClassDiagnosticLevel to 18. (See Below!)

2] Write function LoadPreviousRecord which shall load the previous logical record, in the query set, into the users form. NoLogRecs is repeatedly filled because any user on a concurrent system might add or delete records at any time.

3]   Copy the code from TestModule17 to TestModule18 and make the following changes.

4] Execute the Users Test Module. There are only 6 records in the record set. The loop simulates the pushing of the Previous button 21 times. The records should cycle from the first record (Hicks) back tot he last record (Warner). In addition to the trace, you should see the record data change every second for a twenty second period. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

 

5] Double click on btnPreviousRight   and btnPreviousLeft of your Users form.  Add a call to LoadPreviousRecord for both btnPreviousRight  and btnPreviousLeft . (See Below!)

6] Execute the Users form but do not push the Testing button. Push only the left and right previous buttons to cycle through all of the records. Make sure that your Previous button provides the records in the above order (skipping those records that are tagged for deletion.
Update Function Users_Load

1] Once you update Users_Load, we shall leave it alone for a while.

Function LoadFirstRecord

1] Change the UserClassDiagnosticLevel to 19. (See Below!)

2] Write function LoadFirstRecord which shall load the first logical record, in the query set, into the users form.

3]   Copy the code from TestModule18 to TestModule19 and make the following changes.

4] Execute the Users Test Module. The second logical record is loaded; after a one second delay, the first record is loaded. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

5] Double click on btnTopRight   and btnPreviousTopLeft of your Users form.  Add a call to LoadFirstRecord  for both btnTopRight and btnPreviousTopLeft. (See Below!)

6] Execute the Users form but do not push the Testing button. Push the left previous button a couple of times. Then push the top button to load the first logical record.

Function LoadLastRecord

1] Change the UserClassDiagnosticLevel to 20. (See Below!)

2] Write function LoadLastRecord which shall load the last logical record, in the query set, into the users form.

3]   Copy the code from TestModule19 to TestModule20 and make the following changes.

4] Execute the Users Test Module. The second logical record is loaded; after a one second delay, the first record is loaded. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

5] Double click on btnBottomRight   and btnBottomLeft of your Users form.  Add a call to LoadLastRecord  for both btnBottomRight and btnBottomLeft. (See Below!)

6] Execute the Users form but do not push the Testing button. Push the left next button a couple of times. Then push the bottom button to load the last logical record.

Part I-H:
Generic Utilities
SetLogRecNo, LoadFirstLetterRecord

1] Buttons A-Z do not replace good search functionality, but they do complement it.

2] The scenario is this. A user is currently looking at the record for Hicks, Tom. When finished, the user wants to proceed to the record for Smith, Heather. Pushing the S does not immediately find Heather, but it gets the user close--> they then press the Next button once and are there in our example.
Functions SetLogRecNo & LoadFirstLetterRecord

1] Change the UserClassDiagnosticLevel to 21. (See Below!)

2] Function LoadFirstLetterRecord , below, is going to load the nearest match (in the query set) to the letter selected. If the letter 'C' is selected, the nearest match to the first 'C' will be selected; should there be no 'C', it will proceed to the nearest match beyond the 'C' - perhaps a 'D', 'E', etc. Once the nearest match has been loaded into the form, it will be necessary to re-set the LogRecNo.

3] Write function SetLogRecNo which is a support function that determines the LogRecNo of the record currently in the datatable (dt). It is used by LoadFirstLetterRecord.

4] Write function LoadFirstLetterRecord which shall load the first logical record matching the specified letter in the identified field name. By passing in the FeldName, it can be generic enough to be usable on other forms in other tables and databases.

5]   Copy the code from TestModule20 to TestModule21 and make the following changes.

6] Execute the Users Test Module. You should be able to see that your function is working correctly for both the undeleted records and then the undeleted administrators. Examine the test module carefully to make sure that you understand what it is attempting to do. Examine the output carefully to make sure that it matches that below and is consistent with the test code above.

6] Double click on each of your letter buttons A through Z    Add an appropriate call to LoadFirstLetterRecord for each of these 26 buttons (See Below!)

7] Test each of the 26 buttons
Part II-I:
Non-Generic Utilities
OrderBy & Select ComboBoxes

1] Both the Select Filter ComboBox and the OrderBy ComboBox are going to be unique to each form. Neither of these controls are as complete as one would really do for a library application, but both are sufficient to demonstrate the principles.
Function cbSelect_SelectedIndexChanged

1] Change the UserClassDiagnosticLevel to 22. (See Below!)

2] Double click on the select combobox control to generate the proper event triggering for the form. This should automatically create the shell for function cbSelect_SelectedIndexChanged.

3] Add the following documentation and code to function cbSelect_SelectedIndexChanged.

4]   Copy the code from TestModule21 to TestModule22 and make the following changes.

5] Execute the Users Test Module. You will have to do most of the testing manually. Test the Navigation and Letter Button choices for each of the Select Filter options. Double check your results with your database to make sure they function properly. Note that the OrderBy combobox has not yet been coded; thus all order shall be by FullName.
Function cbOrderBy_SelectedIndexChanged

1] Change the UserClassDiagnosticLevel to 23. (See Below!)

2] Double click on the order by combobox control to generate the proper event triggering for the form. This should automatically create the shell for function cbOrderBy_SelectedIndexChanged.

3] Add the following documentation and code to function cbOrderBy_SelectedIndexChanged.

4]   Copy the code from TestModule22 to TestModule23 and make the following changes.

5] Execute the Users Test Module. You will have to do most of the testing manually. Test the Navigation and Letter Button choices for each of the OrderBy option and vary the Select Filter options as you do. Double check your results with your database to make sure they function properly.

Part II-J:
For You To Do

1] This is your opportunity to put into practice what you have learned. Do the Following!
Modify The Books Database Table

1] I have intentionally omitted the Deleted field from the Users Database Table so that you could include it. Using Microsoft SQL Server Management Studio Express, add a Deleted field to your Books database table so that we can tag records for deletion.

2] We would like all of the Books, except Pocket Medicine, to be available (not deleted).

3] Either make the 71 changes manually, or do a query similar to the one below and change Pocket Medicine manually!

Modify The Two ComboBoxes As Follows

    

Modify The Books.cs Text Field Names

1] Change the name the text fields to txtTitle, txtAuthor, txtGenreID, and txtPrice (as is appropriate!)

2] The code to fill this form shall be :

        //================================================================================//
        //                              FillFormVariables                                 //
        //================================================================================//
        //  Purpose: Scatter all of the current datatable fields into the form variables. //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public void FillFormVariables()
        {
            if (dt.Rows.Count != 0)
            {
                lbID.Text = dt.Rows[0]["ID"].ToString();
                txtTitle.Text = dt.Rows[0]["Title"].ToString();
                txtAuthor.Text = dt.Rows[0]["Author"].ToString();
                txtGenreID.Text = dt.Rows[0]["GenreID"].ToString();
                LogRecID = Convert.ToInt32(dt.Rows[0]["ID"]);
                txtPrice.Text = String.Format("{0:N2}", 
                                Convert.ToInt32(dt.Rows[0]["Price"]));
            }
            else
                MessageBox.Show("Unable To Fill Form Variables - Query Unsuccessful!");
        }
Double-Click On All Books.cs Buttons

1] Double-Click on all of the Books.cs buttons and comboboxes to create events.
Add Tool Tips To Book.cs

1] Add ToolTips to the Books.cs
Add & Modify Code

1] Gradually phase in the following items from Users.cs to Books.cs. I included my test code (and made modifications to make it work), but this is optional. Make sure that the Books.cs includes the following:

namespace LibraryApp
{
    public partial class Books : Form
    {
        MainMenu pf;                            // Parent Reference To MainMenu.cs
        SqlConnection Conn;                     // Connection Object To Be Used For All Book Queries
        DataTable dt = new DataTable();         // DataTable Holding The Current View Record
        DataTable LogRecKeys = new DataTable(); // DataTable of ID's Used To Navigate The Query Set

        int LogRecNo;                           // Logical Record Pointer 
        int NoLogRecs;                          // No of Logical Records in LogRecKeys
        int LogRecID;                           // ID Of Current Record

        String PrimaryKey;                      // Primary Key For The Class Associated With This Form
        String TableName;                       // Database Table Associated With This Form
        String OrderBy;                         // Current Sort Order For This Table
        String WhereClause;                     // Primary Key For The Class Associated With This Form

        int AuthorSelectedSelectIndex = 0;        // Stores The Author Successful Filter To Reset

        //================================================================================//
        //                                    Constructors                                //
        //================================================================================//
        //  Purpose: Blank Constructor and one which accepts information from the Parent  //
        //           MainMenu.                                                            //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public Books()
        {
            InitializeComponent();
        }

        public Books(MainMenu Parent)
        {
            InitializeComponent();
            pf = new MainMenu();
            pf = Parent;
        }

        //================================================================================//
        //                                 Form Load Events                               //
        //================================================================================//
        //  Purpose: Do all that is necessary to load and initialize this basic form.     //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                   Environment : .NET 2005/8  //
        //        Date : 3/1/2008                                 Language : C#           //
        //================================================================================//
        private void Books_Load(object sender, EventArgs e)
        {
            SQLAuthenticationConnection("Net1\\SQLExpress", "LibraryApp", "student", "student");
            TableName = "Books";
            PrimaryKey = "ID";
            OrderBy = "Title";
            WhereClause = "(Deleted != 'T')";
            LoadToolTips();
            ReadQueryLogRecKeys();
            LoadFirstRecord();
        }


        //================================================================================//
        //                                CreateParams                                    //
        //================================================================================//

        //================================================================================//
        //                        WindowsAuthenticationConnection                         //
        //================================================================================//

        //================================================================================//
        //                           SQLAuthenticationConnection                          //
        //================================================================================//

        //================================================================================//
        //                                 FillDataTable                                  //
        //================================================================================//

        //================================================================================//
        //                                  TableLength                                   //
        //================================================================================//

        //================================================================================//
        //                                 ReadIDRecord                                   //
        //================================================================================//
 
        //================================================================================//
        //                                 DisplayRecord                                  //
        //================================================================================//

        //================================================================================//
        //                              FillFormVariables                                 //
        //================================================================================//
        //  Purpose: Scatter all of the current datatable fields into the form variables. //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public void FillFormVariables()
        {
            if (dt.Rows.Count != 0)
            {
                lbID.Text = dt.Rows[0]["ID"].ToString();
                txtTitle.Text = dt.Rows[0]["Title"].ToString();
                txtAuthor.Text = dt.Rows[0]["Author"].ToString();
                txtGenreID.Text = dt.Rows[0]["GenreID"].ToString();
                LogRecID = Convert.ToInt32(dt.Rows[0]["ID"]);
                txtPrice.Text = String.Format("{0:N2}", 
                                Convert.ToInt32(dt.Rows[0]["Price"]));
            }
            else
                MessageBox.Show("Unable To Fill Form Variables - Query Unsuccessful!");
        }

 
        //================================================================================//
        //                                 LoadToolTips                                   //
        //================================================================================//
        //  Purpose: Create ToolTip help for each and every button on the Books form.     //
        //                                                                                //
        //  Written By : Dr. Thomas E. Hicks                    Environment : .NET 2005/8 //
        //        Date : xx/xx/xxxx                                Language : C#          //
        //================================================================================//
        public void LoadToolTips()
        {
            toolTip1.SetToolTip(btnA, "Go To The Title Book Whose Author Name Starts With An 'A'");
            toolTip1.SetToolTip(btnB, "Go To The Title Book Whose Author Name Starts With An 'B'");
            toolTip1.SetToolTip(btnC, "Go To The Title Book Whose Author Name Starts With An 'C'");
            toolTip1.SetToolTip(btnD, "Go To The Title Book Whose Author Name Starts With An 'D'");
            toolTip1.SetToolTip(btnE, "Go To The Title Book Whose Author Name Starts With An 'E'");
            toolTip1.SetToolTip(btnF, "Go To The Title Book Whose Author Name Starts With An 'F'");
            toolTip1.SetToolTip(btnG, "Go To The Title Book Whose Author Name Starts With An 'G'");
            toolTip1.SetToolTip(btnH, "Go To The Title Book Whose Author Name Starts With An 'H'");
            toolTip1.SetToolTip(btnI, "Go To The Title Book Whose Author Name Starts With An 'I'");
            toolTip1.SetToolTip(btnJ, "Go To The Title Book Whose Author Name Starts With An 'J'");
            toolTip1.SetToolTip(btnK, "Go To The Title Book Whose Author Name Starts With An 'K'");
            toolTip1.SetToolTip(btnL, "Go To The Title Book Whose Author Name Starts With An 'L'");
            toolTip1.SetToolTip(btnM, "Go To The Title Book Whose Author Name Starts With An 'M'");
            toolTip1.SetToolTip(btnN, "Go To The Title Book Whose Author Name Starts With An 'N'");
            toolTip1.SetToolTip(btnO, "Go To The Title Book Whose Author Name Starts With An 'O'");
            toolTip1.SetToolTip(btnP, "Go To The Title Book Whose Author Name Starts With An 'P'");
            toolTip1.SetToolTip(btnQ, "Go To The Title Book Whose Author Name Starts With An 'Q'");
            toolTip1.SetToolTip(btnR, "Go To The Title Book Whose Author Name Starts With An 'R'");
            toolTip1.SetToolTip(btnS, "Go To The Title Book Whose Author Name Starts With An 'S'");
            toolTip1.SetToolTip(btnT, "Go To The Title Book Whose Author Name Starts With An 'T'");
            toolTip1.SetToolTip(btnU, "Go To The Title Book Whose Author Name Starts With An 'U'");
            toolTip1.SetToolTip(btnV, "Go To The Title Book Whose Author Name Starts With An 'V'");
            toolTip1.SetToolTip(btnW, "Go To The Title Book Whose Author Name Starts With An 'W'");
            toolTip1.SetToolTip(btnX, "Go To The Title Book Whose Author Name Starts With An 'X'");
            toolTip1.SetToolTip(btnY, "Go To The Title Book Whose Author Name Starts With An 'Y'");
            toolTip1.SetToolTip(btnZ, "Go To The Title Book Whose Author Name Starts With An 'Z'");

            toolTip1.SetToolTip(btnNextRight, "Go To The Next Book!");
            toolTip1.SetToolTip(btnPreviousRight, "Go To The Previous Book!");
            toolTip1.SetToolTip(btnTopRight, "Go To The Title Book!");
            toolTip1.SetToolTip(btnBottomRight, "Go To The Author Book!");
            toolTip1.SetToolTip(btnNextLeft, "Go To The Next Book!");
            toolTip1.SetToolTip(btnPreviousLeft, "Go To The Previous Book!");
            toolTip1.SetToolTip(btnTopLeft, "Go To The Title Book!");
            toolTip1.SetToolTip(btnBottomLeft, "Go To The Author Book!");
        }

        //================================================================================//
        //                                  QueryTableLength                              //
        //================================================================================//

        //================================================================================//
        //                               ReadQueryLogRecKeys                              //
        //================================================================================//

        //================================================================================//
        //                               DisplayLogRecKeys                                //
        //================================================================================//

        //================================================================================//
        //                              ReadQueryRecord                                   //
        //================================================================================//

        //================================================================================//
        //                                 ReadCurrentRecord                              //
        //================================================================================//
 
        //================================================================================//
        //                                 LoadNextRecord                                 //
        //================================================================================//

        //================================================================================//
        //                              LoadPreviousRecord                                //
        //================================================================================//

        //================================================================================//
        //                               LoadFirstRecord                                  //
        //================================================================================//

        //================================================================================//
        //                               LoadLastRecord                                   //
        //================================================================================//

        //================================================================================//
        //                                   SetLogRecNo                                  //
        //================================================================================//

        //================================================================================//
        //                            LoadFirstLetterRecord                               //
        //================================================================================//

        //================================================================================//
        //                         cbSelect_SelectedIndexChanged                          //
        //================================================================================//

        //================================================================================//
        //                                  cbOrderBy                                     //
        //================================================================================//

2] Compile your program regularly as you add the functions above.

3] If you copied the test functions and tested your functions individually, you will have some degree of confidence in your code; you will have make modifications to get the code to compile.

Test Books.cs
 
1] Test the Navigation Buttons, The Letter Buttons, and the Controls. Look at your database to confirm proper functionality. Test the tool tips.