Member Avatar for dougancil

I am developing an application that based on when a user has last entered a payroll, my program tells them the next available Sunday that is available to them based on

1. The date that the last time payroll was entered
2. If payroll ran = "yes" or "no"

I have a sql query that returns the next Sunday back to me based on this criteria, but when I put it into my application, it returns 1/1/0001 to me.

Here is the query:

select MAX(payrolldate) AS [payrolldate], <br> 
dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday] <br> 
from payroll <br> 
where payrollran = 'no'

and here is my vb code as it stands now, with my old query in it:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br> 
Dim connectionString As String<br> 
Dim cnn As SqlConnection<br> 
Dim myCommand As SqlCommand<br> 
Dim dteReturnValue As DateTime = Nothing<br> 
'the connection string to the SQL server'<br> 
connectionString = "Data Source=xxxxxx;Initial Catalog=MDR;uid=xxxxxxx;password=xxxxxxx"<br> 
cnn = New SqlConnection(connectionString)<br> 
cnn.Open()<br> 
'the SQL query'<br> 
Try<br> 
myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")<br> 
myCommand.Connection = cnn<br> 
ReturnValue = myCommand.ExecuteScalar()<br> 
If ReturnValue IsNot Nothing Then<br> 
dteReturnValue = Convert.ToDateTime(ReturnValue)<br> 
dteReturnValue = dteReturnValue.AddDays(1)<br> 
End If<br> 
Catch exp As SqlException<br> 
End Try<br> 
cnn.Close()<br> 
Dim ButtonDialogResult As DialogResult<br> 
ButtonDialogResult = MessageBox.Show(String.Format("The next pay period available to you is {0} through {1}", dteReturnValue.ToShortDateString(), dteReturnValue.AddDays(7).ToShortDateString), "Payroll", MessageBoxButtons.OKCancel)<br> 
If ButtonDialogResult = Windows.Forms.DialogResult.OK Then<br> 
Button2.Enabled = True<br> 
Button1.Enabled = False<br> 
End If<br> 
End Sub

I understand that the reason that I'm getting 1/1/0001 back to me is that it's a blank response. Can anyone help me with this?

Thank you

Doug

  • 2 Contributors
  • 2 Replies
  • 207 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dougancil

Dani AI

Generated

Short diagnosis and why 1/1/0001 appears
The DateTime shown (1/1/0001) is DateTime.MinValue — it means the local variable was never set because the database call returned NULL or an exception was swallowed. Two common causes in this thread: using ExecuteScalar with a multi‑column SELECT (it only returns the first column) and not checking for DBNull.Value (Convert.ToDateTime(DBNull.Value) throws, then the empty Catch leaves the DateTime at its default). This ties ’s MAX approach to ’s DATEPART comment: DATEPART depends on SET DATEFIRST, so a DATEFIRST‑independent formula is safer.

Deterministic SQL (does not rely on DATEFIRST)
This computes the next Sunday after MAX(payrolldate) using a fixed anchor Sunday:

SELECT DATEADD(day, 7 - (DATEDIFF(day, '1900-01-07', MAX(payrolldate)) % 7), MAX(payrolldate)) AS NextSunday
FROM payroll
WHERE payrollran = 'no';

VB.NET pattern to read safely
Use a single‑column SELECT with ExecuteScalar (or use ExecuteReader to fetch named columns). Check for DBNull and avoid empty Catch blocks:

Using cn As New SqlConnection(connString)
    Using cmd As New SqlCommand("SELECT DATEADD(day, 7 - (DATEDIFF(day, '1900-01-07', MAX(payrolldate)) % 7), MAX(payrolldate)) AS NextSunday FROM payroll WHERE payrollran = 'no'", cn)
        cn.Open()
        Dim obj As Object = cmd.ExecuteScalar()
        If obj IsNot Nothing AndAlso obj IsNot DBNull.Value Then
            Dim nextSunday As DateTime = DirectCast(obj, DateTime)
            MessageBox.Show(String.Format("The next pay period available to you is {0} through {1}", nextSunday.ToShortDateString(), nextSunday.AddDays(7).ToShortDateString()))
        Else
            MessageBox.Show("No eligible payroll rows found.")
        End If
    End Using
End Using

Troubleshooting notes
MAX returns NULL when no rows match the WHERE clause — check row existence first (or use TOP 1 ... ORDER BY payrolldate DESC). Avoid hardcoding credentials and never swallow exceptions silently; log them so failures (like DBNull conversions) surface during debugging.

Recommended Answers

All 2 Replies

Member Avatar for lolafuertes

maybe you can try to change your query to some thing like:

select MAX(payrolldate) AS [payrolldate], 
dateadd(dd, 7 - DATEPART(weekday,MAX(payrolldate)), MAX(payrolldate)) AS [Sunday] 
from payroll where payrollran = 'no'

The hint here is that datepart returns the week day(1 to 7). The return value depends on the value that is set by using SET DATEFIRST. I use 1 (monday) as first day of week so Sunday is 7.

If you use another first day of week, then you must change the query according.

Hope this helps

Member Avatar for dougancil

Ok so I guess a better question would be if I just use the query, how do I capture the values from SQL to present them back to the user? In other words if I use this query

myCommand = New SqlCommand("select MAX(payrolldate) AS [payrolldate], dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday] from(payroll)where payrollran = 'no' ")
            myCommand.Connection = cnn

I need to pass the value for [Sunday] back to the user here:

ButtonDialogResult = MessageBox.Show(String.Format("The next pay period available to you is"), "Payroll", MessageBoxButtons.OKCancel)
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.