How to declaring store and access "global" variables?

[dougancil]

I have an application that I'm writing and I'm trying to declare two variables from my sql server, store the values and pass them to my forms.

Here is my code:

Imports System.Data.SqlClient
Public Class Main
    Dim instForm2 As New Exceptions
    Dim oDr As SqlDataReader
    Dim payPeriodStartDate = oDr.GetDateTime(1)
    Dim payPeriodEndDate = payPeriodStartDate.AddDays(7)
    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 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"
        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 = sql
                .Parameters.AddWithValue("@payperiodstartdate", payPeriodStartDate)
                .Parameters.AddWithValue("@payperiodenddate", payPeriodEndDate)
                oDr = .ExecuteReader()
            End With
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        Exceptions.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

The variables that I need are

Dim payPeriodStartD ate = oDr.GetDateTime (1)
Dim payPeriodEndDat e = payPeriodStartD ate.AddDays(7)

and I think that I have to call these values from my load event but I'm not sure. Can anyone offer any assistance as to the best way to do this?

Thank you

Doug

[David Gluth]

Your Dim at the top of main won’t work. You can’t set the value to a SQL reader value before you open the reader.
Declare them as public and they will be available to other classes within your application.

Dim payPeriodStartDate = oDr.GetDateTime(1) 		S/B public payPeriodStartDate as Date = nothing
Dim payPeriodEndDate = payPeriodStartDate.AddDays(7)   S/B public payPeriodEndDate as Date =nothing

[dougancil]

David,

I edited my code as follows:

Imports System.Data.SqlClient
Public Class Main
    Dim instForm2 As New Exceptions
    Public payrollstartdate As Date = Nothing
    Public payrollenddate As Date = Nothing
    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=10.2.1.41;uid=sa;password=chili123")
                .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 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"
        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 = sql
                .Parameters.AddWithValue("@payperiodstartdate", payPeriodStartDate)
                .Parameters.AddWithValue("@payperiodenddate", payPeriodEndDate)
                oDr = .ExecuteReader()
            End With
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        Exceptions.Show()
    End Sub

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

    End Sub
End Class

I put a debug on line 27 and looked back at the variables declared .. in the top and see the values as 12:00 AM. They should be (in this case 10/3/2010 and 10/10/2010)

[David Gluth]

I may have keyed it wrong for you -

Variables as defined

Public payrollstartdat e As Date = Nothing
Public payrollenddate As Date = Nothing

Variables you are setting

payPeriodStartD ate = oDr.GetDateTime (1)
payPeriodEndDat e = payPeriodStartD ate.AddDays(7)

These are different variable names

Change the declarations to match the ones you use

[dougancil]

I only have a slight problem with that. I'm not declaring a variable for oDr until the sub starts running.

so here is my code:

Public Class Main
    Dim instForm2 As New Exceptions
    Public payrollstartdate As Date = oDr.GetDateTime(1)
    Public payrollenddate As Date = payPeriodStartDate.AddDays(7)
    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

and the problem is that I don't know how to declare the value for oDr in the top.

[David Gluth]

Sure you are,

Public xxx as date is a declaration (Public means that other forms can see and use it)
Dim xxx as date is a declaration (Dim means that only the procedure that it is in can see it)

The two statements that I gave you before declare the variable for you to use.
" Public payPeriodStartD ate As Date = Nothing"
" Public payPeriodEndDat e As Date = Nothing"

You declare the SQL Reader for local use with
" Dim oDr As System.Data.Sql Client.SqlDataR eader"

You then use the variables by setting their value from something you read with the reader
" payPeriodStartD ate = oDr.GetDateTime (1)"

And then set the value based on the first variables value
" payPeriodEndDat e = payPeriodStartD ate.AddDays(7)"

Imports System.Data.SqlClient 
Public Class Main 
    Dim instForm2 As New Exceptions 
    Public payPeriodStartDate As Date = Nothing 
    Public payPeriodEndDate As Date = Nothing 
    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=10.2.1.41;uid=sa;password=chili123") 
                .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 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" 
        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 = sql 
                .Parameters.AddWithValue("@payperiodstartdate", payPeriodStartDate) 
                .Parameters.AddWithValue("@payperiodenddate", payPeriodEndDate) 
                oDr = .ExecuteReader() 
            End With 
            oDr.Close() 
            oCmd.Connection.Close() 
        Catch ex As Exception 
            MessageBox.Show(ex.Message) 
            oCmd.Connection.Close() 
        End Try 
        Exceptions.Show() 
    End Sub 
  
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
  
    End Sub 
End Class