Declaring global variables and passing them to a sql query - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Declaring global variables and passing them to a sql query

dougancildougancil Posts: 4Member
I am building an application that I have to declare 2 variables for every form and pass them to my SQL Stored procedures. I have the following code:

[code]
Imports System.Data.SqlClient
Public Class Main
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 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
[/code]

My two variables you can see are coming from this statement in my code:

[code]
payPeriodStartDate = oDr.GetDateTime(1)
payPeriodEndDate = payPeriodStartDate.AddDays(7)
[/code]

What I need to do is to declare those on the Load event and pass them to my sql stored procedures BUT I need to declare them globally because each form needs to have those values passed to it. Can someone offer some guidance as to the best way to accomplish this?

Thank you

Doug

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    Hi doug,

    There are SQL Server functions that accomplish what you are trying to do, you can slip them directly into your SQL.

    [b]Look up: [/b]
    SQL GetDate()
    and
    SQL DateAdd(unit, number, date)

    For your case
    replace @payperiodstartdate with GetDate()
    and
    replace @payperiodenddate with Dateadd(day,7,GetDate())

    Hope that helps.

    Otherwise you'd be having to manage changing that DateTime object in VB every time the function is called, and if you are doing that, it seems illogical to declare a global variable (since it technically is not going to have a value that is held statically for other operations to use, rather just memory space that is constantly reserved)

    Perhaps I misunderstood your question, please correct me if I am wrong,
    Sean Campbell - Firesickle
  • dougancildougancil Posts: 4Member
    Sean,

    What I did for my case was to create a module with the values in it. That seems to work fine for my purposes.
  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    Normally, I would have suggested that. That creates variables at the Application level (a module works like a Static Class if that means anything to you)...

    However, I was providing you a SQL Solution so you could get the DateTime of the SQL Server rather than the Date Time of the computer that is executing the code.

    Happy Coding sir,
    Sean Campbell - Firesickle
Sign In or Register to comment.