VB.NET Aggregation Problem

[Chris Ewe]

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

[crapulency]

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)

[reach_yousuf]

Yes,

Crapulency is not saying crap!!!

Item 20 doesnot exist in this context

sqlReader.GetValues(sqlReader.Item(20))

[kvprajapati]

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