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.
Create a database named “studentdb”
Execute the following query to create a table.
- (2019001, 'Mike', 'Pelarmo', 'Asoncion', 'Kabankalan City', '09827262521'),
- (2019002, 'Kevin', 'Lim', 'Tan', 'Ilog, Negros Occidental', '09827626272');
Open Microsoft Visual Studio 2015 and create a new windows form application in visual basic.
Do the form just like shown below.
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
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
Create a function to retrieve data in the database.
- Public Function retrieve_single_result(ByVal sql As String)
- Dim maxrow As Integer = 0
- cmd = New MySqlCommand
- da = New MySqlDataAdapter
- dt = New DataTable
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- da.SelectCommand = cmd
- maxrow = dt.Rows.Count
- Catch ex As Exception
- End Try
- Return maxrow
- End Function
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
- For Each txt As Control In GroupBox1.Controls
- If TypeOf txt Is TextBox Then
- txt.Text = ""
- End If
- End If
- End Sub
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.