Doug Ancil

Doug Ancil

  • NA
  • 16
  • 0

SQL Variable Declaration error

Jan 19 2011 3:48 PM

I have the following form:


 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.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 Sub
End 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


Answers (5)