Member Avatar for chdboy

CODE:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        Dim adaptor As New SqlDataAdapter
        Dim dataset As New DataSet
        con.Open()
        con.Close()
        con.Open()
        cmd = New SqlCommand(str, con)
        cmd.Connection = con
        cmd.CommandText = "SELECT Firstname FROM Attendance where Firstname ='" & Firstname_txt.Text.Trim & "'"
        cmd.Connection = con
        adaptor.SelectCommand = cmd
        adaptor.Fill(dataset, "O")
        Dim count = dataset.Tables(0).Rows.Count
        If count > 0 Then
            MessageBox.Show("This Employee Does Exist!")

            cmd.CommandText = "SELECT Count(Present),Firstname FROM Attendance WHERE (AttandenceID = AttandenceID) And (Firstname = Firstname) Group BY Firstname"
            cmd = New SqlCommand(cmd.CommandText, con)
            cmd.Connection = con
            dr = cmd.ExecuteReader
            While dr.Read
                TextBox1.Text = dr(0)

            End While
            con.Close()
        Else
            MsgBox("This Employee does not Exists!")
        End If
        con.Close()
    End Sub

Now the problem is that whenever I click the Count result is not the same as in Database (IF the present days are 3 then it shows 4..it automatically gets incremented by 1.
In my database if the person is Present then it's value will be 1
Present is 1
and absent is 0
Absent is 0
I want to calculate how many presents days are there in a month(TO calculate sallary)
I have
AttandanceID Field
Firstname Field
Present Field
Absent Field
AttandanceDate Field

  • 2 Contributors
  • 2 Replies
  • 184 Views
  • 47 Minutes Discussion Span
  • Latest Post Latest Post by chdboy

Dani AI

Generated

A quick diagnosis and a safe fix.

The symptom you saw (3 present days reported as 4) comes from using COUNT on the Present column and from the ineffective WHERE in your second query. COUNT(column) tallies non‑NULL values, it does not add the numeric contents of the column — so a column that stores 1/0 will be counted as a row whether it’s 1 or 0 unless you filter. (learn.microsoft.com)

To get "number of present days" aggregate the values or only count rows where Present indicates presence. For example, to count for a specific employee and month you can sum a 1/0 expression (this also works when you want to ignore absent rows):

SELECT SUM(CASE WHEN Present = 1 THEN 1 ELSE 0 END) AS PresentDays
FROM Attendance
WHERE Firstname = @firstName
  AND MONTH(AttendanceDate) = @month
  AND YEAR(AttendanceDate) = @year;

If Present is a SQL Server BIT column you must convert it to a numeric type (or use the CASE form above) because SUM requires a numeric expression. (learn.microsoft.com)

In your VB code use a parameterized command and ExecuteScalar for a single aggregated value (avoid string concatenation and the tautology Firstname = Firstname). Example outline:

cmd.CommandText = "... WHERE Firstname = @name AND MONTH(AttendanceDate)=@m AND YEAR(AttendanceDate)=@y"
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = Firstname_txt.Text.Trim()
Dim presentDays As Integer = Convert.ToInt32(cmd.ExecuteScalar())

Use ExecuteScalar for a single value and prefer explicit parameter types rather than building SQL with string concatenation. (learn.microsoft.com)

Extra tips: fix the AttandenceID = AttandenceID / Firstname = Firstname clauses (they’re always true), close/dispose readers and connections, and use a stable key (EmployeeID) instead of Firstname to avoid duplicate-name issues.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

In short, what you should do is either use SUM(Present) or use WHERE Present = 1. COUNT just counts the number of rows in the result set.

Member Avatar for chdboy

Thanks a lot :) It works like a charm

Be a part of the DaniWeb community

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