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

Submitted by: 
Visitors have accessed this post 477 times.

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.


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.