SQl Query on form returning value of 1/1/0001

[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], 
   dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday] 
from payroll 
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
Dim connectionString As String
Dim cnn As SqlConnection
Dim myCommand As SqlCommand
Dim dteReturnValue As DateTime = Nothing
'the connection string to the SQL server'
connectionString = "Data Source=xxxxxx;Initial Catalog=MDR;uid=xxxxxxx;password=xxxxxxx"
cnn = New SqlConnection(connectionString)
cnn.Open()
'the SQL query'
Try
myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")
myCommand.Connection = cnn
ReturnValue = myCommand.ExecuteScalar()
If ReturnValue IsNot Nothing Then
dteReturnValue = Convert.ToDateTime(ReturnValue)
dteReturnValue = dteReturnValue.AddDays(1)
End If
Catch exp As SqlException
End Try
cnn.Close()
Dim ButtonDialogResult As DialogResult
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)
If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
Button2.Enabled = True
Button1.Enabled = False
End If
End Sub
[/code]

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

 

[dougancil]

my apologies

Here is my code reformatted

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim cnn As SqlConnection
Dim myCommand As SqlCommand
Dim dteReturnValue As DateTime = Nothing
'the connection string to the SQL server'
connectionString = "Data Source=xxxxxx;Initial Catalog=MDR;uid=xxxxxxx;password=xxxxxxx"
cnn = New SqlConnection(connectionString)
cnn.Open()
'the SQL query'
Try
myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")
myCommand.Connection = cnn
ReturnValue = myCommand.ExecuteScalar()
If ReturnValue IsNot Nothing Then
dteReturnValue = Convert.ToDateTime(ReturnValue)
dteReturnValue = dteReturnValue.AddDays(1)
End If
Catch exp As SqlException
End Try
cnn.Close()
Dim ButtonDialogResult As DialogResult
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)
If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
Button2.Enabled = True
Button1.Enabled = False
End If
End Sub