I have the following form:
Imports System.Data.SqlClientPublic 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.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 adapter As System.Data.SqlClient.SqlDataAdapter Dim connection As System.Data.SqlClient.SqlConnection Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx" Dim ds As New DataSet connection = New SqlConnection(connectionString) Try connection.Open() adapter = New SqlDataAdapter("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) If (ds Is Nothing) Then 'it's empty MsgBox("There was no data for this time period, press Ok to continue", "No Data") connection.Close() Exceptions.Hide() Else adapter.Fill(ds) connection.Close() End If Catch ex As Exception MessageBox.Show(ex.ToString) connection.Close() End Try Exceptions.Show() End Sub Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End SubEnd Class
and when I run my app, I get a SQL error that tells me that I need to declare a value for '@payperiodstartdate' but I know that that value is declared because the first query runs fine. I am told that the line where the error is is line 59, which is this line:
adapter.Fill(ds)
Can someone please tell me what I may be missing?
Thank you
Doug