Tutorial: MSSQL- Library App Form Control
Without GUI Connections
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
The purpose of this tutorial is to show how to configure a Windows Application Form, using Visual Studio without using delegates, to process next, previous, delete, top, bottom, order by, and alphabetical search button controls..
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-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
1] You might want to download and add the following database on the server; call the database LibraryApp. You can use import from the Microsoft Access database
Tutorial: MSSQL-Server-From-2003-Access-DB\MSSQL-Server-From-2003-Access-DB
Tutorial: MS-SQL-Server-From-2007-Access-DB\MS-SQL-Server-From-2007-Access-DB
or you might want to try to import the LibraryApp database directly into Microsoft SQL Server using Microsoft SQL Server Management Studio Express.
Tutrial: MSSQL Import-Export-Backup-MSSQL-Database
1] Our database is shown, below, via Microsoft SQL Server Management Studio Express. Note that our server, to which we shall connect, is NET1\SQLEXPRESS (See Below!)
2] There are a number of good ways to connect. We are going to use two connection string options. See the link below for an Internet link that most of us should keep bookmarked.
3] Since we shall connect with both Windows and SQL server authentication, it is important that you configure your SQL Server to authenticate both ways. An easy way to do this is to use Microsoft SQL Server Management Studio Express (Right mouse click on Net1\Express and select properties-select Security page). (See Below!)
4] Use Microsoft SQL Server Management Studio Express to create a user, called student whose password is student. (See Below!)
5] Double-click on the student account; select User Mappings. Use Microsoft SQL Server Management Studio Express to enable the student user to use the LibraryApp database. (See Below!)
6] Continue to use Microsoft SQL Server Management Studio Express. Right-mouse click on the LibraryApp database and select Properties. Select the Permissions Page. Add the student user if necessary. The student user certainly does not need all of the permissions, but for the sake of simplicity (and because I don't want this to be a tutorial on SQL Server), grant the student user all permissions. (See Below!)
7] SQL user student
should now be able to use password student to authenticate with the LibraryApp
database throughout this tutorial.
1] My students have been asked to complete some basic forms to support this tutorial. Firs is LibraryApp.cs, which uses a MenuStrip to control the main application. The lab description may be seen below. LibraryApp.cs, is to look something like the following:
MSSQL-Library-App\MSSQL-Library-App-1 Lab
2] They were to create a basic Administrative login form called
Login.cs. It is to look something like the following:
3] They
were to also create a non-functional ViewUser vorm called Users.cs that
looks something like the following: [We realize that the MajorID and
Administrator should eventually be combo boxes and that the password might
eventually be replaced by #'s. To get rid of the close box, add the following function right below the
function public
Users()
private const int CS_NOCLOSE = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
return cp;
}
}
4] They were supposed to place all of the fields, from the Users table, in the first tab frame of the Users form.
5] Button Close should hide the Users Form. Their Users form was to include all of the buttons seen on the form above and all of the combo boxes seen on that form.
6] A collection of images was made available to the students. They included: Top
,
Bottom 
, Next
,
and Previous 
.
Top
is to navigate to the first logical record.
Bottom
Next
is to navigate to the next logical record - wrap around if necessary.
Previous
is to navigate to the previous logical record - wrap around if
necessary.
7] Below you can see, in turquoise, the names I have associated with the various TextBoxes, lLabels, and ComboBoxes on my Users.cs form.
1] The purpose of this tutorial is to connect to our database, without guis, and add functionality to many of the buttons on the Users.cs form.
1] You must include the System.Data.SqlClient Namespace at the top of the Users.cs form.
1] If you are working on your SQL Server, using Windows Authentication will probably be the easiest.
2] Using the mouse, double-click someplace on your users form that has no objects; this will create a User_Load function that will be fired each time our form is loaded. We are going to place our temporary testing code in that function.
3] In accordance with ConnectionStrings.com, let us define our Connection String to load the Library database on Net1\SQLExpress. Note the need to use the \\ escape sequence in the connection string. Create a string object, called ConnectionString, and fill it in accordance with your SQL Server Name. Add the line below to your form and test it; your form should load, but there will be no evidence that the database stuff is working yet. (See Below!)
String ConnectionString = "Data Source=net1\\SQLExpress;Integrated Security=True;" +
"Initial Catalog=LibraryApp";
4] Create a connection object, called Conn. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
5] Create a string object, called Query. We could read all of the
User records into a record set and cycle through them. If we had thousands of
records, this would take both time and memory. If this were a single user
application with a small number of records, this might be ok.
We are designing an application strategy that will be extremely fast for small
and large data sets. We are going to read only a single record at a time. We are
about to design a record to read the Crisanto record (ID = 1) from our
Users table. Add the line below to your form and test it; your form should load,
but there will be no evidence that the database stuff is working yet. (See
Below!)
6] We have established a connection and created a query command. We are now
going to create a data adapter object, called da.
The SqlDataAdapter holds the SQL commands
and connection object for reading and writing data. We are going to initialize
it by passing our SQL Query and our Connection object. The code above creates a new SqlDataAdapter, da. The SQL select
statement specifies what data will be read into a DataSet. The connection
object, Conn, has already been instantiated, but not opened. As you
can see in the diagram above, It is the SqlDataAdapter's responsibility to open
and close the connection when Fill and Update method are called. Add the line below to your form and test it; your form should load, but there
will be no evidence that the database stuff is working yet.
7] The results of our query command are going to be returned to the data
table in our application. We are now going to create a data table object, called
dt. Add the line below to your form and test it; your form should load,
but there will be no evidence that the database stuff is working yet. (See
Below!)
8] Once we have created the data table, we can use the data adapter to
fill it with data in accordance with our diagram above. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
9] We now have data and can display it! Add the line below to your form
and test it! 10] User_Load can be seen below!SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
Your form should load and the message box should display the following: (See Below!)
1] In accordance with ConnectionStrings.com, let us define our Connection String
to load the Library database on Net1\SQLExpress for user student whose
password is student. Note the need to use the \\ escape sequence in
the connection string. Create a string object, called ConnectionString,
and fill it in accordance with your SQL Server Name. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)String ConnectionString = "Data Source=NET1\\SQLEXPRESS;Initial Catalog=LibraryApp;" +
"Persist Security Info=True;User ID=student; Pwd=student";
2] Your form should once again load and the message box should display the following: (See Below!)
3] Your Users.cs form should work equally well. The compelte code is shown below!
1] As we begin to modularize our code, we will find that we are going to use the Conn object and the dt object in a number of modules. Rather than create multiple connections, or pass it, let us make them available to the entire form. Declare Conn and dt as shown below. Add the other variables as well; we shall use them later.
public partial class Users : Form
{
long RecNo, NoRecords;
SqlConnection Conn;
DataTable dt;
2] A number of the functions in this application can be re-used in multiple applications if generalized a bit. Include the code for functions WindowsAuthenticationConnection and SQLAuthenticationConnection immediately below the declarations for Conn and dt (above)..
3] Test function WindowsAuthenticationConnection with the code below. Substitute your SQLServer!
private void Users_Load(object sender, EventArgs e)
{
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
}
3] Test function SQLAuthenticationConnection with the code below. Substitute your SQLServer!
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
}
1] Using your mouse, drag a ListBox from the ToolBox off to the side of your Users form. Using a MessageBox to display very much is a hassle. Name the ListBox lbTrace.
1] We are generally going to have only one record in our DataTable dt; it will be located at Function DisplayRecord shall be used to display the data table field name and content of the the first, and only, record in our DataTable. It is generic and will work for other tables as well. Add the following utility function to your application.
2] Test Function DisplayRecord with the following:
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
dt = new DataTable();
da.Fill(dt);
RecNo = 1;
NoRecords = 56;
DisplayRecord("Contents Of Record (ID=1)");
}
3] The output in txtTrace should be:
4] As we move to the top, next, previous, and bottom records, it is essential that we be able to display the DataTable information.
1] Add generic Function TableLength to your Users.cs form. If we are going to create a button to go to the Next Record, it will be essential that we know how many records are in the table. Note that this number might continually change in a multi-user (concurrent) database application.
2] Test Function TableLength with the following:
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
dt = new DataTable();
da.Fill(dt);
RecNo = 1;
NoRecords = TableLength("Users");
DisplayRecord("Contents Of Record (ID=1)");
}
3] The output in txtTrace is once again as follows:
4] Note that the value in the CNoRecords variable was calculated this time; in the previous section, it was simply assigned..
1] Add Function FillFormVariables to your Users.cs form. The responsibility of FillFormVariables is to fill the User Form variables with the data from record 0 in the DataTable Record. This function is non-generic, but there will be a similar function on other forms, such as Books.cs.
2] Test Function FillFormVariables with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
DisplayRecord("Contents Of Record (ID=2)");
FillFormVariables();
}
3] The output should be as follows:
1] Add Function FillDataTable to your Users.cs form. The responsibility of FillFormVariables explicitly return a DataTable that has been filled with the passed Query.
2] Test Function FillDataTable with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
}
3] The output should be as follows:
1] In accordance with our other tutorials, the btnClose should be as follows:
1] In accordance with our other tutorials, the ViewUser MenuStrip control seen below
should open your User form with the Show method.
2] Should you choose to close our exiting user form by clicking the X in the top right corner
you would get an error message next time you tried to open it from the main Application form. Even though I have included this code in previous tutorials, I am going to include the code it agian below. Add the code to remove the close functionality from your User form.
//================================================================================//
// CreateParams //
//================================================================================//
// Purpose: Block of code to disable the close box on a form and yet control //
// miniize and maximize functionality. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private const int CS_NOCLOSE = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
return cp;
}
}
3] I would also add this code to the LibraryApp.cs form.
1] Add the diagnostic Function DisplayFormVariables to your Users.cs form. This function will send a copy of all Form Variables to txtTrace. It should prove to be a valuable diagnostic routine when we begin to have problems with our navigation functions later in this tutorial.
//================================================================================//
// DisplayFormVariables //
//================================================================================//
// Purpose: Display the Form Variables in the listbox called lbTrace. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void DisplayFormVariables()
{
lbTrace.Items.Add("-----------------------------------------------------");
lbTrace.Items.Add(" Form Variables ");
lbTrace.Items.Add("-----------------------------------------------------");
lbTrace.Items.Add("TableName.........= " + TableName.ToString());
lbTrace.Items.Add("PrimaryKey........= " + PrimaryKey.ToString());
lbTrace.Items.Add("OrderBy...........= " + OrderBy.ToString());
lbTrace.Items.Add("NoRecords.........= " + NoRecords.ToString());
lbTrace.Items.Add("RecNo.............= " + RecNo.ToString());
lbTrace.Items.Add("OrderBy...........= " + OrderBy.ToString());
lbTrace.Items.Add("txtFirst..........= " + txtFirst.Text);
lbTrace.Items.Add("txtMI.............= " + txtMI.Text);
lbTrace.Items.Add("txtLast...........= " + txtLast.Text);
lbTrace.Items.Add("txtMajorID........= " + txtMajorID.Text);
lbTrace.Items.Add("txtAdministrator..= " + txtAdministrator.Text);
lbTrace.Items.Add("txtUserName.......= " + txtUserName.Text);
lbTrace.Items.Add("txtPassword...... = " + txtFirst.Text);
lbTrace.Items.Add("dt->[ID]..........=" + dt.Rows[0]["ID"].ToString());
lbTrace.Items.Add("dt->[Deleted].....=" + dt.Rows[0]["Deleted"].ToString());
lbTrace.Items.Add("dt->[FullName]....=" + dt.Rows[0]["FullName"].ToString());
lbTrace.Items.Add("-----------------------------------------------------");
}
2] Test Function DisplayFormVariables with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
DisplayFormVariables();
}
3] The output should be as follows:
1] Using your mouse, drag a ToolTip from the ToolBox to your User.cs form. Name the ToolTip toolTip1.
2] Add function LoadToolTips to your User.cs form.
public void LoadToolTips()
{
toolTip1.SetToolTip(btnA, "Go To The First User Whose Last Name Starts With An 'A'");
toolTip1.SetToolTip(btnB, "Go To The First User Whose Last Name Starts With An 'B'");
toolTip1.SetToolTip(btnC, "Go To The First User Whose Last Name Starts With An 'C'");
toolTip1.SetToolTip(btnD, "Go To The First User Whose Last Name Starts With An 'D'");
toolTip1.SetToolTip(btnE, "Go To The First User Whose Last Name Starts With An 'E'");
toolTip1.SetToolTip(btnF, "Go To The First User Whose Last Name Starts With An 'F'");
toolTip1.SetToolTip(btnG, "Go To The First User Whose Last Name Starts With An 'G'");
toolTip1.SetToolTip(btnH, "Go To The First User Whose Last Name Starts With An 'H'");
toolTip1.SetToolTip(btnI, "Go To The First User Whose Last Name Starts With An 'I'");
toolTip1.SetToolTip(btnJ, "Go To The First User Whose Last Name Starts With An 'J'");
toolTip1.SetToolTip(btnK, "Go To The First User Whose Last Name Starts With An 'K'");
toolTip1.SetToolTip(btnL, "Go To The First User Whose Last Name Starts With An 'L'");
toolTip1.SetToolTip(btnM, "Go To The First User Whose Last Name Starts With An 'M'");
toolTip1.SetToolTip(btnN, "Go To The First User Whose Last Name Starts With An 'N'");
toolTip1.SetToolTip(btnO, "Go To The First User Whose Last Name Starts With An 'O'");
toolTip1.SetToolTip(btnP, "Go To The First User Whose Last Name Starts With An 'P'");
toolTip1.SetToolTip(btnQ, "Go To The First User Whose Last Name Starts With An 'Q'");
toolTip1.SetToolTip(btnR, "Go To The First User Whose Last Name Starts With An 'R'");
toolTip1.SetToolTip(btnS, "Go To The First User Whose Last Name Starts With An 'S'");
toolTip1.SetToolTip(btnT, "Go To The First User Whose Last Name Starts With An 'T'");
toolTip1.SetToolTip(btnU, "Go To The First User Whose Last Name Starts With An 'U'");
toolTip1.SetToolTip(btnV, "Go To The First User Whose Last Name Starts With An 'V'");
toolTip1.SetToolTip(btnW, "Go To The First User Whose Last Name Starts With An 'W'");
toolTip1.SetToolTip(btnX, "Go To The First User Whose Last Name Starts With An 'X'");
toolTip1.SetToolTip(btnY, "Go To The First User Whose Last Name Starts With An 'Y'");
toolTip1.SetToolTip(btnZ, "Go To The First User Whose Last Name Starts With An 'Z'");
toolTip1.SetToolTip(btnSearch, "Launch Search User Dialog!");
toolTip1.SetToolTip(btnAdd, "Add A New User!");
toolTip1.SetToolTip(btnDelete, "Delete This User!");
toolTip1.SetToolTip(btnUnDelete, "Undelete Users!");
toolTip1.SetToolTip(btnEdit, "Edit This User!");
toolTip1.SetToolTip(btnPrint, "User Reports & PrintOut Options!");
toolTip1.SetToolTip(btnNextRight, "Go To The LoadNextRecord User!");
toolTip1.SetToolTip(btnPreviousRight, "Go To The LoadPreviousRecord User!");
toolTip1.SetToolTip(btnTopRight, "Go To The First User!");
toolTip1.SetToolTip(btnBottomRight, "Go To The Last User!");
toolTip1.SetToolTip(btnNextLeft, "Go To The LoadNextRecord User!");
toolTip1.SetToolTip(btnPreviousLeft, "Go To The LoadPreviousRecord User!");
toolTip1.SetToolTip(btnTopLeft, "Go To The First User!");
toolTip1.SetToolTip(btnBottomLeft, "Go To The Last User!");
toolTip1.SetToolTip(btnClose, "Close This Form!");
}
3] Test Function LoadToolTips with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
DisplayFormVariables();
LoadToolTips();
}
1] Suppose we do the Query :
Select * From Users. The records will be displayed in Physical Order. The users has an auto incremented ID field that helps to confirm this in the snapshot below; note that this primary key helps to confirm the order. If one were to cycle through the records in which the ID = 1, ID =2, ID =3, etc <== this would be referred to as Physical Order.
2] Suppose we do the Query : Select * From Users Order By FullName. The records will be displayed in Logical Order. Note that this order is different than the Physical Order above. If one were to cycle through the records in which the ID = 8, ID = 9, ID = 11, etc but the critical issue here is that cycling through the records would provide Agoston, Akers, Aldana, etc. (because of the Order By) <== this would be referred to as logical order.
3] Most applications display data in some type of Logical Order. It is critical that our Next and Previous buttons cycle through our records in Logical Order.
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.
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] The table must have a Primary Key. The PrimaryKey for the Users table is ID.
3] Below you can see the results of the Query =
SELECT TOP 5 *
FROM Users
WHERE [ID] NOT IN
(SELECT TOP 3 [ID]
FROM [Users]
ORDER BY FullName)
ORDER BY FullName
4] This much more complicated MSSQL Query would also display 5 records in logical order - starting with the RecNo 3. The first record is RecNo = 0.
5] The Query to display the fifth logical record, RecNo 4, is seen below.
1] The question becomes, "how do we extrapolate what we have seen above into a usable generic function?"
2] In the first FillTableWithRecord function I wrote, I passed four arguments:
public DataTable FillTableWithRecord (long RecNo, String TableName, String PrimaryKey, String OrderBy)
3] The user can use the OrderBy ComboBox to control the display order on the form. his function was effective and I called it many times in the User form, but I kept passing it the same information over and over. In order to simplify this tutorial, I have declared seven variables that shall be available to all of the functions on the Users form.
public partial class Users : Form
{
String TableName, PrimaryKey, OrderBy;
long RecNo, NoRecords;
SqlConnection Conn;
DataTable dt;
4] My Users is going to eventually do the following:
public Users()
{
InitializeComponent(); // Create the Users form
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp"); // Connect to the DB
TableName = "Users"; //Identify Users as the primary LibraryApp table for this form
PrimaryKey = "ID"; // Identify ID as the primary key for the Users Table
OrderBy = "FullName"; // Identify FullName as the initial logical order in which to cycle through the records
RecNo = 0; // Set the current record number = 0
NoRecords = TableLength(TableName); // Set the NoRecords = 56 so that we know the end point
LoadFirstRecord(); // Read record 0 from the table and fill the form fields
LoadToolTips(); // Load the Tool Tips for this form
}
5] Because the TableName, PrimaryKey, and OrderBy are globally available, I have written a simplified FillTableWithRecord function that is passed only one argument:
public DataTable FillTableWithRecord (long RecNo)
6] Add function FillTableWithRecord to your application
7] Test Function FillTableWithRecord with the following:
public Users()
{
InitializeComponent();
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
FillTableWithRecord(0);
FillFormVariables();
LoadToolTips();
}
private void Users_Load(object sender, EventArgs e)
{
}
8] Test Function FillTableWithRecord with the following:
1] The purpose of LoadNextRecord is to load the next undeleted logical record. When we get to last logical record, it will be the responsibility of LoadNextRecord to wrap around and proceed to the next undeleted record starting from record 0.
2] NoRecords is repeatedly filled because any user on a concurrent system might add new records at any time. Add function LoadNextRecord to your project.
3] Add a call
to LoadNextRecord for both btnNextRight
and btnNextLeft. (See Below!)
4]
Run your application and cycle all the way through the records. Open the Users table and set Deleted to T/t for Steven, Michael, and John. (See Below!)
5] Cycle all the way through the records again; note that 2, 3, and 5 are skipped.
1] The purpose of LoadPreviousRecord is to load the previous undeleted logical record. When we get to first logical record, it will be the responsibility of LoadPreviousRecord to wrap around and proceed to the previous undeleted record starting from last logical record.
2] NoRecords is repeatedly filled because any user on a concurrent system might add new records at any time. Add function LoadPreviousRecord to your project.
3] Add a call
to LoadPreviousRecord for both btnPreviousRight
and
btnPreviousLeft. (See Below!)
4]
Run your application and cycle all the way through the records; note that 2, 3, and 5 are still skipped.1] The purpose of LoadFirstRecord is to load the first undeleted logical record. If the first record is deleted, then proceed to the next record.. Add function LoadFirstRecord to your project.
3] Add a call
to LoadFirstRecord for both btnTopRight
and btnPreviousTopLeft.
1] The purpose of LoadLastRecord is to load the last undeleted logical record. If the last record is deleted, then proceed to the previoous record.. Add function LoadLastRecord to your project.
3] Add a call
to LoadLastRecord for both btnBottomRight
and
btnBottomLeft. (See Below!)
1] Filters and OrderBy increase the difficulty level of the navigation buttons we have completed thus far. The purpose of SetRecNo is to adjust the RecNo appropriately as one changes the OrderBy ComboBox.Resetting the RecNo properly for ordered lists makes this the most challenging function in the tutorial.
2] Add function SetRecNo to your project.
1] The purpose of LoadFirstLetterRecord is to load the first undeleted logical record whose FieldName begins with the Letter. If that record is deleted or not there then proceed to the next record; wrap around if necessary. Note that this function uses the SetRecNo function above.
2] The process of searching for the first record whose starting letter matches the FullName, it is necessary to temporarily change the order to the FullName; the user's chosen order is returned upon completion.
3] Add function LoadFirstLetterRecord to your project.
4
] Add a call to LoadFirstLetterRecord for each and every one of the 26 buttons below. (See Below!)
5] Test each of the 26 buttons.
1] Double click on your OrderBy combo box. The ComboBox to change the OrderBy is pretty straight forward. Include the code in your application..
2] Test your application.
1] Adding filters is an assignment left to my students. Good Luck!
