Help with VB6 SQL query

[navaidstech]

OK...guys... I'm about to have a cow here. It sounds real simple but I just can't seem to get it.
Here is the deal: I have a normalized database made up of four tables (Primary Key in bold)

Player (PlayerID, PlayerName)
NHLTeam (NHLTeamID, PoolTeamName)
PoolTeam (PoolTeamID, PoolTeamName)
PlayerStats (PlayerID, NHLTeamID, PoolTeamID, Points)

My software already displays the list of NHL Teams (or Pool Teams for that matter) in a MSFlexGrid - that's the easy part. When I select a team from that grid, I want the software to populate another MSFlexGrid that will contain a list of players that play for that particular team and sort them by the amount of points they have accumulated.

My question is: can this be done, and if so how? I would appreciate any help as I'm running out of hairs to pull.

Thanks!
Alex

[QVeen72]

Hi,

Well.. You could have Normalized further, with Just One Master for both Pool Team and NHL Team.. :

NPTeam (TeamID, TeamName, TeamType)
TeamType >> N or P

Your PlayerStats will be :
PlayerStats (PlayerID, TeamID, Points)

Anyway, Populate the other team by opening this Query :
Say, Column 1 , displays the Pool Team Name, then to get list of players :

The Query is for PoolTeam:

Dim sSQL As String
sSQl = "Select  P.PlayerName, Sum(PS.Points) From " _
  & " PlayerStats PS, Player P, PoolTeam PT Where  " _
  & " PS.PlayerID = P.PlayerID And " _
  & " PS.PoolTeamID = PT.PoolTeamID And " _
  & " PT.PoolTeamName='" & Grd.TextMatrix(Grd.Row, 1) & "'" _
  & " Group By P.PlayerName "

Write the code to populate in either Click event of the Grid/ or place a command button and write in its Click event..

Regards
Veena

[navaidstech]

Hi,

Well.. You could have Normalized further, with Just One Master for both Pool Team and NHL Team.. :

NPTeam (TeamID, TeamName, TeamType) TeamType >> N or P

Yes... that is correct. In fact, that's how the database was initially designed but I decided to break it apart to make team updates easier (it's a long story).

Thank you for your reply. I can't believe it's that easy.
I'll plug your snippet into my code and report later.

Thanks again Veena!
Alex

[navaidstech]

Veena, it worked flawlessly. I analyzed the Query and I think I'm getting it (finally). I had to modify it a bit as your query was adding up all the points for that player and I was interested in individual points. No biggie, modification worked great.

One more question if you don't mind. The idea here is to create a table (Flexgrid) of players and their points. Once you select a player from that table, there will be a separate text box that will display that player's points. Reason for that is to allow the user to edit points and such. So far so good. The problem I'm running into takes place after I try to update the recordset, it tells me that the dataset is read-only. I have a feeling that the method I used to display the data in the first place is not correct. Here is what I did:
I set up a Data Control called datPlayers MSFlexGrid called PlayerList and a Command button which I use to run the query. The FlexGrid uses datPlayers as the data source. This is the code that I've assigned to the button:

Dim sSql As String

datPlayers.DatabaseName = App.Path & "\data.mdb"
    
sSql = "Select Players.PlayerName... blah blah blah ...Order By PlayerStats.Points Desc"

datPlayers.RecordSource = sSql
datPlayers.Refresh

Obviously I'm doing something wrong and I was wondering if it would be possible for you to steer me in the right direction.

Thanks again!

Alex

[QVeen72]

Hi,

When you have opened recordset with multiple tables, the Recordset becomes ReadOnly.. What you have to do is open a DAO's Database Object and Run SQL Update Queries.. something like this :

Dim DB As Database
Dim i As Integer
Dim sSQL as String
Set DB = OpenDatabase(App.Path & "\data.mdb")
For i  = 1 To Grd.Rows- 1
  sSQl = "UpDate MyTable Set myFld = " & Grd.TextMatrix(1,4) _
    & " Where  Fld1 = '" & Grd.TextMatrix(1,1) & "' And Fld2 ='" _
    & Grd.TextMatrix(1,2) & "'"
  DB.Execute sSQL
Next
DB.Close

Change Field/Table Column Names accordingly..

Regards
Veena

[navaidstech]

Dim DB As Database
Dim i As Integer
Dim sSQL as String
Set DB = OpenDatabase(App.Path & "\data.mdb")
For i  = 1 To Grd.Rows- 1
  sSQl = "UpDate MyTable Set myFld = " & Grd.TextMatrix(1,4) _
    & " Where  Fld1 = '" & Grd.TextMatrix(1,1) & "' And Fld2 ='" _
    & Grd.TextMatrix(1,2) & "'"
  DB.Execute sSQL
Next
DB.Close

OK, I see.... so this routine is attached to the Command button that executes the save routine, correct?
And I'm assuming that (1,4), (1,1) and (1,2) are just numbers that you pulled out of a hat. All I really need to do is update a particular cell in one row of that FlexGrid, so all I will need to do is provide just one set of coordinates, right?

Thanks
ALex

[navaidstech]

Veena,

it worked like a charm.
I had to get my grey matter to think a little and modify the query a bit but in the end it does exactly what I want.

Thank you very much for your help, I really appreciate it.

Alex