Retrieve Single Result in the Database Using VB.Net and MySQL Database

Submitted by janobe on Wednesday, February 27, 2019 - 21:34.

In this tutorial, I will teach how to retrieve single result in the Database Using VB.Net and MySQL Database. This method has the ability to retrieve the data in the database and display the specific field in the textboxes. It has a function that when you write an ID no. of the student it will then display the student details in the textboxes.

Creating Database

Create a database named “studentdb” Execute the following query to create a table.

CREATE TABLE `tblstudent` (
  `idno` int(11) NOT NULL,
  `fname` varchar(90) NOT NULL,
  `mname` varchar(90) NOT NULL,
  `lname` varchar(90) NOT NULL,
  `address` varchar(90) NOT NULL,
  `contactno` varchar(90) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Execute the following query to add the data in the table that you have created.

INSERT INTO `tblstudent` (`idno`, `fname`, `mname`, `lname`, `address`, `contactno`) VALUES
(2019001, 'Mike', 'Pelarmo', 'Asoncion', 'Kabankalan City', '09827262521'),
(2019002, 'Kevin', 'Lim', 'Tan', 'Ilog, Negros Occidental', '09827626272');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application in visual basic. ps1

Step 2

Do the form just like shown below. ps2

Step 3

Open the code editor by pressing F7 on the keyboard. After that, add a namespace above the public class to access MySQL Libraries.

Imports MySql.Data.MySqlClient

Step 4

Create a connection between Visual Basic 2015 and MySQL Database. After that, declare all the classes that are needed.

   Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=;database=studentdb;sslMode=none")
    Dim cmd As MySqlCommand
    Dim da As MySqlDataAdapter
    Dim dt As DataTable
    Dim sql As String
    Dim mxrow As Integer

Step 5

Create a function to retrieve data in the database.

    Public Function retrieve_single_result(ByVal sql As String)
        Dim maxrow As Integer = 0
 
        Try
            con.Open()
            cmd = New MySqlCommand
            da = New MySqlDataAdapter
            dt = New DataTable
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            da.SelectCommand = cmd
            da.Fill(dt)
 
            maxrow = dt.Rows.Count
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
        Return maxrow
    End Function

Step 6

Double click the “Student ID” textbox to the fire the text_changed event handler of it and do the following codes to retrieve the data in a single result when the text changed.

Private Sub txt_idno_TextChanged(sender As Object, e As EventArgs) Handles txt_idno.TextChanged
        sql = "SELECT * FROM `tblstudent` WHERE `idno`=" & txt_idno.Text
        mxrow = retrieve_single_result(sql)
        If mxrow > 0 Then
            With dt.Rows(0)
                txt_fname.Text = .Item("fname")
                txt_mname.Text = .Item("mname")
                txt_lname.Text = .Item("lname")
                txt_address.Text = .Item("address")
                txt_contactno.Text = .Item("contactno")
            End With
        Else
            For Each txt As Control In GroupBox1.Controls
                If TypeOf txt Is TextBox Then
                    txt.Text = ""
                End If
            Next
        End If
 
    End Sub

Press F5 to run your project. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Add new comment

2493 views