Database confusion

[Mcdermid]

I've been working on this project that works with a MS Access database. What I need to do with this and what I am having trouble with is I need to select a team name from a listbox and then calculate their total points.

I'm struggling to figure out how I can calculate this.
To help give you an idea of what I'm talking about the list is something like this:

control............... team
10.......................bilby
10...................... gumbles
20...................... bilby
20.......................gumbles
30.......................gumbles
(... = spaces)

So if i were to choose gumbles how do i just calculate all of the control points from the database giving me the result (from the example) 60.

[Ramy Mahrous]

You should do these calcualtions against database not on UI layer.

[Teme64]

Like Ramy said, use SQL functions to sum the points.

Here's a one sample code

Public Function TeamTally(ByVal TeamName As String) As Integer
  ' Return total points for TeamName
  Dim oConn As OdbcConnection
  Dim oCmd As OdbcCommand
  Dim strSQL As String
  Dim QueryResult As Object

  ' Create and open an Odbc connection
  oConn = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\test.mdb;Uid=;Pwd=;")
  oConn.Open()

  ' Tally points
  strSQL = "SELECT SUM(control) AS TeamTally FROM TeamPoints WHERE Team='" & TeamName & "'"

  ' Create an Odbc command
  oCmd = New OdbcCommand(strSQL, oConn)
  ' Execute command. We can use ExecuteScalar since only one value is returned
  QueryResult = oCmd.ExecuteScalar
  ' Close db connection
  oConn.Close()

  ' Test null value
  If IsDBNull(QueryResult) Then
    Return 0 ' Non-existing team, return nil
  Else
    Return CInt(QueryResult)
  End If

End Function

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
  ' Debug
  Dim a As Integer
  Dim b As Integer
  Dim c As Integer

  a = TeamTally("Bilby")
  b = TeamTally("Gumbles")
  c = TeamTally("NotFound")

  MessageBox.Show("Bilby: " & a & ", Gumbles: " & b & ", NotFound: " & c, "Points", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

I made a separate function, which returns the total points for a given team. The second sub is just for testing.

I assumed ODBC connection. You will also have to check and change connection string, table name, field names, import correct namespace and add proper error handling.

[Mcdermid]

Thanks that helped me understand everything a little better but I'm still having some issues as I'm new and learning everything atm.

This is what I've been using to open up the database

connector = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "
connector = connector & "rogaine.mdb"

I've been able to print out reports easy enough into a textfile but i couldn't get TeamTally("teamName") to print out the result to a label.

(teamName = lstTeams.SelectedItem)

Any furthur help would be much appericated and I'll continue trying to figure it all out

[Teme64]

My sample code used Odbc but that's irrelevant.

So, you don't get a team name from a string "10...................... gumbles" which is a line from a list box?

Here's my test sub again

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
  ' Debug
  Dim a As Integer
  Dim b As Integer
  Dim c As Integer

  Dim TempStr As String
  Dim TeamName As String

  ListBox1.Items.Add("10.......................Bilby") ' Debug
  ' This is my debug line. You would use ListBox1.SelectedIndexChanged event and check if an item is selected
  ListBox1.SelectedIndex = 0 ' Debug

  ' Listbox stores Objects, convert to string
  TempStr = ListBox1.SelectedItem.ToString

  ' Do we have a fixed width columns? (First col 25 chars?)
  TeamName = TempStr.Substring(25, TempStr.Length - 25)

  ' Or do we have a variable width first column?
  Dim SepChar As String ' This is a separator character between points and names. I used dot, but you can change this to space etc.
  SepChar = "."
  Dim LastPos As Integer ' Last occurrence (position) of SepChar assuming that team name DOES NOT contain SepChar
  LastPos = TempStr.LastIndexOf(SepChar)
  If LastPos >= 0 Then
    TeamName = TempStr.Substring(LastPos + 1, TempStr.Length - LastPos - 1)
  Else
    ' SepChar not found
    TeamName = "NotFound"
  End If

  a = TeamTally(TeamName)
  b = TeamTally("Gumbles")
  c = TeamTally("NotFound")

  MessageBox.Show(TeamName & ": " & a & ", Gumbles: " & b & ", NotFound: " & c, "Points", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

It shows two methods to get the team name. If you use the latter, just comment out the line TeamName = TempStr.Substring(25, TempStr.Length - 25) . You could actually comment it out anyway, the code would still work.