Hi, appreciate if you can spend few minutes to analyze the problem below and shed some lights. Thanks

I would like to Sum up MTD Value and separate by employee ID. I've used SUM(MTD_Value) and Group By Employee_ID but is not work.

Thanks


VB.NET Coding

Dim connetionString As String
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sql As String

connetionString = "data source=infinitySH-PC; initial catalog=Human_Resource_Management;" & "integrated security=true"

sql = "Select Sum(MTD_Value) from PCB_Est Group by Employee_ID"
        sqlCnn = New SqlConnection(connetionString)
        sqlCnn.Open()
        sqlCmd = New SqlCommand(sql, sqlCnn)
        Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
        Dim X As Decimal
        While sqlReader.Read()
            X = sqlReader.GetValues(sqlReader.Item(20))
        End While
        Response.Write(X)

Error Message
Please refer attached document

Database
Employee ID | MTD_Value
ABC12345 | 30.3
ABC12343 | 50.5

Hi chris,

Isn't sqlReader.Item(20) attempting to get the value of the 21st column when only 2 columns are returned?


Try getting the value of your first column (EmployeeID) with sqlReader.item(0) and your second column (MTD_Value) with sqlReader.item(1). Alternatively use sqlReader.GetString(0) and sqlReader.GetFloat(1)

Yes,

Crapulency is not saying crap!!!

Item 20 doesnot exist in this context

sqlReader.GetValues(sqlReader.Item(20))

Dim connetionString As String
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sql As String

connetionString = "data source=infinitySH-PC; initial catalog=Human_Resource_Management;" & "integrated security=true"

sql = "Select Sum(MTD_Value) from PCB_Est Group by Employee_ID"
        sqlCnn = New SqlConnection(connetionString)
        sqlCnn.Open()
        sqlCmd = New SqlCommand(sql, sqlCnn)
        Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
        Dim X As Decimal
        While sqlReader.Read()
            X = sqlReader.GetValue(0)
       
            Response.Write(X)
        End While
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.