How do I generate salary structure in below problem please

Question

0.00/5 (No votes)

See more:

I have the following data in my sql table;

rank   grade_level   maximum_step   salary_base   salary_increment
A          15             1             5000.00          0.00
B          14             3             4500.00          205.00
C          13             7             3500.00          185.00

I want to write a vb.net code to read each line and return result below:

rank    grade_level     step_1    step_2    step_3 ........ step_n
A          15           5000.00
B          14           4500.00   4705.00   4910.00 
C          13           3500.00   3685.00   3870.00   4055.00 .......

, where n=highest value in maximum_step field which is 7 in the above example

What I have tried:

VB

Private Sub btnRun_Click(sender As Object, e As EventArgs) Handles btnRun.Click
        Dim str As String
        Dim i As Integer
        str = "select rankCode, gl,maxStep,sBase,sInc,rBase,rInc from ifmis_rank "
        Dim com As New SqlCommand(str, cn)
        Dim rd As SqlDataReader
        lstVAudit.Items.Clear()
        cn.Open()
        With com
            rd = .ExecuteReader(CommandBehavior.CloseConnection)
            With rd
                While .Read
                    lst = New ListViewItem
lst.Text = CType(.Item(0), String)
                    lst.SubItems.Add(CType(.Item(1), String))
for i = 1 to ctype(.item(2),integer)
 lst.SubItems.Add(CType(.Item(3), Decimal))
next 1        
                    lstVAudit.Items.Add(lst)
                End While
            End With
        End With
    End Sub

Posted 26-Jan-18 4:50am

Olaoluwa

Updated 8-Feb-18 1:16am

Maciej Los

v2

Add a Solution

Comments

OriginalGriff 26-Jan-18 11:47am

And?
What does it do that you didn't expect, or not do that you did?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-02-04T08:16:00

If i undestand you correctly, you have to calculate salary_base by adding salary_increment multiplied by n till n reaches maximum_step.

Assuming your data is stored in a DataTable object...

VB.NET

'your code here till
rd = .ExecuteReader(CommandBehavior.CloseConnection)
'then
Dim dt As DataTable = New DataTable()
dt.Load(rd)

'get max of maximum_step
Dim maxstep = dt.AsEnumerable().Max(Function(x) x.Field(Of Integer)("maximum_step")) 

'create final DataTable
Dim finaldt As DataTable = New DataTable()
finaldt.Columns.Add(New DataColumn("rank", Type.GetType("System.String")))
finaldt.Columns.Add(New DataColumn("grade_level", Type.GetType("System.Int32")))
'add "step" columns
For i As Integer = 1 To maxstep
	finaldt.Columns.Add(New DataColumn("step_" & i, Type.GetType("System.Int32")))
Next

For Each r As DataRow In dt.Rows
	Dim destRow As DataRow = finaldt.NewRow()
	maxstep = r.Field(Of Integer)("maximum_step")
	destRow("rank") = r.Field(Of String)("rank")
	destRow("grade_level") = r.Field(Of Integer)("grade_level")
	For i As Integer = 1 To maxstep
		destRow("step_" & i) = r.Field(Of Double)("salary_base") + (r.Field(Of Double)("salary_increment") * i)
	Next
	finaldt.Rows.Add(destRow)
Next

Result (finaldt content):

rank grade_level step_1 step_2 step_3 step_4 step_5 step_6 step_7
A    15          5000   null   null   null   null   null   null 
B    14          4705   4910   5115   null   null   null   null 
C    13          3685   3870   4055   4240   4425   4610   4795

In the same manner you can create ListViewItem[^].

[EDIT]
.NET 2. solution: