Attendence form

[Om Alooi]

I have an Attendence form

the form is like this

the employee should select there number

then click on sign in button

it should save the date of the day , the time in , the number of the employee and display tha data in the gridview

and disable the sign in button for the employee who enter the system and enable sign out button for him

the table in the database

Employee No
Time In
Time Out
Date

my Q

1- how can i builde the query
2- what i shuold write in the sign in button (I meen the code)

[Reverend Jim]

First a few comments.

• It is a bad idea to have field names that contain spaces.
• it is preferable (for sorting) to keep both the date and time in one field
• What is to prevent an employee from signing in under another employee's number?

To address the first two points I suggest a table structure like

Attendance
    EmployeeNo        Int        Not Null
    SignIn            DateTime   Not Null
    SignOut           DateTime   Null

The primary key can be a combination of the first two fields. The first two fields cannot be null, first of all because you can't have a null key and second of all because when you insert the record you will know the value of both fields. The last field must allow nulls because you can't supply the value until the employee logs out.

To address the last point you would need an employee table that contains the names of all employees and their employee numbers (primary key). You would also need a password table which contains the employee number (primary key) and their password. To login to the app they would have to provide their password.

Based on my assumptions, the query on signin would be something like

Dim query As String = "insert into Attendance (EmployeeNo, SignIn) Values("
                    & empno & ",'" & Now() & "')"

where empno is the number selected from the combobox. If the attendance "sheet" is being maintained by someone other than the employee then a password is not required.

[hericles]

I would change the database table to have the employee ID the date time stamp and a status showing whether they entered or exited (1 or 0 basically). Then you just insert into the table the employee ID, the time stamp and the status (based on whether the sign in or sign out button was clicked).
Otherwise, as you have it, when they sign out you need to locate the last row used by the employee (having a sign in time but no sign out) and update the sign out time. This is pretty awkward and inefficient.

in your code for the button you need the database connection and command execution code. If you don't know how to do this there are plenty of tutorials online that eplain it in better detail than you can get here.

[Reverend Jim]

I see that I totally screwed up my previous post. It is gone and I'll try to get it back.

Any database access will be awkward and inefficient depending on how the data is to be accessed. Databases are typically organized to be the most efficient for the most likely access. In the case of a database with millions of records this is going to be a consideration. For the given application I doubt that this will be a consideration. Given that, I think the most reasonable organization is the one that I have given. It maps to what I think is the most likely display, a table showing the employee name/id, sign in time and sign out time.

It is also more suitable for casual scanning of the database using something like SQL Server Management Studio where a simple "select * from Attendance" shows all of the data.

[Reverend Jim]

OK. I think I have restored my original reply. Now perhaps my second post will make more sense.

[Om Alooi]

thank you very much for your help
it work
=)