Passing a declared value to a sql query

[dougancil]

I have 2 public variables that I declared in form1 of an application. I am trying to call that variable in form2 and then pass that variable in a sql query.

If I declare

Public Class Form1
Public payPeriodStartDate, payPeriodEndDate As Date

How then to I declare that variable in form2 and how to I pass it to my sql query.

Here is the code I have for form1:

Imports System.Data.SqlClient

Public Class Form1
    Public payPeriodStartDate, payPeriodEndDate As Date

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
        Form2.payPeriodStartDate = payPeriodStartDate
        Form2.payPeriodEndDate = payPeriodEndDate
        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
        Form2.Show()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        EmployeeEditform.Show()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class

and here is form2:

Public Class Form2
    Public payPeriodStartDate, payPeriodEndDate As Date
    Private localVariable As Integer
    Public Property ReturnValue() As Integer
        Get
            Return localVariable
        End Get
        Set(ByVal value As Integer)
            localVariable = value
        End Set
    End Property

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MDRDataSet.Exceptions' table. You can move, or remove it, as needed.
        Me.ExceptionsTableAdapter.Fill(Me.MDRDataSet.Exceptions)
        
    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 Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Form4.Show()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Form1.payrollButton.Enabled = True
        Form1.exceptionsButton.Enabled = False
        Me.Close()
    End Sub

End Class

I've used the datagrid wizard to create my sql query on this form. Here is the query:

SELECT Employeenumber, Exceptiondate, Starttime, Endtime,Duration, Code, Submittedby FROM Exceptions where [Exceptions].exceptiondate between '" & payperiodStartDate & "' and '" & payPeriodEndDate & "'

and when I debug this form, no data will appear. I'm sure that it's something wrong with how I'm either calling the variable in form2 or in the query. Can anyone offer any assistance on this?

Thank you

Doug

[jlego]

uhm

form2 code

form2.payperioddate = form1.payperioddate

update table set column = me.payperioddate

end form 2 code

?