In this blog we will use ExecuteScalar method to see a record. Here we can find Min Marks, Max Marks, Avg Marks, Total Marks, Count how many records, Max Mark Sid, Max Marks Sname of student table.

Imports System.Data

Imports System.Data.OleDb

Public Class Form1

Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("dsn")

Dim con As OleDbConnection = New OleDbConnection(ConnectionString)

Dim com As OleDbCommand

Dim oledbda As OleDbDataAdapter

Dim ds As DataSet

Dim str As String

Private Sub btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.Click

Try

con.Open()

str = "select * from student"

com = New OleDbCommand(str, con)

oledbda = New OleDbDataAdapter(com)

ds = New DataSet

oledbda.Fill(ds, "student")

DataGrid1.DataSource = ds

DataGrid1.DataMember = "student"

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub btnmax_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmax.Click

Try

con.Open()

str = "select max(smarks)from student"

com = New OleDbCommand(str, con)

Label1.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub btnmin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmin.Click

Try

con.Open()

str = "select min(smarks)from student"

com = New OleDbCommand(str, con)

Label2.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub btnavg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnavg.Click

Try

con.Open()

str = "select avg(smarks)from student"

com = New OleDbCommand(str, con)

Label3.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub btntotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntotal.Click

Try

con.Open()

str = "select sum(smarks)from student"

com = New OleDbCommand(str, con)

Label4.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub btncount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncount.Click

Try

con.Open()

str = "select count(smarks)from student"

com = New OleDbCommand(str, con)

Label5.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub maxsid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles maxsid.Click

Try

con.Open()

str = "select * from student where smarks=(select max(smarks) from student)"

com = New OleDbCommand(str, con)

Label6.Text = com.ExecuteScalar

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub maxname_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles maxname.Click

Try

con.Open()

str = "select max(smarks)from student"

com = New OleDbCommand(str, con)

Label7.Text = com.ExecuteScalar

str = "select sname from student where smarks=" & CInt(Label7.Text) & ""

com = New OleDbCommand(str, con)

Dim reader As OleDbDataReader = com.ExecuteReader

If reader.Read Then

Label7.Text = reader("sname")

End If

reader.Close()

con.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

End Class

Thanks for reading