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 payPeriodStartDate = oDr.GetDateTime(1)
Dim payPeriodEndDate = payPeriodStartDate.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
5 2708
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
-
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)
I may have keyed it wrong for you -
Variables as defined
Public payrollstartdate As Date = Nothing
Public payrollenddate As Date = Nothing
Variables you are setting
payPeriodStartDate = oDr.GetDateTime(1)
payPeriodEndDate = payPeriodStartDate.AddDays(7)
These are different variable names
Change the declarations to match the ones you use
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.
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 payPeriodStartDate As Date = Nothing"
" Public payPeriodEndDate As Date = Nothing"
You declare the SQL Reader for local use with
" Dim oDr As System.Data.SqlClient.SqlDataReader"
You then use the variables by setting their value from something you read with the reader
" payPeriodStartDate = oDr.GetDateTime(1)"
And then set the value based on the first variables value
" payPeriodEndDate = payPeriodStartDate.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
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: mrbog |
last post by:
I have an array/hash that stores path information for my app. As in,
what directory this is in, what directory that's in, what the name of
the site is, what the products are called, etc. It's...
|
by: Chris Stromberger |
last post by:
This doesn't seem like it should behave as it does without using
"global d" in mod().
d = {}
def mod():
d = 3
mod()
print d
|
by: Lyn |
last post by:
Hi and Season's Greetings to all.
I have a question regarding the use of a qualifier word "Global". I cannot
find any reference to this in Access help, nor in books or on the Internet.
"Global"...
|
by: Javaman59 |
last post by:
I saw in a recent post the :: operator used to reach the global namespace, as
in
global::MyNamespace
I hadn't seen this before, so looked it up in MSDN, which explained it
nicely. My question...
|
by: BB |
last post by:
Hello all,
I might be missing something here, but am trying to understand the difference between using application-level variables--i.e. Application("MyVar")--and global variables--i.e. public...
|
by: dave |
last post by:
If I have a class that hold, for instance, user settings that should be
accessible to the entire program logic, what is a good paradigm to use?
In C++, I would have made it a global object,...
|
by: Sir Psycho |
last post by:
Is there a way to set a global variable without using the Session
object?
I basically want to be able to track if a visitor is logged in without
starting a Session. Is there such a thing as a...
|
by: eBob.com |
last post by:
I have this nasty problem with Shared methods and what I think of as "global
storage" - i.e. storage declared outside of any subroutines or functions.
In the simple example below this "global"...
|
by: sap0321 |
last post by:
This should be kindergarten stuff but for some reason I am having trouble with it. I do 99.9% web programming and this is the first windows app i've done in years - probably the first ever in C# ......
|
by: ChrisWang |
last post by:
Hi,
I am having trouble understanding the use of 'global' variables I want to use a global variable with the same name as a parameter of a function. But I don't know how to use them at the same...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |