Skip to main content

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


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.

  1. CREATE TABLE `tblstudent` (
  2. `idno` int(11) NOT NULL,
  3. `fname` varchar(90) NOT NULL,
  4. `mname` varchar(90) NOT NULL,
  5. `lname` varchar(90) NOT NULL,
  6. `address` varchar(90) NOT NULL,
  7. `contactno` varchar(90) NOT NULL

Execute the following query to add the data in the table that you have created.
  1. INSERT INTO `tblstudent` (`idno`, `fname`, `mname`, `lname`, `address`, `contactno`) VALUES
  2. (2019001, 'Mike', 'Pelarmo', 'Asoncion', 'Kabankalan City', '09827262521'),
  3. (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.

  1. 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.

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

Step 5

Create a function to retrieve data in the database.

  1. Public Function retrieve_single_result(ByVal sql As String)
  2. Dim maxrow As Integer = 0
  3.  
  4. Try
  5. con.Open()
  6. cmd = New MySqlCommand
  7. da = New MySqlDataAdapter
  8. dt = New DataTable
  9. With cmd
  10. .Connection = con
  11. .CommandText = sql
  12. End With
  13. da.SelectCommand = cmd
  14. da.Fill(dt)
  15.  
  16. maxrow = dt.Rows.Count
  17.  
  18. Catch ex As Exception
  19. MsgBox(ex.Message)
  20. Finally
  21. con.Close()
  22. da.Dispose()
  23. End Try
  24. Return maxrow
  25. 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.

  1. Private Sub txt_idno_TextChanged(sender As Object, e As EventArgs) Handles txt_idno.TextChanged
  2. sql = "SELECT * FROM `tblstudent` WHERE `idno`=" & txt_idno.Text
  3. mxrow = retrieve_single_result(sql)
  4. If mxrow > 0 Then
  5. With dt.Rows(0)
  6. txt_fname.Text = .Item("fname")
  7. txt_mname.Text = .Item("mname")
  8. txt_lname.Text = .Item("lname")
  9. txt_address.Text = .Item("address")
  10. txt_contactno.Text = .Item("contactno")
  11. End With
  12. Else
  13. For Each txt As Control In GroupBox1.Controls
  14. If TypeOf txt Is TextBox Then
  15. txt.Text = ""
  16. End If
  17. Next
  18. End If
  19.  
  20. 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.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.