Find Items inside the ListBox Based on TextBox Using C# and MySQL Database

Submitted by janobe on Wednesday, July 31, 2019 - 18:18.
In this tutorial, I will teach you how to find items in the ListBox using C# and MySQL Database. This method has the ability to find the data from the database and display it inside the ListBox. This procedure is very easy and you can be done in no time. Just follow the step by step guide to know how it works

Creating Database

1. Install the XAMPP on your machine. 2. Open the XAMPP and start MySQL and Apache 3. Open the browser and type this url (localhost/phpmyadmin) to access PHPMyAdmin. 4. Create a new database named “dbsubjects” 5. Do the following query to add a table and data in the database that you have created.
  1. CREATE TABLE `subject` (
  2.   `SUBJ_ID` int(11) NOT NULL,
  3.   `SUBJ_CODE` varchar(30) NOT NULL,
  4.   `SUBJ_DESCRIPTION` varchar(255) NOT NULL,
  5.   `UNIT` int(2) NOT NULL,
  6.   `PRE_REQUISITE` varchar(30) NOT NULL DEFAULT 'None'
  8.  
  9. --
  10. -- Dumping data for table `subject`
  11. --
  12.  
  13. INSERT INTO `subject` (`SUBJ_ID`, `SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `PRE_REQUISITE`) VALUES
  14. (8, 'English Plus', 'English Plus', 3, 'Nones'),
  15. (14, 'NSTP1', 'National Service Training Program 1', 3, 'None'),
  16. (15, 'PE1', 'Physical Education 1', 2, 'None'),
  17. (16, 'HUMAN', 'Humanities', 3, 'None'),
  18. (17, 'COMART2', 'Communication Arts 2', 3, 'COMART1'),
  19. (18, 'COPRO-2', 'Computer Programming 2', 4, 'COPRO1'),
  20. (19, 'DATSTRUC', 'Data Structures', 4, 'COPRO1'),
  21. (20, 'DISTRUC', 'Discrete Structure', 3, 'ALGEBRA'),
  22. (21, 'NSTP2', 'National Service Training Program 2', 3, 'None'),
  23. (22, 'INTROART', 'Introduction to Arts', 3, 'None'),
  24. (23, 'PE2', 'Physical Education 2', 2, 'PE1'),
  25. (24, 'TRIGO', 'Trigonometry', 3, 'ALGEBRA'),
  26. (25, 'COMART3', 'Communication Arts 3', 3, 'COMART2'),
  27. (26, 'COPRO-3', 'Computer Programming 3', 4, 'COPRO2'),
  28. (27, 'LOGIC', 'Logic Design and Switching', 3, 'DISTRUC'),
  29. (28, 'PHILGOV', 'Philippine Government', 3, 'None'),
  30. (29, 'PHYSICS1', 'Physics 1', 4, 'None'),
  31. (30, 'STAT', 'Statistics', 3, 'ALGEBRA'),
  32. (31, 'SOCSTUD', 'Social Studies', 3, 'None'),
  33. (32, 'PE3', 'Physical Education 3', 2, 'PE1'),
  34. (33, 'COMART4', 'Communication Arts 4', 3, 'COMART3'),
  35. (34, 'ASSEM', 'Assembly Language', 4, 'LOGIC'),
  36. (35, 'PHILO', 'Philosophy', 3, 'None'),
  37. (36, 'SADSIGN', 'System Analysis and Design', 3, 'COPRO1'),
  38. (37, 'PHYSICS2', 'Physics 2', 4, 'Physics 1'),
  39. (38, 'DBSYS', 'Theory Database Systems', 3, 'DATSTRUC'),
  40. (39, 'PE4', 'Physical Education 4', 2, 'PE1'),
  41. (40, 'Eng 111', 'CommunicationArts 1', 3, 'none'),
  42. (41, 'Fil 111', 'Kom sa Akad. Fil', 3, 'NONE'),
  43. (42, 'Math 1', 'Basic math ', 3, 'NONE'),
  44. (43, 'SCE 111', 'Earth Science', 3, 'NONE '),
  45. (44, 'Lit 111', 'Philippines Literature ', 3, 'NONE'),
  46. (45, 'Rdg 1', 'Dev. Reading 1', 3, 'NONE'),
  47. (46, 'Psych 116', 'General Psychology', 3, 'NONE'),
  48. (47, 'PE 111', ' Physical Fitness', 2, 'NONE'),
  49. (48, 'NSTP 111', 'National Service training program 1 ', 3, 'NONE'),
  50. (49, 'Eng 211', 'speech & oral communication', 3, 'Eng 121'),
  51. (50, 'Fil 211', 'Masining na pagpapahayag', 3, 'Fil 121'),
  52. (51, 'Lit 121 ', 'World literature', 3, 'Lit 111'),
  53. (52, 'SocSci 132', 'Rizal & other heroes ', 3, 'NONE'),
  54. (53, 'Ed 211', 'Child & adolescent Psychology', 3, 'Psyh 116'),
  55. (54, 'Ed 212', 'Education Technology 1', 3, 'NONE'),
  56. (55, 'FS 1', 'Observation of learners Dev\'t & the school enviroment  ', 3, 'NONE'),
  57. (56, 'Eng 212', 'The Teaching Of speaking ', 3, 'NONE '),
  58. (57, 'Eng 213', 'The teaching of lsting & Rdg.', 2, 'NONE'),
  59. (58, 'PE 211', 'Team sports', 2, 'PE 121 '),
  60. (59, 'Stat 115', 'Intro. to Statistics ', 3, 'NONE'),
  61. (60, 'Ed 311', 'The teaching profession ', 3, 'NONE'),
  62. (61, 'Ed 312', 'Assessment of Teaching 2 ', 3, 'Ed 221'),
  63. (62, 'Ed 313', 'Principles of teaching', 3, 'Ed 224'),
  64. (63, 'FS 3', 'Micro-Teaching &the  use of technology', 1, 'FS 2'),
  65. (64, 'Eng 311', 'Arfo- Asian Literature ', 3, 'NONE '),
  66. (65, 'Eng 312', 'Introduction to Stylistics ', 3, 'NONE'),
  67. (66, 'Eng 313', 'Translating & Editing of  text ', 3, 'NONE'),
  68. (67, 'Eng 314', 'Teaching Literature', 3, 'NONE'),
  69. (68, 'Ed 314', ' Teaching Multigrade Class', 1, 'NONE'),
  70. (69, 'Ed 412', 'Use of popular Media ', 1, 'NONE'),
  71. (70, 'FS 5', 'Assestment of student Learning ', 1, 'FS 4'),
  72. (71, 'FS 6 ', 'Becoming a Teacher', 1, 'FS 5'),
  73. (72, 'Hum116', 'Art , Man & Society', 3, 'Hum 111'),
  74. (73, 'Eng 411', 'language & literature assestment', 3, 'NONE'),
  75. (74, 'Eng 412', 'lang. Curr. for sec. sch.', 3, 'NONE'),
  76. (75, 'Eng 413', 'Literary Criticism ', 3, 'NONE'),
  77. (76, 'Eng 414', 'Language Research ', 3, 'NONE'),
  78. (77, 'Eng 415', 'Dramatic & Stagecrafts', 3, 'NONE '),
  79. (78, 'CS 113', 'Basic Software Package w/ Internet Application', 3, 'None'),
  80. (79, 'Eng 111', 'Communication Arts', 3, 'None'),
  81. (80, 'Fil 111', 'Kom. sa Akad. Fil', 3, 'None'),
  82. (81, 'Math 1', 'Basic Math', 3, 'None'),
  83. (82, 'SCE 111', 'Earth Science', 3, 'None'),
  84. (83, 'Read 1', 'Dev. Reading 1', 3, 'None'),
  85. (84, 'Psych 116', 'General Psycology', 3, 'None'),
  86. (85, 'PE 111', 'Physical Fitness', 2, 'None'),
  87. (86, 'NSTP 111', 'Nat\'l Service Trng. Prog. 1', 3, 'None'),
  88. (87, 'Eng 211', 'Speech and Oral Communication', 3, 'Eng 121'),
  89. (88, 'Fil 211', 'Masining na Pagpapahayag', 3, 'Fil 121'),
  90. (89, 'Lit 121', 'World Literature', 3, 'Lit 111'),
  91. (90, 'SocSci 132', 'Rizal and Other Heroes', 3, 'None'),
  92. (91, 'Ed 211', 'Child Psycology', 3, 'Psych 116'),
  93. (92, 'Ed 212', 'Educational Technology 1', 3, 'None'),
  94. (93, 'FS 1', 'Observation of Learning Dev\'t and the School Environment', 1, 'None'),
  95. (94, 'Eng 212', 'The Teaching of Speaking', 3, 'Eng 121'),
  96. (95, 'Math 3', 'Elem. Theory of Numbers', 3, 'Math 2'),
  97. (96, 'PE 211', 'Team Sports', 2, 'PE 121'),
  98. (97, 'JEEP Start 1', ' ', 3, 'None'),
  99. (98, 'Stat 115', 'Intro. To Statistics', 3, 'None');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for c#. ps1

Step 2

Add a ListBox,Label, and a TextBox inside the Form. Then do the Form just like shown below. ps2

Step 3

Press F7 to open the code editor. In the code editor, add a namespace to access MySQL libararies.
  1.      
  2. using MySql.Data.MySqlClient;

Step 4

Create a connection between C# and MySQL database. After that, declare and initialize all the classes and variables that are needed.
  1.    
  2.         MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=;database=dbsubjects;sslMode=none");
  3.         MySqlCommand cmd;
  4.         MySqlDataAdapter da;
  5.         DataTable dt;
  6.         string sql;

Step 5

Create a method for retrieving data in the database and display it inside the ListBox.
  1.        
  2.         private void retrieve_item(string sql,ListBox lst)
  3.         {
  4.             try
  5.             {
  6.                 con.Open();
  7.                 cmd = new MySqlCommand();
  8.                 da = new MySqlDataAdapter();
  9.                 dt = new DataTable();
  10.  
  11.                 cmd.Connection = con;
  12.                 cmd.CommandText = sql;
  13.  
  14.                 da.SelectCommand = cmd;
  15.                 da.Fill(dt);
  16.  
  17.                 lst.DataSource = dt;
  18.                 lst.DisplayMember = dt.Columns[1].ColumnName;
  19.                 lst.ValueMember = dt.Columns[0].ColumnName;
  20.  
  21.             }
  22.             catch(Exception ex)
  23.             {
  24.                 MessageBox.Show(ex.Message);
  25.             }
  26.             finally
  27.             {
  28.                 con.Close();
  29.                 da.Dispose();
  30.             }
  31.  
  32.         }

Step 6

Go back to the design view, double click the Form to open the load event handler of it. After that, add this code inside the “Form1_Load” event to retrieve all the data in the database.
  1.        
  2.             sql = "SELECT * FROM `subject`";
  3.             retrieve_item(sql, listBox1);

Step 7

Go back to the design view, double click the TextBox to open the TextChanged event handler of it. After that, add this code inside the “textBox1_TextChanged” event to search the specific data in the database.
  1.  
  2.             sql = "SELECT * FROM `subject` WHERE `SUBJ_CODE` LIKE '%" + textBox1.Text + "%'";
  3.             retrieve_item(sql, listBox1);      
The complete source code is included. You can download it and run it on your computer. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.
Tags
Finding items in a listbox using c#
c#
MySQL Database
search items in the listbox

Add new comment

About text formats
  • 330 views