-1

How can pull selected fields of a record from an access database and put it into a label? This is what I have so far:

Using conn As New OleDbConnection(Get_Constring)

conn.Open()

Using cmd As OleDbCommand = conn.CreateCommand
    If cb_Stat.Text = "Top Scorer" Then
        cmd.CommandText = "SELECT MAX(Goals) FROM PlayerDatabase"
        Label1.Text = cmd.ExecuteScalar
    End If

End Using

conn.Close()

End Using

What happens is the max number of goals scored by a player from the playerdatabase is put into the string which is 10. How can I get it to also pull out the data from the Forename and Surname fields of the same record and display it as "John Doe 10" instead of just "10"

dave
  • 41
  • 1
  • 6

2 Answers2

3

You can use order by and get only one row. The ANSI standard method is:

SELECT p.*
FROM PlayerDatabase p
ORDER BY goals DESC
FETCH FIRST 1 ROW ONLY;

Not all databases support the last clause. Some use select top 1. Others limit 1, and some are even more arcane.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Instead of using Max aggregate use order by plus top 1 to get the player who scored max number of goals.

Then concatenate the Forename, Surname, Goals columns. Try this.

SELECT Top 1 Forename &" "& Surname &" "& cast(Goals as varchar(10)) 
FROM PlayerDatabase
order by Goals desc
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • I changed my originial SQL statement for what you suggested and now I'm getting an error at the Label1.Text = cmd.ExecuteScalar line. The error is: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll – dave Jan 25 '15 at 18:35