Data adapter not filling my dataset
- by Doug Ancil
I have the following code:
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(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') 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.AddSeconds(604799)
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
and when I run my program and press this button
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
I have my date range within a time that I know that my dataset should produce a result, but when I put a line break in my code here:
adapter.Fill(ds)
and look at it in debug, I show a table value of 0. If I run the same query that I have to produce these results in sql analyser, I see 1 result. Can someone see why my query on my form produces a different result than the sql analyser does?
Also here is my schema for my two tables:
Exceptions
employeenumber varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
exceptiondate datetime no 8 yes (n/a) (n/a) NULL
starttime datetime no 8 yes (n/a) (n/a) NULL
endtime datetime no 8 yes (n/a) (n/a) NULL
duration varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
code varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approvedby varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approved varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
time timestamp no 8 yes (n/a) (n/a) NULL
employees
employeenumber varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
name varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
initials varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
loginname1 varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
