none
sum the number of hours for each month RRS feed

  • Question

  • Hello
    I Have sql table, which contains daily work hours for two years 2020 and 2021.
    I want to sum the number of hours for each month into these two years.
    my code is

    Private Sub btndgv_Click(sender As Object, e As EventArgs) Handles btndgv.Click
            Dim dt As New DataTable
            Dim lst As New List(Of Integer) _
        From {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}
            For Each item As Integer In lst
                Using con As New SqlConnection(My.Settings.Setting)
                    Dim cmd As New SqlCommand("SELECT sum([Ob1])'Ob1',sum([Ob2])'Ob2' ,sum([Ob3])'Ob3' FROM natt WHERE month([Datum]) = lST and year([Datum]) = 2020", con)
                    con.Open()
    
                    dt.Load(cmd.ExecuteReader())
                End Using
            Next
            DataGridView1.DataSource = dt
        End Sub
    i got error message
    System.Data.SqlClient.SqlException: 'Invalid column name' lST '. "
    can you help me
    thanks


    Wednesday, April 1, 2020 9:23 AM

Answers

  • This code compiles just fine but noticed you are loading the DataTable on each iteration which means only the last iteration is used in regards to loading the DataTable. If there are still errors it must be due something I can't see from what you have shown.

    If you have SSMS you can generate a script for the table with the option to include data then I can import and run your code to see what's going on.

    Private Sub btndgv_Click(sender As Object, e As EventArgs) Handles btndgv.Click
        '
        ' This is syntactically correct, I don't know if the column
        ' names are valid
        '
        Dim selectStatement =
                "SELECT sum(Ob1) as Ob1Sum,sum(Ob2) as Ob2Sum ,sum(Ob3) as Ob3Sum " &
                "FROM natt " &
                "WHERE month([Datum]) = @MonthIndex  And year([Datum]) = @CurrentYear"
    
        Dim dt As New DataTable
        Dim currentYear = 2020
    
        Dim monthIndies As New List(Of Integer) From
                {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}
    
    
        Using cn As New SqlConnection(My.Settings.Setting)
    
            Using cmd As New SqlCommand(selectStatement, cn)
                '
                ' Define parameters once
                '
    
                '
                ' Value set in for
                '
                cmd.Parameters.Add(New SqlParameter() With {
                                      .ParameterName = "@MonthIndex",
                                      .SqlDbType = SqlDbType.Int})
    
                '
                ' Value is set if there is a need to do another 
                ' year set in when needed
                '
                cmd.Parameters.Add(New SqlParameter() With {
                                      .ParameterName = "@CurrentYear",
                                      .SqlDbType = SqlDbType.Int, .Value = currentYear})
    
                cn.Open()
    
                '
                ' This does not make sense as the final iteration
                ' is the only data which will be seen
                '
                For Each monthIndex As Integer In monthIndies
    
                    cmd.Parameters("@MonthIndex").Value = monthIndex
                    dt.Load(cmd.ExecuteReader())
    
                Next
    
            End Using
        End Using
    
        DataGridView1.DataSource = dt
    
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Majed Abbas Wednesday, April 1, 2020 12:13 PM
    Wednesday, April 1, 2020 10:48 AM
    Moderator

All replies

  • Try this

    Dim cmd As New SqlCommand($"SELECT sum(Ob1) as Ob1Sum,sum(Ob2) as Ob2Sum ,sum(Ob3) as Ob3Sum FROM natt WHERE month([Datum]) = {lST} and year([Datum]) = 2020", con)

    Or

    Dim cmd As New SqlCommand("SELECT sum(Ob1) as Ob1Sum,sum(Ob2) as Ob2Sum ,sum(Ob3) as Ob3Sum FROM natt WHERE month([Datum]) = " & lst.ToString() & "And year([Datum]) = 2020", con)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Wednesday, April 1, 2020 9:47 AM
    Moderator
  • its not working
    Wednesday, April 1, 2020 10:00 AM
  • its not working
    Please elaborate

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, April 1, 2020 10:28 AM
    Moderator
  • This code compiles just fine but noticed you are loading the DataTable on each iteration which means only the last iteration is used in regards to loading the DataTable. If there are still errors it must be due something I can't see from what you have shown.

    If you have SSMS you can generate a script for the table with the option to include data then I can import and run your code to see what's going on.

    Private Sub btndgv_Click(sender As Object, e As EventArgs) Handles btndgv.Click
        '
        ' This is syntactically correct, I don't know if the column
        ' names are valid
        '
        Dim selectStatement =
                "SELECT sum(Ob1) as Ob1Sum,sum(Ob2) as Ob2Sum ,sum(Ob3) as Ob3Sum " &
                "FROM natt " &
                "WHERE month([Datum]) = @MonthIndex  And year([Datum]) = @CurrentYear"
    
        Dim dt As New DataTable
        Dim currentYear = 2020
    
        Dim monthIndies As New List(Of Integer) From
                {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}
    
    
        Using cn As New SqlConnection(My.Settings.Setting)
    
            Using cmd As New SqlCommand(selectStatement, cn)
                '
                ' Define parameters once
                '
    
                '
                ' Value set in for
                '
                cmd.Parameters.Add(New SqlParameter() With {
                                      .ParameterName = "@MonthIndex",
                                      .SqlDbType = SqlDbType.Int})
    
                '
                ' Value is set if there is a need to do another 
                ' year set in when needed
                '
                cmd.Parameters.Add(New SqlParameter() With {
                                      .ParameterName = "@CurrentYear",
                                      .SqlDbType = SqlDbType.Int, .Value = currentYear})
    
                cn.Open()
    
                '
                ' This does not make sense as the final iteration
                ' is the only data which will be seen
                '
                For Each monthIndex As Integer In monthIndies
    
                    cmd.Parameters("@MonthIndex").Value = monthIndex
                    dt.Load(cmd.ExecuteReader())
    
                Next
    
            End Using
        End Using
    
        DataGridView1.DataSource = dt
    
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Majed Abbas Wednesday, April 1, 2020 12:13 PM
    Wednesday, April 1, 2020 10:48 AM
    Moderator
  • its not working

    No, but the error is probably gone. 

    People try to help you here, and the only response you make is then. 

    its not working. 

    Do you pay that people?

    Even marking answers seems to much work for you.


    Success
    Cor

    Wednesday, April 1, 2020 12:00 PM