kipslem 0 Light Poster


I'm newbie to VB.Net and thus I need an urgent help so doing this posting.

I'm currently working on a project to track officers attendance time.
I have several table in SQL backend. The 2 obvious tables are tblStaff
which keeps all the staff's records, and the tblAttendance which keeps
the clockin and out times, the staffId, and the PC name.

The user enters his username and password to clock in, which is validated with
those stored in database. If validation is Okay then gets to the next form
which has clkockin and clockout button. When the user clicks the clockin or clockout buttons,
the realtime, date, and the staff's ID (StaffID) will be inserted into the
tblAttenance table.

Now the issue is::-/
Since the StaffID will be got from the tblStaff after validating with
the Staff name (Username) and inserted into the tblAttendance, how do i
write the code for this? Please help! The part of my code is below.
(A stored procedure if provided to do all this will be much appreciated)

Thanks in advance.

Private Sub cmdClockIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClockIn.Click

        Dim clockin As New SqlCommand
        'Dim strCommand As String

            'When user clicks it should generate a message box with
            'If user cockedin today then he does not need to clockin again. i.e when clicking the Clockin button twice.

            If MessageBox.Show("Confirm To Clock In", "Confirm", MessageBoxButtons.OKCancel, MessageBoxIcon.None) = Windows.Forms.DialogResult.OK Then
              'This bit gets the StaffID from tha tblStaff

                Dim selectIDnum As String = "Select StaffNumber from tblStaff where UserName = @UserName"
                Dim objCommand As SqlCommand = New SqlCommand(selectIDnum)
                objCommand.Parameters.AddWithValue("@UserName", cmbUserName.Text)

                'Dim Todaysdate As String
                'Todaysdate = Date.Now()

                ' ''    'This inserts the data into the tblAttendance
                clockin.CommandText = "INSERT INTO tblAttendance " & _
            "(EntryDate, StaffNumber, CheckinTime, CheckoutTime, Latereason, WorkStationId, UserName) " & _
            "VALUES(@EntryDate, @StaffNumbr, @CheckinTime, @CheckoutTime, @Latereason, @WorkStationId, @UserName)"
                clockin.Parameters.AddWithValue("@EntryDate", DateTime.Now())
                clockin.Parameters.AddWithValue("@StaffNumber", selectIDnum) 'Get the code to retrieve the staff number from tblStaff
                clockin.Parameters.AddWithValue("@CheckinTime", TimeOfDay)
                clockin.Parameters.AddWithValue("@CheckoutTime", "")
                'clockin.Parameters.AddWithValue("@Latereason", frmLateReason.Reason) 'gets the reasons from the latereason form.
                clockin.Parameters.AddWithValue("@WorkStationId", System.Environment.MachineName)
                clockin.Parameters.AddWithValue("@UserName", cmbUserName.Text)


                Dim time As DateTime
                time = Date.Now

                txtPassword.Text = ""
                cmbUserName.SelectedIndex = -1
                Me.grpAttendenceCheck.Visible = False
                Me.grpPrintTime.Visible = False
            End If


        Catch ex As Exception
            MessageBox.Show("Please contact your system administrator", "DB Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub
VB.Net Inserting data after validating with the data 
entered against that stored in SQL DB
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.