C# & SQL time in and out application

See more:

SQL

C#7.0

I would like to request some help on creating a c# based sign in and out form that uses a local sql datbase to store people signing in and then out again

There are no errors however the form will not accept any data and the sign out form drop box will not populate with names of people from persons table I would like help to making this work please.
I need major help to ensure that:
the data accepts from The sign in form to the database columns of Name, Time in and organisation
then saves
then when signing out the combobox is populated with all names of people who have signed in
Then to sign out the table uses the name in the combobox to find the time out column related to it and pastes the time out into it

This then needs a computed column in which the sql table will display the retention time of each user

This then needs to be displayed in a graph on the admin page


If anyone has any examples o something like this or wishes to work on my program could they email me at: [email address removed] (only email I have access to at the minuet)

What I have tried:

My current code is

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace SignInAndOutForm
{
    public partial class SignIn : Form
    {
        bool dataAccepted = false;
        string connectionString = (@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Register.mdf;Integrated Security=True"); //mainly used as a note rather than placeholder
        // also uses this as the link to the DATABASE
        SqlConnection Connection;

        public SignIn()
        {
            InitializeComponent();
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            //allows for automated time when clicked provided by local system
            string AutoTime = DateTime.Now.ToString("HH:mm");
            textBox2.Text = AutoTime;
        }

        private void SignIn_Load(object sender, EventArgs e)
        {
            //Loads oddly enough
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //ACCEPT SOME DATA, by calling the subroutine
            AcceptData();
            // then close the form, it hides it to stop
            if (dataAccepted == true)
            {
                textBox1.Clear();
                textBox2.Clear();
                textBox3.Clear();
                this.Hide();
            }

        }
        // this tries to create a connection to the current database of PEOPLE and then tried to input the data required
        private void AcceptData()
        {
            using (Connection = new SqlConnection(connectionString))
            {
                Connection.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter("UPDATE ", Connection))//INPUT changed to INSERT Allowing data to be accepted //keep for a later date INSERT INTO Person SELECT * FROM PERSON WHERE TimeOut IS NULL
                {
                    DataTable RegisterTable = new DataTable();
                    adapter.Fill(RegisterTable); //Initialisation fixed using .open() command

                    string name = textBox1.Text;
                    string organisation = textBox3.Text;
                    DateTime Time = DateTime.Parse(textBox2.Text);
                    string strDateTimeIn = Time.ToString("yyyy-MM-dd HH:mm:ss.ffff");
                    string query = "INSERT INTO Person (Name,Organisation,TimeIn) VALUES('" + name + "','" + organisation + "','" + strDateTimeIn + "')";
                    SqlCommand SignIn = new SqlCommand(query, Connection);
                    SignIn.ExecuteNonQuery(); // this should be fine currently
                    dataAccepted = true;

                }
            }

        }
    }
}

Sign Out

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace SignInAndOutForm
{
    public partial class SignOut : Form
    {
        public SignOut()
        {
            InitializeComponent();
            BindComboName();
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            string AutoTime = DateTime.Now.ToString("HH:mm");
            textBox1.Text = AutoTime;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
            if (textBox1.Text == null || textBox1.Text == "")
            {
                MessageBox.Show("Please click the time box and press a key");
            }
            else
            {
                DateTime timeOut = DateTime.Parse(textBox1.Text);
                string strDateTimeOut = timeOut.ToString("yyyy-MM-dd HH:mm:ss.ffff");
                string queryOut = "UPDATE Person SET TimeOut = '" + strDateTimeOut+ "' FROM Person p1 WHERE p1.TimeOut IS NULL AND p1.TimeIn = (SELECT MAX(TimeIn) FROM Person p2 WHERE p2.Name = p1.Name AND p2.TimeOut IS NULL)" ;

            }
            // ACCEPT SOME DATA
            textBox1.Clear();
            this.Hide();

        }

        private void SignOut_Load(object sender, EventArgs e)
        {

        }

        private void BindComboName()
        {
            using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Register.mdf;Integrated Security=True"))

            {
                SqlCommand sqlCmd = new SqlCommand("SELECT Name FROM Person WHERE TimeOut IS NULL GROUP BY Name ORDER BY Name", sqlConnection);
                sqlConnection.Open();
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();

                while (sqlReader.Read())
                {
                    comboBox1.Items.Add(sqlReader["Name"].ToString());
                }
                sqlReader.Close();
            }
        }
    }
}