Posts by cgeier

Some of the sample data didn't get posted in the proper place. The 30 minute cut-off didn't allow me to edit it further. Here's the sample data with it's table:

AppPermission:

AppGroup:

AppUser:

AppGroupPermission:

AppUserGroup:

Sample SQL Queries:

To get permissions for "user2":

SELECT AppGroupPermission.groupName, AppGroupPermission.permissionName 
FROM AppGroupPermission
INNER JOIN AppUserGroup
ON AppGroupPermission.groupName = AppUserGroup.groupName
WHERE AppUserGroup.userId = 'user2'

To get permissions for "user2" along with the description of the permissions:

SELECT t2.groupName, t2.permissionName, AppPermission.description 
FROM AppPermission
INNER JOIN
(SELECT AppGroupPermission.groupName, AppGroupPermission.permissionName 
FROM AppGroupPermission
INNER JOIN AppUserGroup 
ON AppGroupPermission.groupName = AppUserGroup.groupName
WHERE AppUserGroup.userId = 'user2') t2 
ON t2.permissionName = AppPermission.name

Here's how to create a table programmatically:

Need to add Imports System.Data.SqlClient

    Public Sub AppPermissionTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE AppPermission (name nvarchar(50) NOT NULL "
                sqlText += "CONSTRAINT PK_AppPermission_name PRIMARY KEY, "
                sqlText += "description nvarchar(100))"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                System.Windows.Forms.MessageBox.Show("Table created: AppPermission", "Table Created.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End Using

        Catch ex As SqlClient.SqlException
            System.Windows.Forms.MessageBox.Show("Error:: AppPermissionTbl: " & ex.Message, "Error - Create Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Error:: AppPermissionTbl: " & ex.Message, "Error - Create Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

There are documents attached below (in rich text format) that contain …

My previous post used some reserved words. Also, I've worked on the db structure a little more.

The table names are as follows:

• AppPermission
• AppGroup
• AppUser
• AppGroupPermission
• AppUserGroup

AppPermission:

Version 1:

CREATE TABLE AppPermission (name nvarchar(50) NOT NULL
CONSTRAINT PK_AppPermission_name PRIMARY KEY,
description nvarchar(100))

AppGroup:

CREATE TABLE AppGroup (name nvarchar(50) NOT NULL
CONSTRAINT PK_AppGroup_name PRIMARY KEY,
description nvarchar(100))

AppUser:

CREATE TABLE AppUser (id nvarchar(25) NOT NULL
CONSTRAINT PK_AppUser_id PRIMARY KEY,
firstName nvarchar(50) NOT NULL,
lastName nvarchar(50) NOT NULL,
password nvarchar(25))

AppGroupPermission:

CREATE TABLE AppGroupPermission (groupName nvarchar(50) NOT NULL,
permissionName nvarchar(50) NOT NULL,
CONSTRAINT PK_groupName_permissionName
PRIMARY KEY(groupName, permissionName), 
CONSTRAINT FK_AppGroupPermission_AppGroup_groupName 
FOREIGN KEY (groupName) 
REFERENCES AppGroup(name) 
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT FK_AppGroupPermission_AppPermission_permissionName 
FOREIGN KEY (permissionName) 
REFERENCES AppPermission(name) 
ON DELETE CASCADE ON UPDATE CASCADE)

AppUserGroup:

CREATE TABLE AppUserGroup (userId nvarchar(25) NOT NULL,
groupName nvarchar(50) NOT NULL,
CONSTRAINT PK_userId_groupName
PRIMARY KEY(userId, groupName), 
CONSTRAINT FK_AppUserGroup_AppGroup_groupName 
FOREIGN KEY (groupName) 
REFERENCES AppGroup(name) 
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT FK_AppUserGroup_AppUser_userId 
FOREIGN KEY (userId) 
REFERENCES AppUser(id) 
ON DELETE CASCADE ON UPDATE CASCADE)

Store all of the menu names that will require access permissions in a table.

Create table: Menu
Column: name (PK)

or

Column: id (PK)
Column: name

Create table: Group
Column: name (PK)
Column: menuPermissions (PK); FK references Menu.name

Create table: UserPermission
Column: id (PK); FK references User.id
Column: groupName (PK); FK references Group.name

Create table: User
Column: id (PK)
Column: firstName
Column: lastName
Column: password

Add a permission for each menu item--some of the items you may be able to group together and some menu items may be dependent on others. If someone doesn't have access to the file menu, they shouldn't have access to the edit menu under file menu. The following is not tested:

Dim FileMenuAccess as Boolean = False
Dim FileEditMenuAccess as Boolean = False
Dim FileBackupMenuAccess as Boolean = False
          ...

If FileMenuAccess = True Then
   'enable file menu

   If FileEditMenuAccess = True Then
      'enable file-edit menu
   Else
      'disable file-edit menu
   End If
Else
   'disable file menu
End If

Private Sub GetMenuPermissions()
   'get menu permissions for user from database

End Sub

Or

You could use a dictionary:

Dim menuDict as New Dictionary(Of String, Boolean)

If menuDict.Contains("FileMenuAccess") Then
   'enable file menu

   If menuDict.Contains("FileEditMenuAccess") Then
      'enable file-edit menu

   Else
      'disable file-edit menu

   End If
Else
   'disable file menu

End If

Private Sub GetMenuPermissions()
   'get menu permissions for user from database
   'add each menu permission to menuDict

End Sub

Check your recycle bin to see if it is there. Otherwise you may be able to recover the file using data recovery software. You will want to stop using the drive that contains the data until you recover the file, as further usage may overwrite the file that you are trying to recover.

Also, this is not really a vb issue, but rather a deleted file / data recovery issue.

In the future, ensure that you have a backup solution in place so that your files are backed up.

Ensure that the "Shutdown" option is not configured to restart the computer.

• Right click the "Start button" (Windows globe)
• Select "Properties"
• Click "Start Menu"
• Ensure "Power Button Action" is "Shutdown" (not restart)

Also, ensure that your program is compiled only for x86 (not anyCPU). I don't believe that there is a 64-bit version of JET.

Get information about the computer (architecture and OS version):

            if (System.Environment.Is64BitOperatingSystem == true)
            {
                Console.WriteLine("Processor Architecture: 64-bit");
            }//if
            else
            {
                Console.WriteLine("Processor Architecture: 32-bit");
            }

            Console.WriteLine("OS Version: " + System.Environment.OSVersion.ToString());

Alternatively,

In a "cmd.exe" window,

OS version:
* reg query "HKLM\Software\Microsoft\Windows NT\CurrentVersion" /v ProductName

Architecture:
set | find /i "architecture"

Check the event logs on the server to see if there are any errors. Also add the following Catch (OleDbException e).

Also check that "Jet 4.0" is installed (properly).

• reg query "HKCR\CLSID{dee35070-506b-11cf-b1aa-00aa00b8de95}\OLE DB Provider"

• reg query "HKCR\CLSID{dee35070-506b-11cf-b1aa-00aa00b8de95}\InprocServer32"

• reg query "HKCR\CLSID{dee35070-506b-11cf-b1aa-00aa00b8de95}\ProgID"

• reg query "HKLM\Software\ODBC\ODBCINST.INI\ODBC Drivers" | find "Microsoft Text Driver"

Adapted from here.

Are you running the actual program on the server, or running the program on your computer and trying to access the file on the server?

It seems like a no-brainer to develop using the .NET version that you plan on using to run the program, because new features are added in newer versions. Either install .NET 4.5 on the server, or install the .NET version you plan on using to run the program, on your development machine and use that version for your program (v4.0).

Where is the definition for AMSCONN?

Using statement:

You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

Something like the following would work:

    'keeps track of selected rows
    Private selectedRowsDict As New Dictionary(Of Integer, String)

DataGridView MouseClick event:

    Private Sub DataGridView1_MouseClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
        'allows for row de-selection by toggling
        'between row being selected and de-selected
        If selectedRowsDict.ContainsKey(DataGridView1.CurrentRow.Index) Then
            'deselect row
            selectedRowsDict.Remove(DataGridView1.CurrentRow.Index)
        Else
            'select row
            selectedRowsDict.Add(DataGridView1.CurrentRow.Index, "Selected")
        End If

        'loop through all rows in DataGridView
        'de-select rows that have been de-selected
        'select rows that have been selected
        For rowNum As Integer = 0 To DataGridView1.RowCount - 1
            If selectedRowsDict.ContainsKey(rowNum) Then
                DataGridView1.Rows(rowNum).Selected = True
            Else
                DataGridView1.Rows(rowNum).Selected = False
            End If
        Next
    End Sub

How are you identifying which row the user wants to select? ComboBox? TextBox? Mind reading?

Also, what happens when two or more users have the same first initial and same first 4 letters of the last name?

example:

• Michael Richardson
• Matthew Richards

Or if 2 or more people have the exact same names?

• John Smith
• John Smith

It is probably better to let the user choose his/her username. Check if the username is available. If not, prompt the user for a different username.

You could probably write your own installer in less time than it takes to troubleshoot the one you are currently using--considering you have to wait for response from someone else.

Call "Refresh" method. labelName.Refresh()

In that case, I recommend that you contact "InstallSimple" for support. You will have to work with them to identify the issue.

First of all there should be a way to exit the program without having to kill the process (in my opinion).

There are two versions of .NET 4--client and full version. Which version does your program use?

What version of the OS did you test it on? 32-bit or 64-bit? What version of the OS is the customer using (32-bit or 64-bit)?

It appears that your application requires Office. What version of Office did you test with (2007, 2010, 2013, etc...)? What version of Office is the customer using? Also, is the customer's Office 32-bit or 64-bit?

Did you compile your application for "Any CPU", or for "x86"?

It is difficult to know what the problem could be without knowing what your program does (seeing the code or you providing more information about it).

I would try compiling for "x86" rather than "Any CPU" and see if that resolves the issue. Or you could find an 64-bit OS to test it on.

Also the uninstaller doesn't work (32-bit Win 7).

Other than installing your program in %ProgramFiles%, you should not be writing to %ProgramFiles% (or HKEY_LOCAL_MACHINE in the registry).

Use one of the following directories to store your data:

Dim userProfileDir As String = Environment.GetEnvironmentVariable("USERPROFILE")

Dim allUsersProfileDir As String = Environment.GetEnvironmentVariable("ALLUSERSPROFILE")

Dim appDataDir As String = Environment.GetEnvironmentVariable("APPDATA")

Something like the following should work:

        'path from original file
        Dim source As String = "C:\temp\regextest.txt"

        'path new file
        Dim destination As String = "C:\temp\regextest2.txt"

        Dim newText As String = "300" 'new value

        Dim inputText As String = My.Computer.FileSystem.ReadAllText(source)

        'split lines
        Dim inputTextArray As String() = inputText.Split(New String() {"\r\n", "\n", "\r", System.Environment.NewLine}, StringSplitOptions.None)

        'used named capturing groups
        Dim pattern As String = "(?<label>l)=([""])(?<tag>.*)([""])"

        'create a substitution pattern for replace method
        Dim replacePattern As String = "${label}=""" & newText & """"

        Dim outputText As String = String.Empty

        For Each lineData As String In inputTextArray
            Dim result As String = Regex.Replace(lineData, pattern, replacePattern, RegexOptions.IgnoreCase)

            outputText += result & System.Environment.NewLine
        Next

        My.Computer.FileSystem.WriteAllText(destination, outputText, False)


        'My.Computer.FileSystem.WriteAllText(destination, My.Computer.FileSystem.ReadAllText(source).Replace(oldText, newText), False)
        Process.Start(destination) 'opens program in wordpath to control value

Adapted from here.

Try posting more of your code. What version of VB .NET? What version of Outlook?

Teach Your Apps To Play Nicely With Windows Vista User Account Control

What version of XP (32-bit or 64-bit)? What version of Vista (32-bit or 64-bit)? What version of Win 7 (32-bit or 64-bit)?

Did you check that the necessary version of .NET is installed on the Vista and Win 7? There are two versions of .NET 4--a client version and a full version.

.Net 4 - Full

.Net 4 - Client

Another thing to check is if you do anything that requires elevated user permissions. UAC (user account control) settings could be preventing it from running

• Control Panel
• User Accounts
• Change User Account Control settings

Here is a small program I wrote to help understand what nested for loops do:

        static void Main(string[] args)
        {
            string inputStr = String.Empty;
            int outerForLoopCount = 0;
            int firstNestedForLoopCount = 0;
            int secondNestedForLoopCount = 0;

            int totalOuterLoops = 0;
            int totalFirstNestedLoops = 0;
            int totalSecondNestedLoops = 0;

            do
            {
                Console.Write("How many times do you want to run the outer for loop? ");
                inputStr = Console.ReadLine();
            } while (Int32.TryParse(inputStr, out outerForLoopCount) == false);

            do
            {
                Console.Write("How many times do you want to run the 1st nested for loop? ");
                inputStr = Console.ReadLine();
            } while (Int32.TryParse(inputStr, out firstNestedForLoopCount) == false);

            do
            {
                Console.Write("How many times do you want to run the 2nd nested for loop? ");
                inputStr = Console.ReadLine();
            } while (Int32.TryParse(inputStr, out secondNestedForLoopCount) == false);

            for (int i = 0; i < outerForLoopCount; i++)
            {
                for (int j = 0; j < firstNestedForLoopCount; j++)
                {
                    for (int k = 0; k < secondNestedForLoopCount; k++)
                    {
                        totalSecondNestedLoops += 1;
                    }//for

                    totalFirstNestedLoops += 1;
                }//for

                totalOuterLoops += 1;
            }//for

            Console.WriteLine();
            Console.WriteLine("Outer for loop executed: " + totalOuterLoops + " time(s)");
            Console.WriteLine();
            Console.WriteLine("First nested for loop executed: " + totalFirstNestedLoops + " times(s)");
            Console.WriteLine("     This is " + outerForLoopCount + " * " + firstNestedForLoopCount);
            Console.WriteLine();
            Console.WriteLine("Second nested for loop executed: " + totalSecondNestedLoops + " times(s)");
            Console.WriteLine("     This is " + outerForLoopCount + " * " + firstNestedForLoopCount + " * " + secondNestedForLoopCount);
            Console.WriteLine("");
            Console.WriteLine("Press any key to quit");
            Console.ReadKey();
        }

TimeZoneInfo looks like it is only supported in .NET versions >= 3.5

Here are some other resources:

.NET Framework Versions and Dependencies

Multi-Targeting Support

Global Time

VS Express 2010

VS Express 2012 - Desktop

VS Express 2013 - Desktop

I've updated 'getData' to use an SqlDataAdapter and added code that significantly reduces the number of times the labels are re-drawn (appearance of flashing on the form). This is an update to "DatabaseOnCallRotation-SqlDataReader.zip" but only contains a few of the changes that xrj made.

When other people make a lot of changes to your code, sometimes it no longer feels like it is your program. It is a good idea to separate the code into blocks that do a particular task (such as retrieving the data, showing the data, etc) as xrj did. This version is a progression towards that.

Using an SqlDataAdapter and DataTable will be better because it will make it easier for you to use your data as a DataSource should you decide to switch to another type of control (such as a DataGridView).

The code in 'getData' is still mostly your code, with a few additional modifications. It now uses a function called 'compareLabelLists'.

Add the following declaration inside 'Public Class MainFrm':

    'used to hold the names of the 
    'previous labels. This will prevent
    'controls from re-drawing every
    'updateInterval. Instead, only
    're-drawing if there are changes
    Dim previousLblList As List(Of Label) = New List(Of Label)

compareLabelLists:

    Private Function compareLabelLists(ByVal list1 As List(Of Label), ByVal list2 As List(Of Label))
        Dim areListsEqual As Boolean = True

        Try

            'if the number of elements is different
            'the lists can't be equal
            If list1.Count <> list2.Count Then
                areListsEqual = False
            Else

                'if the text of the …

'SqlDataReader' version is basically the same one that I posted previously. Other minor modifications are documented in the code.

'SQLDataAdapter' version has the changes made by xj implemented (uses SqlDataAdapter and a DataTable). I tried to document the changes that xj made the best that I could (in the "Modified" section).

Both versions contain the fix for "lblName not being declared" (see my previous post) and contain more comments / documentation than the version in my previous post.

You should be able to open / run these in your version of VS.

In the For Loop where the labels are removed, it refers to lblName (for each lblName in...) but when i add that it complains that lblName is not declared.

Fix: Add "As String".

Change the following code from:

'remove old labels
For Each lblName In lblNameList
    Me.Controls.RemoveByKey(lblName)
Next

To:

'remove old labels
For Each lblName As String In lblNameList
    Me.Controls.RemoveByKey(lblName)
Next

I think that it is easier to understand if I post some sample data.

Team Sample Data:

TeamMember Sample Data:

Schedule Sample Data:

A primary key must be unique--it's value cannot be inserted more than once in a table. By looking at the data in "Schedule", we can see that an employeeID (called teamMemberEmpID in table Schedule), has multiple schedules--start times and finish times. So how can we create a unique value from that? Well, in my table design, we have two choices:

Choice 1:
teamMemberEmpID, start

Choice 2:
teamMemberEmpID, finish

Question 1: Will start time always be known?

Question 2: Will finish time always be known?

Question 3: Is it possible that start OR finish will ever contain a null value?

A null value can be unique so long as another one is not entered.

It is possible that an employee is given a start time, but the finish time is subject to change based on the needs of the company. In choosing a primary key, it is better to choose data that is more likely to remain static. Therefore, I chose to use start time as the other part of my primary key.

Since, one employee can only work one shift at a time, he/she will only ever have one start date/time on a particular day. Therefore this will always make it unique.

You may notice some flashing when an update is occuring because the labels are removed and added again. A work-around, would be to check to see if any of the data has changed. If it has, delete the labels and re-add them. If not, don't do anything. Could use another list and compare the lists using List.Except (compare the names).

The code should work with your local project database too--I tested it both ways. In "SQLExpressDB.vb" (connectStr), comment out the "connectStr" that is currently used and uncomment one of the other ones. I couldn't include the database in the .zip file because it made the file too large.

Read about SQL Server Express User Instances

*"...Databases running on a user instance are opened in single-user mode only, and it is not possible for multiple users to connect to databases running on a user instance...."

I also noticed that when VS is open, the database connection is not released, however it didn't affect my project. Only if you tried to connect to the database file (or attach it in SQL Management Studio). If you eliminate the using statements and close the connection yourself, you may not have that problem any more.

Here is a project I created. It is based on the code you posted above.

It requires that you have SQLExpress installed.

Download SQL Management Studio. It will make it easier for you when using a service-based database. (You can use the 2012 version with SQLExpress 2005/2008)

The following post may help:

How to: Host Controls in Windows Forms DataGridView Cells

Duplicate post: http://www.daniweb.com/software-development/vbnet/threads/476214/saving-selected-data-from-textbox-to-db

See your other post for a better database design. When you have a properly designed database, you can use "join" statements to retrieve the data you want.

Here is the table creation code. It is in "SQLExpress.vb" which is in "Files.zip". I will put it here in case the file becomes unavailable.

Connection String (same as above):

'Project => Add New Item => Service-based Database
'SQLExpress as a service (Service-based Database)
'Private connectStr = "Server=.\SQLExpress;Database=ShiftRota;Trusted_Connection=Yes;"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
'Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Trusted_Connection=Yes;User Instance=True"

CreateTeamTbl:

    Public Sub CreateTeamTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE Team (teamID nvarchar(25) NOT NULL "
                sqlText += "CONSTRAINT PK_Team_teamID PRIMARY KEY,"
                sqlText += "name nvarchar(25))"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                Console.WriteLine("Table: Team created.")
            End Using

        Catch ex As SqlClient.SqlException
            Console.WriteLine("Error:: CreateTeamTbl: " & ex.Message)
        Catch ex As Exception
            Console.WriteLine("Error:: CreateTeamTbl: " & ex.Message)
        End Try
    End Sub

CreateTeamMemberTbl:

    Public Sub CreateTeamMemberTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE TeamMember (employeeID nvarchar(25) NOT NULL "
                sqlText += "CONSTRAINT PK_TeamMember_employeeID PRIMARY KEY, "
                sqlText += "firstName nvarchar(50), "
                sqlText += "surName nvarchar(50), "
                sqlText += "onCallPhone nvarchar(25), "
                sqlText += "location nvarchar(25), "
                sqlText += "teamID nvarchar(25), "
                sqlText += "CONSTRAINT FK_TeamMember_TeamID_Team_TeamID FOREIGN …

Here's some of the code I used. The database structure is slightly different from what you currently have.

I use the following table names:

• Team
• TeamMember
• Schedule

Team:

TeamMember:

Schedule:

And here is the sql select statement text:

Dim sqlText As String = String.Empty
sqlText = "SELECT Team.name as 'TeamName', TeamMember.firstName, "
sqlText += "TeamMember.Surname, TeamMember.onCallPhone, TeamMember.location, "
sqlText += "Schedule.start, Schedule.finish FROM Team "
sqlText += "INNER JOIN TeamMember "
sqlText += "ON Team.teamID = TeamMember.teamID "
sqlText += "INNER JOIN Schedule "
sqlText += "ON TeamMember.employeeID = Schedule.TeamMemberEmpID "
sqlText += "WHERE (Schedule.start < { fn NOW() }) AND (Schedule.finish > { fn NOW() })"

The connection strings I used:

'Project => Add New Item => Service-based Database
'SQLExpress as a service (Service-based Database)
'Private connectStr = "Server=.\SQLExpress;Database=ShiftRota;Trusted_Connection=Yes;"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
'Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Integrated Security=True;User Instance=True"

'Project => Add New Item => Local Database
'SQLExpress as a file (Local Database)
Private connectStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & Application.StartupPath & "\ShiftRota.mdf;Trusted_Connection=Yes;User Instance=True"

"SQLExpress.vb" and "DefaultData.vb" are attached in "Files.zip" below. The connection string is specified in the top of "SQLExpress.vb".

To create a database using "SQLExpress.vb":

SQLExpressDB.CreateDatabase("ShiftRota")

If you should decide to place your labels onto a panel, you will need to reference each label by it's parent. To do so, you need to first find it's parent. So instead of:

            'remove old labels
            'the names are in lblNameList
           For Each lblName In lblNameList
               Me.Controls.RemoveByKey(lblName)
           Next

           Using cn As New SqlConnection(sqlString)

            ...

You would do something like this:

        'remove old labels
        For Each lblName In lblNameList

            'holds the parent control of
            'the control we are looking for
            Dim parentCtrl As Control = Nothing

            'find the label name and put 
            'results into list
            Dim ctrlList = Me.Controls.Find(lblName, True).ToList()

            'find parent control name
            'this is so we can reference the 
            'control in relation to it's parent.
            'This is necessary if an object
            'is placed on a panel.
            For Each ctrlItem In ctrlList
                parentCtrl = ctrlItem.Parent
            Next

            If parentCtrl IsNot Nothing Then

                'remove the label
                parentCtrl.Controls.RemoveByKey(lblName)
            End If

            'Me.Controls.RemoveByKey(lblName)
        Next


        Using cn As New SqlConnection(sqlString)

            ...

I believe that your issue is that you are creating a label for each person, but never attempt to remove them before updating the data. So if a person's shift is over you will still see their label because you haven't removed it.

How to fix it:
Keep track of the name of the labels you are adding and delete them each time before querying the database. I will use a list:

Public Class Form1

        'list to keep track of label names
        'that were dynamically added
        Dim lblNameList As List(Of String) = New List(Of String)

        ....

Delete the old labels (from the previous query), before getting the updated data from the database:

            'remove old labels
            'the names are in lblNameList
            For Each lblName In lblNameList
                Me.Controls.RemoveByKey(lblName)
            Next

            Using cn As New SqlConnection(sqlString)

            ...

Clear the previous list and add the names of the newly created labels to our list:

    'clear the previous list
    lblNameList.Clear()

    'keep track of labels that were created
    'add lblTeam.Name to list
    lblNameList.Add(lblTeam.Name)

    ' Add the label control to the form
    Me.Controls.Add(lblTeam)

I think updating your form every 5 seconds is excessive. Every 15 to 30 seconds is more than enough. Once per 45 or 60 seconds may be better.

Also, in my opinion it is not good practice to include the table names in the column names (unless you are using a reserved word in your column name and can't find any other name to name it).

Tough crowd. The Console.WriteLine statements were for debugging. I left them in so that one could verify the results. They should be removed when finished debugging.

Here is an updated version:
The concept of the code is the same. However, I moved the majority of the code to it's own class: ListBoxMonitor. "getLastItemChanged" now returns an "ItemInfo" object. I removed some parameters from "getLastItemChanged" and made them private variables in class ListBoxMonitor. I've also made ListBoxMonitor static.

Create a new class called "ListBoxMonitor.cs" and replace with the following code:

    public static class ListBoxMonitor
    {
        //holds the last item that was changed
        //based on SelectionType chosen by user
        private static ItemInfo lastItem = new ItemInfo();

        //holds the previously selected list items
        private static List<string> previousSelectedItemsList = new List<string>();

        //create an enumeration 
        public enum SelectionType { Deselected, Either, Selected }; 


        public static ItemInfo getLastItemChanged(System.Windows.Forms.ListBox lb, SelectionType sType)
        {

            List<string> currentSelectedItemsList = new List<string>();

            //put selected items into a list
            //so we can use list.Except
            for (int i = 0; i < lb.SelectedItems.Count; i++)
            {
                currentSelectedItemsList.Add(lb.SelectedItems[i].ToString());
            }//for

            if (sType.Equals(SelectionType.Either))
            {
                lastItem = findExcludedItemInList(currentSelectedItemsList,
                                                  previousSelectedItemsList);
            }//if
            else if (sType.Equals(SelectionType.Selected))
            {
                //get last item selected as a list
                //by comparing the current list
                //to the previous list

                if (String.Compare(findExcludedItemInList(currentSelectedItemsList,
                    previousSelectedItemsList).SelectionType, "Selected", true) == 0)
                {
                    lastItem = findExcludedItemInList(currentSelectedItemsList,
                               previousSelectedItemsList);
                }//if
            }//else if
            else if (sType.Equals(SelectionType.Deselected))
            {
                //if item was deselected, switch the order
                //compare previous list to current list

                //if > 0, last item was de-selected
                if (String.Compare(findExcludedItemInList(currentSelectedItemsList,
                    previousSelectedItemsList).SelectionType, "Deselected", true) == …

I re-worked the above code to create a version that allows you to specify if you want the last "Selected" item, the last "De-selected" item, or last item that was changed (either "Selected" or "De-selected").

Create a new class called "ItemInfo". Replace the code with the following:
ItemInfo:

public class ItemInfo
{
    public string Name { get; set; }
    public string SelectionType { get; set; }
}

In the form (Form1), create a list, a new instance of ItemInfo, and an enum as seen below:

public partial class Form1 : Form
{
    //holds previously selected items
    List<string> previousSelectedItemsList = new List<string>();

    //holds the last selected and/or de-selected item
    ItemInfo lastItem = new ItemInfo();

    //create an enumeration 
    enum SelectionType { Deselected, Either, Selected }; 

    public Form1()
    {
        InitializeComponent();
    }
}

Now, we will create a method that will compare our lists and return an ItemInfo object:

private ItemInfo findExcludedItemInList(List<string> list1, List<string> list2)
{
    ItemInfo item = new ItemInfo();

    //get list of items in list1 that aren't in list2
    var lastItemList1 = list1.Except(list2).ToList();

    if (lastItemList1.Count > 0)
    {
        //there should only be 1 item in the list
        item.Name = lastItemList1[0].ToString();
        item.SelectionType = "Selected";
    }//if
    else
    {
        //get list of items in list2 that aren't in list1
        var lastItemList2 = list2.Except(list1).ToList();

        if (lastItemList2.Count > 0)
        {
            //there should only be 1 item in the list
            item.Name = lastItemList2[0].ToString();
            item.SelectionType = "Deselected";
        }//if
    }//else

    return item;
}//findExcludedItemInList

Next, we will create a method "getLastItemChanged" that will use …

Here's a solution. Version 2 code has been tested.

Create a new class called "ItemInfo". Replace with the following code:

ItemInfo:

    public class ItemInfo
    {
        public string Name { get; set; }
        public string SelectionType { get; set; }
    }

In your form, (ex: Form1.cs), do the following:

Create a list to hold the previously selected items and an instance of ItemInfo.

    public partial class Form1 : Form
    {
        List<string> previousSelectedItemsList = new List<string>();
        ItemInfo lastItem = new ItemInfo();

        public Form1()
        {
            InitializeComponent();
        }
    }

Then we create a method to find the last selected item:

Version 1
(keeps track of last selected item):

private void getLastItemChanged(ref ListBox lb)
{
    List<string> lastItemSelected;


    List<string> currentSelectedItemsList = new List<string>();

    //put selected items into a list
    //so we can use list.except

    for (int i = 0; i < lb.SelectedItems.Count; i++)
    {
        //add selected items to list
        currentSelectedItemsList.Add(lb.SelectedItems[i].ToString());

        //Console.WriteLine("item: " + lb.SelectedItems[i]);
    }//for

    //get last item selected as a list
    //by comparing the current list
    //to the previous list

    lastItemSelected = currentSelectedItemsList.Except(previousSelectedItemsList).ToList();

    //if last item was selected count > 0
    //else if last item was de-selected 
    //lastItemSelected.Count = -1

    if (lastItemSelected.Count > 0)
    {
        lastItem.Name = lastItemSelected[0].ToString();
        lastItem.SelectionType = "selected";
    }//if


     Console.WriteLine(lastItem.Name + " : " + lastItem.SelectionType);

     //updated previousSelectedItems before returning
     previousSelectedItemsList = currentSelectedItemsList;
}//getLastItemChanged

Version 2
(keeps track of last item - selected OR de-selected):

private void getLastItemChanged(ref ListBox lb)
{
    List<string> lastItemSelected;
    List<string> lastItemDeSelected;

    List<string> currentSelectedItemsList = new List<string>();

    //put …

If you change your database as in my previous post, all of the data from all the tables you currently have will be in one table (Weather). You could even add a column named "Hour" and make it part of the primary key if you want to maintain hourly data.

Why are you wanting to create a separate table for each week? I recommend that you create one table named "Weather" (it will be the same as one of the tables that you currently have, except add the following columns: "WeekNumber" (Number), "MonthNumber" (Number), "YearNumber" (Number) to the columns you already have.

Set the "Required" property for: YearNumber, MonthNumber, WeekNumber, DayNumber

Open the table in "Design View".

The following columns will form the primary key:
YearNumber, MonthNumber, WeekNumber, DayNumber

Use the "Ctrl" key along with your left-mouse button to select multiple columns. While continuing to hold the "Ctrl" key, right-click the mouse, and select "Primary Key".

We've done our homework assignments. This one is yours. People are here to assist if you get stuck, but you have to show that you've put in effort. Show some code for each one of your pseudo code items.

Connection Strings

What is the problem that you are having? What is the formula that you are using to calculate the values?

Place numbers 1-16 in an ArrayList:

        //holds numbers 1-16
        ArrayList uniqueNums = new ArrayList();

        //add numbers 1-16
        for (int i=0; i < 16; i++)
        {
            uniqueNums.add(i+1);
        }//for

Use the following random number generator method. It was taken from here.

randInt:

    public static int randInt(int min, int max) {

        // Usually this can be a field rather than a method variable
        Random rand = new Random();

        // nextInt is normally exclusive of the top value,
        // so add 1 to make it inclusive
        int randomNum = rand.nextInt((max - min) + 1) + min;

        return randomNum;
    }

"randInt" is used below.
Generate unique random numbers like this:

        //choose random int
        for (int j = 15; j >= 0; j--)
        {
            int selectedNum = randInt(0,j);

            //i print out the selected number.
            //you will add it to your matrix here
            System.out.println("selected:  " + uniqueNums.get(selectedNum));

            //after a number is added to the
            //matrix, remove it from
            //the arraylist
            uniqueNums.remove(selectedNum);

            System.out.println();
        }//for

You can add your numbers to the matrix where I have the "System.out.println...." statement.

You probably want to do some input validation to ensure that the user entered an integer value when an integer value is required. See "isInt" below for a way of performing integer validation.

Declare the following variables as global (before "Main"):

        //need to declare as static 
        //if used inside static methods

        //passenger array
        private static string[] passengers;

        //number of tickets sold
        private static int ticketsSold = 0;

        //number of seats remaining
        private static int seatsRemaining = 0; 

Main:

            //for prompting to exit
            string answer = string.Empty;

            //clear the screen
            Console.Clear();

            //write empty line
            Console.WriteLine();

            //total airplane seats
            //string variable is for console input
            //int variable is for use in our program
            int totalNumberOfSeatsInt = 0; 
            string totalNumberOfSeatsStr = string.Empty;

            //total airplane seats user is trying to purchase
            //string variable is for console input
            //int variable is for use in our program
            int ticketsToPurchaseInt = 0;
            string ticketsToPurchaseStr = string.Empty;

            Boolean isValidInput = false;

            //--------------------------
            //get number of seats
            //--------------------------

            //loop until we receive an int
            //if number of seats available = 0, exit
            do
            {
                //prompt user
                Console.Write("How many seats are on the airplane? ");

                //get number of seats being purchased
                totalNumberOfSeatsStr = Console.ReadLine();

                //check to see if input was an integer
                isValidInput = isInt(totalNumberOfSeatsStr);

                //if input is an integer, convert to int
                if (isValidInput == true)
                {
                    totalNumberOfSeatsInt = Convert.ToInt32(totalNumberOfSeatsStr);

                    //if no seats are available, exit
                    if (totalNumberOfSeatsInt <= 0)
                    {
                        Console.WriteLine("Exiting. Thank you.");
                        Console.WriteLine();
                        return; //exit
                    }//if
                }//if
                else
                {
                    //write empty line …

Did you try refreshing the form after the buttons are generated?

this.Refresh();

TextBox1.Focus will not work, since whenever one presses a virtual key (button) focus will now be on the button, not on the previously selected TextBox.

You haven't given any information about your code. I am going to make the following assumptions:

• Each virtual key is a Button
• TextBox named: TextBox1
• TextBox named: TextBox2

There are a few different events that you could probably use, although I don't have a touch-screen to test them:

• Click
• Double-click
• Enter

There may be more.

In my solution, we will use a variable to keep track of which TextBox is selected, since whenever we press a button (virtual key) focus will be on that control.

    'do not define as "New"
    'we will initialize it in form_load

    Dim desiredTextBox As TextBox

In "Load" event of the form, place the following:

        If desiredTextBox Is Nothing Then
            'initialize desiredTextBox
            'set to TextBox1
            desiredTextBox = TextBox1
        End If

So our code is something like this:

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        If desiredTextBox Is Nothing Then
            'initialize desiredTextBox
            'set to TextBox1
            desiredTextBox = TextBox1
        End If
    End Sub

I am going to use the "Enter" event for each TextBox to set the value of "desiredTextBox". This is the way we will keep track based on the last TextBox the user selected.

    Private Sub TextBox2_Enter(sender As System.Object, e As System.EventArgs) Handles TextBox2.Enter
        Console.WriteLine("Textbox2 enter")

        'set desiredTextBox = TextBox2
        desiredTextBox = sender
    End Sub

    Private Sub TextBox1_Enter(sender As System.Object, e As System.EventArgs) Handles TextBox1.Enter
        Console.WriteLine("Textbox1 enter")

        'set desiredTextBox = TextBox1
        desiredTextBox = sender
    End Sub

Now write to …