Answer to Question #180243 in Visual Basic for najam ul hassan

Question

 Suppose we have the following student table in database. You have to write:  

 Form load event to show all records in grid view when you run the application.

 Event handler for “Semester 6 th Student” button to shows all those students’ data in grid view that has semester value 6 and gpa value is greater than or equal to 3.

Answer 1

1. Create a new VB.Net project:

2. Add DataGridView:

3. Add the button:

4. Add a new Item

5. Add a new query:

6. Insert the following script:

CREATE TABLE [dbo].[Student] (
    [Id]        INT          NOT NULL,
    [FirstName] VARCHAR (20) NULL,
    [LastName]  VARCHAR (20) NULL,
    [Semester]  INT          NULL,
    [GPA]       FLOAT (53)   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);



INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (1, N'Mike', N'Clark', 5, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (2, N'Wilson', N'Mccubbin', 3, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (3, N'John', N'Petrillo', 5, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (4, N'Peter', N'Hummer', 6, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (5, N'Gloria', N'Fenderson', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (6, N'Margaret', N'Willams', 4, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (7, N'Ella', N'Ostrowski', 2, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (8, N'Clifford', N'Nilsen', 1, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (9, N'Dennis', N'Niemeyer', 6, 5)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (10, N'Emil', N'Lingenfelter', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (11, N'Harold', N'Lewallen', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (12, N'Winfield', N'Laurence', 6, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (13, N'Levi', N'Langan', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (14, N'Adolph', N'Guyette', 5, 5)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (15, N'Maurice', N'Fluellen', 3, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (16, N'Cornelius', N'Coelho', 4, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (17, N'Felix', N'Bopp', 2, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (18, N'Reuben', N'Beesley', 3, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (19, N'Wallace', N'Agustin', 5, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (20, N'Claud', N'Vale', 4, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (21, N'Phillip', NULL, 6, 3)

7. Add the following code into the file "Form1.vb":

Imports System.Data.SqlClient


Public Class Form1
    Private databaseConnectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\StudentsDatabase.mdf;Integrated Security=True;Connect Timeout=30"
    ''' <summary>
    ''' Form load event to show all records in grid view when you run the application.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            Dim table As New DataTable("tblStudents")
            Using connection As New SqlConnection(databaseConnectionString)
                Using adapter As New SqlDataAdapter("SELECT * from Student", connection)
                    adapter.Fill(table)
                End Using
            End Using


            'display students
            dgvStudents.DataSource = table
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    ''' <summary>
    ''' Event handler for “Semester 6 th Student” button to shows all those students’
    ''' data in grid view that has semester value 6 and gpa value is greater than or equal to 3.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub btnSemesterStudent_Click(sender As Object, e As EventArgs) Handles btnSemesterStudent.Click
        Try
            Dim table As New DataTable("tblStudents")
            Using connection As New SqlConnection(databaseConnectionString)
                Using adapter As New SqlDataAdapter("SELECT * from Student where Semester=6 and gpa>=3", connection)
                    adapter.Fill(table)
                End Using
            End Using


            'display students
            dgvStudents.DataSource = table
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

Examples: