gridview sortexpression of updated values?

[tqeonline]

I have a gridview that is binded to a SQLDatasource. When the gridview is Databound I have it updated the values in Column 17.

Basically that column is called "Health" and i have the query just returning "1" as the value... then I have the code behind doing a calculation and setting that cell to whatever the result is.

However when I try to sort by the new expression on the gridview in the webpage... IT sorts off of the Databound values ("1s") , I need it to sort off the next values which range from 0 to 15.

this is how I set the new value:

e.Row.Cells(16).Text = Health

Any ideas on how to update the sortexression to recognize the new values?



- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[ca8msm]

Any reason you don't do the calculation in the data as if you can that might save you a lot of time?

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[tqeonline]

because in the calculation i'm putting weights on individual components and it'd put too much load on the sql server and i'd rather have it allocated to the webserver.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[jbenson001]

The calculations should be done on the SQL Server. A SQL Server is ment to handle heavy loads of data and data maipulation. Webservers are really only designed to serve up HTML which is just plain text. Although they can beefy servers, it makes more sense to do what you want on the SQL Server.

 

[tqeonline]

i'm not opposed to doing it. I'm just not sure how I would go about it.

How would I declare a variable (health) and then add to it during the calculations?

The calculations are done at the server side... but the weightings i have on the code-behind side.

Ex. Say "Total Scripts" = 50
Case when Total Scripts < 25 then Health = 1
Case when Total Scripts > 25 then health = 2

Health = 2 in this example... but then the next row i'd have to do Health = Health + (calculation)

Here are the current weights:

If Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Total Scripts")) <> 0 _
                And Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Test Start Date")) < DateAdd(DateInterval.Day, 1, Date.Now) _
                And (Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Actual %")) < 100 _
                Or Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Active Defects")) > 0) Then
                'Active Defects
                ActiveDefects1 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Active Defects-1"))
                Select Case ActiveDefects1
                    Case Is <= 3.0
                        Health = Health + 0
                    Case 3.1 To 5
                        Health = Health + 1
                    Case 6 To 10.0
                        Health = Health + 2
                    Case Is > 10
                        Health = Health + 3
                End Select

                'Critical Defects
                CriticalDefects2 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Critical Defects-2"))
                Select Case CriticalDefects2
                    Case 0
                        Health = Health + 0
                    Case 0.1 To 0.33
                        Health = Health + 1
                    Case 0.34 To 0.66
                        Health = Health + 2
                    Case Is > 0.66
                        Health = Health + 3
                End Select

                'Effort Remainding / Days Left
                EffortDays3 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Effort/Days Left-3"))
                Select Case EffortDays3
                    Case Is <= 1.0
                        Health = Health + 0
                    Case 1.0 To 2.0
                        Health = Health + 1
                    Case 2.1 To 4.0
                        Health = Health + 2
                    Case Is > 4.0
                        Health = Health + 3
                End Select

                'Actual % / Projected %
                ActualProjected4 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Actual/Projected-4"))
                Select Case ActualProjected4
                    Case Is > 0.95
                        Health = Health + 0
                    Case 0.661 To 0.95
                        Health = Health + 1
                    Case 0.33 To 0.66
                        Health = Health + 2
                    Case Is < 0.33
                        Health = Health + 3
                End Select

                'Auto Scripts Left / Total Scripts Left
                AutomatedLeft5 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Automated/Left-5"))
                Select Case AutomatedLeft5
                    Case Is >= 0.5
                        Health = Health + 0
                    Case Else
                        Health = Health + 1
                End Select

                'Total Scripts Left
                Left6 = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Left-6"))
                Select Case Left6
                    Case Is <= 25
                        Health = Health + 0
                    Case 26 To 50
                        Health = Health + 1
                    Case 50 To 75
                        Health = Health + 2
                    Case Is > 75
                        Health = Health + 3
                End Select

                'Comparison for Test End Date
                Dim TestEnd As Date = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Test End Date"))
                Dim DateNow As Date = Date.Now
                Dim TestEndCompare As String = DateDiff(DateInterval.Day, TestEnd, DateNow)
                If TestEnd < DateNow Then
                    Select Case TestEndCompare
                        Case 1
                            Health = Health + 2
                        Case 2
                            Health = Health + 4
                        Case 3
                            Health = Health + 8
                        Case Is > 3
                            Health = Health + 16
                    End Select
                End If
            End If

So I'd need these in the SQL statement but am not familiar with declaring and updating variables in SQL.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[jbenson001]

You would also use a SQL CASE statement. However, the CASE in SQL is NOT the same as .NET. It would not be constructed the same. It can be a bit confusing at first. If you have a SQL person you can go to, I suggest you work with them on it. However, I don't think this is relevant to your original question. I belive the problem is because you are using a SQLDataSource control. Where do you have the code that you posted(In what event)?

 

[tqeonline]

gvCycles_RowDataBound

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008