Passing values between forms and presenting sql data in datagrid view

Question

dougancil 0 Junior Poster in Training

13 Years Ago

I have two forms and I am running a sql query to provide a set of variables in my first form that I need to pass to my second form. Here is the code that I have:

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=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

The values that I'm declaring are payrollstartdate and payrollenddate and then I need to pass the values that you can see on these two lines:

payPeriodStartDate = oDr.GetDateTime(1)
payPeriodEndDate = payPeriodStartDate.AddDays(7)

to form 2 which then shows the data retrieved from a sql query in the load event to a datagrid view. Can anyone offer any kind of assistance with my code on how to properly do this?

thank you

Doug

vb.net

Edited 13 Years Ago by dougancil because: n/a

2 Contributors
30 Replies
347 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by dougancil

zinnqu 7 Junior Poster in Training

13 Years Ago

Do you want to pass the data from form to form? Or would you rather "INSERT" the data then pull the new data from the SQL database?

This is a consideration that needs to be used in the planning of your program. One way means you declare a global var that is on both forms which is done like this

'from form one
payPeriodStartDate = oDr.GetDateTime(1)
payPeriodEndDate = payPeriodStartDate.AddDays(7)
Form2.payPeriodStartDate = payPeriodStartDate
Form2.payPeriodEndDate = payPeriodEndDate

The other requires a new table or columns added to you existing table so you can "insert" the values then "Select" the information on the second.

if that is the case then you need to look at this link

zinnqu 7 Junior Poster in Training

13 Years Ago

Zinnqu,

I think I can just pass the data from form1 to form2. I'm using stored procedures to run the rest of my queries, but those aren't happening until later. So I'm assuming that I can do the same code for form3 (if I have one) as well? Lastly, as you can see I'm executing a second sql query later in my form that the results I want to populate in a datagridview on form2. How would I go about doing that?

So this portion

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

needs to present data in that range back to end users who can then edit that data.

For the first question: Yes you can pass data in the same method pushing data to the other form via Form3.GlobalVar. The only thing that you need to make sure of is that you are making the globals public.

'Form 1/2/3
imports system.whatever

'...
'...

Public GlobalVar as whatever

For the second question: The SQL query that you have, You need to bind the query to a datagrid first. Change your Try code to this:
*NOTE your datagrid should also be public ( I think that it is but....)

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()
    Dim ds As DataSet = new DataSet()
    .Fill(ds)
    ' Bind Your DataGrid to the DataSet. 
    Form2.DataGrid.DataSource = ds 
    Form2.DataGrid.DataBind()
  End With
  oDr.Close()
  oCmd.Connection.Close()

Catch ex As Exception
  MessageBox.Show(ex.Message)
  oCmd.Connection.Close()
End Try

Edited 13 Years Ago by zinnqu because: n/a

zinnqu 7 Junior Poster in Training

13 Years Ago

Form2 should be whatever you named your second form, and the same with the third. The forms need to be public as well. Here is a modified version of the code that will handle the errors and allow the fill. I am not sure what handles your query, if its a variable or something else then replace the comment in quotes with the appropriate information.

Public Class Exceptions

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ConnectionString as String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter
        Dim connetionString As String
        Dim ds As New DataSet
        connection = New SqlConnection(connetionString)

        Try  
                connection.Open()
                adapter = New SqlDataAdapter("Your SQL Statement Here", connection)
                adapter.Fill(ds)
                connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
    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
        ExceptionEdit.Show()
    End Sub

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

End Class

The other way of handling data is to create a module that will hold the data your passing. This way the module is global and so is the data. This way you can pass the information from form to form without "FormX is not declared" Create the module and name your class. Add your global vars that you set in them for the program and all is well.

module module1
'module1 

public class mydata

dim firstparam as string
dim secondparam as string
dim thirdparam as string

end class

end module

Change the names of the vars and call them like so

mydata.firstparam = whatever

using a module allows for better programming practice only exposing the part that you need and not the whole project. This will place all the data you need in a central place and can be recalled by any form or a new form ect..

zinnqu 7 Junior Poster in Training

13 Years Ago

Tested and proven data passing method as follows, form one and two listed:

Public Class Form1

    Sub test() 'sub to set data
        Form2.global1 = "test" 'pass data to new form
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        test() ' run the sub to set
        Form2.Show() ' show the new form

    End Sub
End Class

Public Class Form2
    Public global1 As String 'Global var that the program sees
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Label1.Text = global1 'set label with new values.
    End Sub
End Class

Note that Both classes are global

zinnqu 7 Junior Poster in Training

13 Years Ago

zinnqu,
What about this as a module?
Module Module1
    Public payperiodstartdate As Date
    Public payperiodenddate As Date
End Module
What would be the benefit of creating a class as opposed to what I have done? Is your code more concise or more efficient than mine? I'm trying to write the best code that I can so when my project is done I won't have to go back later and over write my code.

Keep it your way as it is simple to remember. I uses classes in methods only to segregate data stores. Meaning that if I have more data for other parts of a program, I would separate them for their need. ie mydata for data myaddr for a ip address or physical. Its more of a personal preference than anything. Experiment and see what works best for you.

For the Second part, you need to copy exactly what i gave you earlier for the connection

line 13 is the error. the connection needs to look like this:

Dim adapter As System.Data.SqlClient.SqlDataAdapter
        Dim connetionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        connection = New SqlConnection(connetionString)

        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)
                adapter.Fill(ds)
                connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try

This will solve your error and create the dataset you need. PS get rid of the reader, its not needed at this point

Edited 13 Years Ago by zinnqu because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dougancil 0 Junior Poster in Training · Answer 1 · 2011-01-19T02:37:49+00:00

Do you want to pass the data from form to form? Or would you rather "INSERT" the data then pull the new data from the SQL database?
This is a consideration that needs to be used in the planning of your program. One way means you declare a global var that is on both forms which is done like this
'from form one
payPeriodStartDate = oDr.GetDateTime(1)
payPeriodEndDate = payPeriodStartDate.AddDays(7)
Form2.payPeriodStartDate = payPeriodStartDate
Form2.payPeriodEndDate = payPeriodEndDate
The other requires a new table or columns added to you existing table so you can "insert" the values then "Select" the information on the second.
if that is the case then you need to look at this link

Zinnqu,

I think I can just pass the data from form1 to form2. I'm using stored procedures to run the rest of my queries, but those aren't happening until later. So I'm assuming that I can do the same code for form3 (if I have one) as well? Lastly, as you can see I'm executing a second sql query later in my form that the results I want to populate in a datagridview on form2. How would I go about doing that?

So this portion

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

needs to present data in that range back to end users who can then edit that data.

dougancil 0 Junior Poster in Training · Answer 2 · 2011-01-19T03:39:10+00:00

Ok so I've changed my code to reflect yours but for some reason, it's telling me that form2 is not declared and that fill is not a member of System.Data.SqlClient.SqlCommand.

This is form2:

Public Class Exceptions

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        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.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payPeriodStartDate)
                .Parameters.AddWithValue("@payperiodenddate", payPeriodEndDate)
                .CommandText = "sp_opsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
    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
        ExceptionEdit.Show()
    End Sub

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

End Class

dougancil 0 Junior Poster in Training · Answer 3 · 2011-01-19T21:19:41+00:00

zinnqu,

What about this as a module?

Module Module1
    Public payperiodstartdate As Date
    Public payperiodenddate As Date
End Module

What would be the benefit of creating a class as opposed to what I have done? Is your code more concise or more efficient than mine? I'm trying to write the best code that I can so when my project is done I won't have to go back later and over write my code.

dougancil 0 Junior Poster in Training · Answer 4 · 2011-01-19T21:46:06+00:00

Also,

Here's what I've written. I kind of merged your code and what I already had and obviously I missing some things:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim adapter As System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        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
                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"
                adapter.fill(ds)
                .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 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

The two things that intellisense is telling me is that for the query, it's expecting an end of the statement. I've tried both parenthesis and an = sign before the statement and neither work. Secondly, this line:

Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

is telling me that on the DataGridView1_CellContentClick that "Handles Clause requires a WithEvents variable defined in the containing type or one of it's base types.

dougancil 0 Junior Poster in Training · Answer 5 · 2011-01-19T22:42:24+00:00

What about the other error for the datagridcellcontentclick event error? I fixed the code for the rest.When the Exceptions window (form 2) opens, I'm still not seeing any data. Actually there's no data for this time period in my query so that brings me to the point of presenting a pop up to the user stating that there is no data for this time period ... what would be the best way to present that?

zinnqu 7 Junior Poster in Training · Answer 6 · 2011-01-19T23:41:44+00:00

What about the other error for the datagridcellcontentclick event error? I fixed the code for the rest.When the Exceptions window (form 2) opens, I'm still not seeing any data. Actually there's no data for this time period in my query so that brings me to the point of presenting a pop up to the user stating that there is no data for this time period ... what would be the best way to present that?

Sorry, I read it but forgot to address it.
Add this to the top of your form, just under the class name

Protected WithEvents DataGridView1 as DataGridView

dougancil 0 Junior Poster in Training · Answer 7 · 2011-01-19T23:50:53+00:00

Thank you for that fix. So then what's the best way to have an pop up window stating that there is no data in form2, if there is no data? I'd just like the window to say something like, "There was no data for this time period, press Ok to continue" and that would close the form2 window and also the pop up window (of course) ... I know how to close the windows but I don't quite know how to provide the user with that error in a pop up.

zinnqu 7 Junior Poster in Training · Answer 8 · 2011-01-20T00:17:49+00:00

Thank you for that fix. So then what's the best way to have an pop up window stating that there is no data in form2, if there is no data? I'd just like the window to say something like, "There was no data for this time period, press Ok to continue" and that would close the form2 window and also the pop up window (of course) ... I know how to close the windows but I don't quite know how to provide the user with that error in a pop up.

make this to the try section and this will do everything

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)
   adapter.Fill(ds)
   If (ds Is Nothing) Or (ds.Rows.Count =0 ) Then
   'it's empty
       MsgBox("There was no data for this time period, press Ok to continue", "No Data")
       connection.Close()
       Form2.Hide()
   Else
   'it exists and there are rows 
      adapter.Fill(ds)
      connection.Close()
   End if

Catch ex As Exception
   MessageBox.Show(ex.ToString)
   connection.Close()
End Try

dougancil 0 Junior Poster in Training · Answer 9 · 2011-01-20T01:06:27+00:00

Thank you. So I modified my code with your code and now I'm getting a sqlexception error that I must declare the value for @payperiodstartdate on line 59 of my code. Which is this line:

adapter.Fill(ds)

Here is the entire 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

What am I not seeing?

zinnqu 7 Junior Poster in Training · Answer 10 · 2011-01-20T05:43:24+00:00

Thank you. So I modified my code with your code and now I'm getting a sqlexception error that I must declare the value for @payperiodstartdate on line 59 of my code. Which is this line:
What am I not seeing?

OK Final Code that you should just paste..... This will catch all exemptions and errors. Sorry for the confusion.

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
        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"
	connection = New SqlConnection(connectionString)
	Dim _CMD as SqlCommand = New SqlCommand(_sql, connection)
	adapter.SelectCommand = _CMD
	_CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", payperiodstartdate))
	_CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", payperiodenddate))
Try
   adapter.Fill(ds)
   If (ds Is Nothing) Or (ds.Rows.Count =0 ) Then
   'it's empty
       MsgBox("There was no data for this time period, press Ok to continue", "No Data")
       connection.Close()
       Form2.Hide()
   Else
   'it exists and there are rows 
      adapter.Fill(ds)
      connection.Close()
   End if

Catch ex As Exception
   MessageBox.Show(ex.ToString)
   connection.Close()
End Try

If this helps please close the thread and vote.

dougancil 0 Junior Poster in Training · Answer 11 · 2011-01-20T21:43:54+00:00

Zinnqu,

I have changed my code and on line 12 (of your code) intellisense tells me:

Variable adapter before it has been assigned a value. A null reference could result at runtime.

zinnqu 7 Junior Poster in Training · Answer 12 · 2011-01-20T21:53:49+00:00

Zinnqu,
I have changed my code and on line 12 (of your code) intellisense tells me:
Variable adapter before it has been assigned a value. A null reference could result at runtime.

Go ahead, like this;

Dim adapter As System.Data.SqlClient.SqlDataAdapter = nothing

That is a warning and not an error. You could also move the declaration. that would look something like this:

Dim _CMD as SqlCommand = New SqlCommand(_sql, connection)
Dim adapter As System.Data.SqlClient.SqlDataAdapter
adapter.SelectCommand = _CMD

dougancil 0 Junior Poster in Training · Answer 13 · 2011-01-20T22:39:30+00:00

Zinnqu

Here is my code:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        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
        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"
        connection = New SqlConnection(connectionString)
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = Nothing
        adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", payperiodstartdate))
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", payperiodenddate))
        Try
            adapter.Fill(ds)
            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
                'it exists and there are rows 
                adapter.Fill(ds)
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub

and when I run it now, debug stops in line 52 and tells me that NullReferenceException was unhandled.

zinnqu 7 Junior Poster in Training · Answer 14 · 2011-01-20T22:53:43+00:00

Zinnqu

Here is my code:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        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
        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"
        connection = New SqlConnection(connectionString)
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = Nothing
        adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", payperiodstartdate))
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", payperiodenddate))
        Try
            adapter.Fill(ds)
            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
                'it exists and there are rows 
                adapter.Fill(ds)
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub

and when I run it now, debug stops in line 52 and tells me that NullReferenceException was unhandled.

remove the " = Nothing" since you moved it. That was my error. I apologize if that was unclear in the last post.

dougancil 0 Junior Poster in Training · Answer 15 · 2011-01-20T23:18:38+00:00

dougancil 0 Junior Poster in Training

13 Years Ago

I have removed it and still get the same error.

zinnqu 7 Junior Poster in Training · Answer 16 · 2011-01-20T23:21:33+00:00

I have removed it and still get the same error.

What exactly is the line 52 on your program that is referenced?

dougancil 0 Junior Poster in Training · Answer 17 · 2011-01-20T23:24:31+00:00

dougancil 0 Junior Poster in Training

13 Years Ago

adapter.SelectCommand = _CMD

zinnqu 7 Junior Poster in Training · Answer 18 · 2011-01-21T06:11:55+00:00

adapter.SelectCommand = _CMD

try doing this to overwrite the issue.

adapter.SelectCommand = New SqlCommand(_sql, connection)

comment out the other lines on this post #11 ( line #50 in your program)
Your program for some reason did not see the _CMD as a active item and returned a null in its place. Force the command to see your connection and hopefully this time it will work.

dougancil 0 Junior Poster in Training · Answer 19 · 2011-01-21T21:39:23+00:00

Ok so here's the code I added:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        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"
        connection = New SqlConnection(connectionString)
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        adapter.SelectCommand = _CMD
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodstartdate", payperiodstartdate))
        _CMD.Parameters.Add(New SqlClient.SqlParameter("@payperiodenddate", payperiodenddate))
        Try
            adapter.Fill(ds)
            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
                'it exists and there are rows 
                adapter.Fill(ds)
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub

and now when I try to debug it, disassembly is giving me an error

0000010d  mov         rax,qword ptr [rbp+18h]

dougancil 0 Junior Poster in Training · Answer 20 · 2011-01-22T03:51:40+00:00

Zinnqu,

I actually found the code on another form that was causing this error. The debug was deceptive and was telling me that the break was happening before it actually was. I removed the code and the page works, but I'm still not seeing the msgbox when I have no data. It was suggested to me to remove the "into" portion of the sql query, which I did, to no avail.

zinnqu 7 Junior Poster in Training · Answer 21 · 2011-01-22T04:23:47+00:00

Zinnqu,
I actually found the code on another form that was causing this error. The debug was deceptive and was telling me that the break was happening before it actually was. I removed the code and the page works, but I'm still not seeing the msgbox when I have no data. It was suggested to me to remove the "into" portion of the sql query, which I did, to no avail.

Into was not the issue, the message part is missing the or statement that I gave you

if (ds is nothing) or (ds.rows.count = 0) then

even if ds returns a 0 it will logically pass the "is nothing" part of the test because 0 is a placeholder to any .NET language. Try that. If not we can give it code to force the exemption before the test.

dougancil 0 Junior Poster in Training · Answer 22 · 2011-01-22T04:34:15+00:00

and when I add your code, intellisense tells me that Rows is not a member of System.Data.Data.Set

zinnqu 7 Junior Poster in Training · Answer 23 · 2011-01-22T04:51:47+00:00

and when I add your code, intellisense tells me that Rows is not a member of System.Data.Data.Set

ds.Tables(0).Rows.Count

Sorry I for got to specify the table lol

dougancil 0 Junior Poster in Training · Answer 24 · 2011-01-24T21:19:31+00:00

Ok so I've modified the code and get the following error:

System.IndexOutofRangeException: Cannot find table 0
Line 58, which is this line:

If (ds Is Nothing) Or ds.Tables(0).Rows.Count Then

Do I need to put the table name in here?