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 e
ndtime 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

I can also verify that the variables are passing the correct values. Can anyone please assist as to why this isnt working?

Thank you

Doug

Pass the command to the adapter, before filling the adapter just pass the command to it and see...

This article has been dead for over six months. Start a new discussion instead.