error: You cannot add or change a record because a related record is required in table

Question

The following code works expect when I select Manager from combox and enter incorrect username and password, error "You cannot add or change a record because a related record is required in table 'Privileges' ". If I enter incorrect or correct username and password for Guest there are no errors. If I enter correct username and password for Manager there is no error. The error is pointing at cmd.ExecuteNonQuery();. Please could someone help me here?

If I remove the whole code for switch case Guest, from

        command.CommandText = "SELECT * FROM [GuestPrivileges] WHERE STRCOMP  

then it works as it should be. GuestPrivileges table is not in relationship, it is on it's own.

private void btnLogin_Click(object sender, EventArgs e)
    {
        string value = cBRoles.Text;

        switch (value)
        {
            case "Managers":
                using (var command = myCon.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM [Privileges] WHERE STRCOMP(UserName, @UserName,0) = 0 AND STRCOMP(Password, @Password,0)=0";
                    command.Parameters.AddWithValue("UserName", (txtUsername.Text));
                    command.Parameters.AddWithValue("Password", (txtPassword.Text));

                    myCon.Open();
                    var reader = command.ExecuteReader();
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                txtUsername.Text = reader["UserName"].ToString();
                                txtPassword.Text = reader["Password"].ToString();
                                MainForm frm = new MainForm();
                                frm.Show();
                            }
                        }
                        else MessageBox.Show("You have entered incorrect credentials. Please try again", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                myCon.Close();

                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO LoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime)";
                cmd.Parameters.AddWithValue("@UserName", txtUsername.Text);
                cmd.Parameters.AddWithValue("@LoggedInDate", DateTime.Now.ToShortDateString());
                cmd.Parameters.AddWithValue("@LoggedInTime", DateTime.Now.ToString("HH:mm"));
                cmd.Connection = myCon;
                myCon.Open();
                cmd.ExecuteNonQuery(); << error pointing here
                cmd.CommandText = "SELECT @@IDENTITY";
                int id = (int)cmd.ExecuteScalar();
                this.ID = id;
                myCon.Close();
                break;

                case "Guest":
                using (var command = myCon.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM [GuestPrivileges] WHERE STRCOMP(GUserName, @GUserName,0) = 0 AND STRCOMP(GPassword, @GPassword,0)=0";
                    command.Parameters.AddWithValue("GUserName", (txtUsername.Text));
                    command.Parameters.AddWithValue("GPassword", (txtPassword.Text));

                    myCon.Open();
                    var reader = command.ExecuteReader();
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                txtUsername.Text = reader["GUserName"].ToString();
                                txtPassword.Text = reader["GPassword"].ToString();
                                ViewReport frmb = new ViewReport();
                                frmb.Show();
                            }
                        }
                        else MessageBox.Show("You have entered incorrect credentials. Please try again", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
               myCon.Close();
               break;
        }
    }

Visual Studio 2010, C#, MS ACCESS 2003

Thank you to all in advance

Answer 1

You apparently have a database Relationship defined between the [LoginLogTable] and [Privileges] tables that is attempting to enforce Referential Integrity. The unknown Manager has no matching row in [Privileges], so when you try to add his (non-existent) UserName into [LoginLogTable] the database won't let you.

If you want to log access attempts from unknown Managers then you should disable "Enforce Referential Integrity" for that Relationship. If you don't want to log those attempts then you could do a lookup in the [Privileges] table to see if the UserName exists before proceeding. If the UserName does not exist then you could have the program simply terminate (since you have already told them that they "have entered incorrect credentials").