获取语法错误从日期时间字符串变换

Question

我有以下代码：

Imports System.Data.SqlClient 
Public Class Main 
    Protected WithEvents DataGridView1 As DataGridView 
    Dim instForm2 As New Exceptions 
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click 
     Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _ 
       "dateadd(s, 518399, max(payrolldate)) AS [Sunday]" & _ 
        "from dbo.payroll" & _ 
        " where payrollran = 'no'" 
     Dim oCmd As System.Data.SqlClient.SqlCommand 
     Dim oDr As System.Data.SqlClient.SqlDataReader 
     oCmd = New System.Data.SqlClient.SqlCommand 

     Try 
      With oCmd 
       .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx") 
       .Connection.Open() 
       .CommandType = CommandType.Text 
       .CommandText = ssql 
       oDr = .ExecuteReader() 
      End With 
      If oDr.Read Then 
       payperiodstartdate = oDr.GetDateTime(1) 
       payperiodenddate = payperiodstartdate.AddDays(7) 
       Dim ButtonDialogResult As DialogResult 
       ButtonDialogResult = MessageBox.Show("  The Next Payroll Start Date is: " & payperiodstartdate.ToString() & System.Environment.NewLine & "   Through End Date: " & payperiodenddate.ToString()) 
       If ButtonDialogResult = Windows.Forms.DialogResult.OK Then 
        exceptionsButton.Enabled = True 
        startpayrollButton.Enabled = False 
       End If 
      End If 
      oDr.Close() 
      oCmd.Connection.Close() 
     Catch ex As Exception 
      MessageBox.Show(ex.Message) 
      oCmd.Connection.Close() 
     End Try 

    End Sub 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click 
     Dim connection As System.Data.SqlClient.SqlConnection 
     Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter 
     Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx" 
     Dim ds As New DataSet 
     Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime) as duration into Scratchpad3" & _ 
     " FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _ 
     " where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _ 
     " GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _ 
     " [Exceptions].code, [Exceptions].exceptiondate" 
     connection = New SqlConnection(connectionString) 
     connection.Open() 
     Dim _CMD As SqlCommand = New SqlCommand(_sql, connection) 
     _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate) 
     _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate) 
     adapter.SelectCommand = _CMD 
     Try 
      adapter.Fill(ds) 
      If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then 
       'it's empty 
       MessageBox.Show("There was no data for this time period. Press Ok to continue", "No Data") 
       connection.Close() 
       Exceptions.saveButton.Enabled = False 
       Exceptions.Hide() 
      Else 
       connection.Close() 
      End If 

     Catch ex As Exception 
      MessageBox.Show(ex.ToString) 
      connection.Close() 
     End Try 
     Exceptions.Show() 
    End Sub 

    Private Sub payrollButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollButton.Click 
     Payrollfinal.Show() 
    End Sub 
End Class 

，当我运行此代码，我得到以下错误

System.Data.SQLClient.SQLException： 语法错误的日期时间 字符串转换，线路57

这是这一行：

adapter.Fill(ds) 

当我调试这个，我把换行符，看看表值，它显示0，但我知道那个时间框架有数据。任何人都可以请协助？

Answer 1

检查了这一点 - 你可能有可空日期时间问题： Linky