Help converting this vb6 code to vb.net?

[modfather]

I'm hoping someone can help me out. I'm trying to convert the code below from VB6 to VB.NET. I'm not sure of the best way to go. This is basically a simple application to do scoring for a competition. I'm using an Access database and all of this code happens "behind the scenes" when all of the scores have been entered on another screen (that piece is done). If someone could help me, I'd really be grateful... I'm not necessarily looking for someone to re-write it all for me (but I wouldn't complain either - ha ha) - but if I could get some ideas, it sure would be great...

Thanks!
Steve

Private Sub cmdAllDone_Click()
rsCompetition.FindFirst "[CompNum] = " & "'" & txtCompNum2 & "'"
If rsCompetition.NoMatch = True Then
MsgBox "Sorry, try again"
txtCompNum2.SetFocus
txtCompNum2.SelStart = 0
txtCompNum2.SelLength = Len(txtCompNum2)
End If
Call ComputePoints("TotalScoreA&quot
Call RankJudge("TotalScoreA", "RankA", "IrishPointsA&quot
Call ComputePoints("TotalScoreB&quot
Call RankJudge("TotalScoreB", "RankB", "IrishPointsB&quot
Call ComputePoints("TotalScoreC&quot
Call RankJudge("TotalScoreC", "RankC", "IrishPointsC&quot
ComputePlaces
PrintReport
End Sub

Private Sub ComputePoints(strt1 As String)
Dim strt3 As String
strt3 = "SELECT * " _
& "FROM Results WHERE " _
& "CompNum = " & "'" & txtCompNum2 _
& "' AND ScoreA1 > 0 " _
& "ORDER BY " & strt1 & " DESC"
Set rsSort = dbs.OpenRecordset(strt3)
End Sub

Private Sub RankJudge(strt1 As String, strt2 As String, strt3 As String)
Dim tempScore As Single
Dim myTempScore As Single
Dim kounter As Integer
Dim tempPoints As Integer 'points from rsIrishPoints
Dim xxxRank As Integer
Dim tempRank As Integer 'Ranking by judge - includes possibility for tie
Dim varBookmark As Variant
Dim varBookmarkB As Variant
Dim tieKntr As Integer

tempScore = 0
kounter = 0
tempRank = 0
With rsSort
.MoveFirst
Do Until .EOF
kounter = kounter + 1
tempScore = rsSort.Fields(strt1).Value
If (rsSort.Fields(strt1).Value > 0) Then
Call CheckForTies(tempScore, rsSort!Cardnum, tempRank, strt1, strt2, strt3, myTempScore, tieKntr)
Else
rsSort.Edit
rsSort.Fields(strt2).Value = 0
rsSort.Fields(strt3).Value = 0
rsSort.Update
End If
.MoveNext
Loop
rsCompetition.Edit
rsCompetition!numofCompetitors = .RecordCount
rsCompetition.Update
.Close
End With
End Sub
Private Sub CheckForTies(tempScore As Single, tempCardNum As Integer, tempRank As Integer, strt1 As String, strt2 As String, strt3 As String, myTempScore As Single, tieKntr As Integer)
Dim cond2 As String
Dim kounter As Integer
Dim strT7 As String
Dim numTie As Integer
Dim actualRank As Integer 'ranking - if tie, skip
Dim myTempRank As Integer
strT7 = "SELECT * FROM Results WHERE " _
& "CompNum = " & "'" & txtCompNum2.Text & "'" _
& " AND " & strt1 & " = " & tempScore _
& &quot; AND &quot; & strt1 & &quot; > 0 AND CardNum <> &quot; _
& tempCardNum _
& &quot; ORDER BY CardNum DESC&quot;
Set rsSort2 = dbs.OpenRecordset(strT7)
tempRank = tempRank + 1
numTie = rsSort2.RecordCount + 1
With rsSort2
.MoveLast
.MoveFirst
End If
End With
If tempScore <> myTempScore Then
tieKntr = 1
ElseIf tempScore = myTempScore And numTie > 1 Then
tieKntr = tieKntr + 1
End If
rsSort.Edit
rsSort.Fields(strt3).Value = 0
myTempRank = tempRank
'MsgBox diffTempRank
kounter = 0
Do Until kounter >= numTie
kounter = kounter + 1
rsIrishPoints.FindFirst &quot;[Rank] = &quot; & myTempRank 'Kounter
rsSort.Fields(strt3).Value = rsSort.Fields(strt3).Value _
+ (rsIrishPoints!Score / numTie)
myTempRank = myTempRank + 1
Loop
myTempScore = tempScore
rsSort.Fields(strt2).Value = tempRank 'Kounter
If tieKntr > 1 And tieKntr = numTie And tempScore = myTempScore Then
' we had a tie and we're on the last record of the tie
tempRank = tempRank + numTie ' - 1
End If
If tieKntr >= 1 And numTie > 1 Then
tempRank = tempRank - 1
End If
If rsCompetition!Prelim = True Or _
rsCompetition!Open = True Then
rsSort!TotalScore = rsSort!IrishPointsA _
+ rsSort!IrishPointsB _
+ rsSort!IrishPointsC
Else
rsSort!TotalScore = rsSort!TotalScoreA
End If
rsSort.Update
End Sub
Private Sub ComputePlaces()
Dim strt3 As String
Dim tempScore As Single
Dim kounter As Integer
Dim tempRank As Integer
strt3 = &quot;SELECT * &quot; _
& &quot;FROM Results WHERE &quot; _
& &quot;CompNum = &quot; & &quot;'&quot; & txtCompNum2 _
& &quot;' AND ScoreA1 > 0 &quot; _
& &quot;ORDER BY TotalScore Desc&quot;
Set rsSort = dbs.OpenRecordset(strt3)

tempScore = 0
kounter = 0
tempRank = 0
With rsSort
.MoveFirst
rsCompetition.Edit
rsCompetition!HighestScore = rsSort!TotalScore
rsCompetition.Update
Do Until .EOF
kounter = kounter + 1
If tempScore = rsSort!TotalScore Then
'tie
'numTie = numTie + 1
tempRank = tempRank - 1
Else
'no tie
'numTie = 0
End If
tempRank = tempRank + 1
tempScore = rsSort!TotalScore
.Edit
rsSort!NetRank = 0
Select Case tempRank
Case 1 To 4
rsSort!NetRank = tempRank
Case 5
If rsCompetition!numofCompetitors >= 11 Or rsCompetition!Prelim = True Or rsCompetition!Open = True Then
rsSort!NetRank = tempRank
ElseIf rsCompetition!CompDesc = &quot;Reel&quot; Then
rsSort!NetRank = 99
End If
Case 6
If rsCompetition!numofCompetitors >= 21 Or rsCompetition!Prelim = True Or rsCompetition!Open = True Then
rsSort!NetRank = tempRank
ElseIf rsCompetition!CompDesc = &quot;Reel&quot; Then
rsSort!NetRank = 99
End If
Case 7
If rsCompetition!numofCompetitors >= 25 Or rsCompetition!Prelim = True Or rsCompetition!Open = True Then
rsSort!NetRank = tempRank
ElseIf rsCompetition!CompDesc = &quot;Reel&quot; Then
rsSort!NetRank = 99
End If
Case Else
If rsCompetition!CompDesc = &quot;Reel&quot; Then
rsSort!NetRank = 99
ElseIf rsCompetition!Prelim = True Or rsCompetition!Open = True Then
rsSort!NetRank = tempRank
Else
rsSort!NetRank = 0
End If
End Select
rsSort!GrossRank = tempRank
.Update
.MoveNext
Loop
.Close
End With

End Sub

 

[RobSchultz]

Some tips...

Variant doesn't exist: Use &quot;As Array&quot; or &quot;As Object&quot;

Use &quot;Imports System.Data.OleDB&quot; so you can connect to a whole host of managed code for Access (et.al) databases.

For brevity's sake lines like

tempRank = tempRank + 1

can be

tempRank += 1

Those are the only major/minor issues for this type of program I saw after a quick once-over.

HtH,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[RiverGuy]

Yes, and another tip for a question like this is to highligh specific areas you are having trouble with. You won't get as many answers asking for someone to convert an entire program for you; especially when parts of the program are missing, i.e., the PrintReport routine.

 

[modfather]

Rob,

Thanks for the tips. I guess my main concern is: I want to do this right, and using DAO is now dead. I have other another module I did that uses DataTables and works great, but that's a form. This is primarily just computing a few things to populate a database table. The real problem I have is: I don't know how to handle the logic of moving through a DataTable and updating other records while still in a loop of the DataTable. For instance, consider this &quot;Pseudo-Code&quot; (keeping in mind that I have maybe 20 records, each with a &quot;Score&quot; for a competitor:

1. Loop through all competitors for this competition, in order of highest to lowest score.
2. Check to see if there are any other records (other than the current one) with the same score.
3. If there is a tie, loop through those (we may have more than two with a tie). I need to know how many tied.
4. If there is a tie, I need to take all of the points for those spots that tied (points are determined by your ranking) and add them up and divide by the number that tied. At this point, we don't want to include any of the tied people when we now go back to #1 and continue.
5. If there is no tie for this position, assign a certain number of &quot;Points&quot; to this record and go back to #1.

It's somewhat complicated, but I'm giving it the ol' college try. I plan to repost where I'm at later today to see if I can give a more focused question or two...

Thanks again, Rob.
Steve

 

[modfather]

Riverguy,

You're 100% right. I apologize. I plan to be a bit more focused later today. As far as the PrintReport sub, it's just a sub that was used with an older version of Crystal Reports to actually just print the results...

Anyway, sorry for the bad post. I was going to be out yesterday all day, so thought I'd just post everything and see if I'd get lucky with some general help on how I can navigate through records due to the complexity I mentioned in my previous post.

Thanks for the tip and I'll try to be a bit more specific.
Steve

 

[benlinkknilneb]

modfather,

You spoke of a version you had that used data table objects, but assumed they were sorted. This is kinda tough to accomplish with the datatable object. I don't know how helpful this would be to you, but I've written an extended DataTable class called &quot;SuperDataTable&quot; that has the ability to sort by any field in the table.

You can find it here: faq796-4691

If you use that, you can just treat the table as an array and go right down the list looking for your desired values. You could even use a Binary search with it (also coded straight into the class) to help you find a value, and look +1 or -1 to see if it duplicates.

My sorts don't work in descending order yet... if you modify the code, send it to me and I'll edit my FAQ post.

HTH

Ben

There's no place like 127.0.0.1.