Visual Basic .Net : Search in Access Database – DataGridView BindingSource Filter Part 1/2
Hello First, open the project that we have created. (Please see the first video or read the description under this video.) This is the form that we have designed and coded in the last video. The program can be added, edited, updated and deleted data. Open Properties window of Form1 Next, I’ll change the text on Title Bar of Form1 by editing at Text Property (Form.Text Property). In this step, change the text to whatever you like. Next, I’ll change the form icon on the left top corner. *** Please notice that the icon has .ico file extension. Now, the icon of Form1 has changed. Next, I’ll change the form border. Go to FormBorderStyle Property. (Choose any as you like.) Hide Properties window for now by clicking Auto Hide. Open Toolbox window instead by clicking Dock. Expand the Form size for adding more controls. I’ll add one GroupBox Control. Then, expand the size as you want. I’ll change the text on GroupBox. Go to Text Property. This one means to adjust row color of DataGridView. Please follow my lead to change the text. Click and drag ComboBox Control on GroupBox. Then Copy a duplicate. This one is for adjusting the color of the rows. Click and drag Label in too. And Copy one more for telling the working status of ComboBox. Arrange it as you want. Then, change the text of both of Labels. Here, type the text by following my lead. Next, change the name of ComboBox by clicking the first one first. Then, go to Name Property and change the name by following my lead. Click the second ComboBox and change the name by following my lead. Adjust the size and arrange the position of Control as you want. Here, you can follow the example on the video. Then, I’ll add one more Label into Form. This Label is for clicking to Reset the setting. We should name it meaningfully for coding. And change the text on Label too, to a meaningful one. You can adjust more properties such as background color, text color or text size. On Cursor, I’ll change it to Hand. It means when we put our cursor onto this Label, it will be changed to a hand symbol. Drag and adjust the position as you want. Hide Properties window for now by clicking Auto Hide. Next, add Button and TextBox for data searching function. From here, you can follow the example on the video. Please note that if we want to write a code with which Control, we should change the name of the Control to a meaningful one. In the example, I want to write a code on searching button, so change the button name to btnSearch or ButtonSearch. And don’t forget to change the text on the button to a meaningful one. Adjust size and color of the text as you want. Do these same steps to TextBox. You can follow the example. Hide Toolbox window for now. Adjust Form and Control size for a good appearance. Next, adjust the properties of Access Database. Right click and choose Properties. Change Copy to Output Directory from Do Not Copy to Copy if Newer. For letting the program copy Access Database to Project Directory only when there is an edit on the database. Then, click Save. Double Click on Title Bar or the header of Form to look for a code inside. It’ll be Form Load Event. It’ll be Event that happened when Form is opened. We will write a code at Event Form Load to set some settings such as database connection. I’ll adjust DataGridView by using With…End With Statement to help shorten the code. Type With, follow by the name of DataGridView then hit Enter. The program will automatically add End With. Next, we don’t have to claim the name of DataGridView anymore. You can type . (Dot) follow by Property or Method. First command is .ClearSelection Method which is a method for clearing chosen Cell or cancel choosing Cell. Next command is .ReadOnly Property. If set to True, it will protect User to be unable to edit Cell. Default setting is False, it means User is enable to edit Cell. Next command is .MultiSelect Property. Set to False to letting User can choose only one Cell. Default setting is True. (User can choose multiple Cell.) Here, coding by following the example on the video. Next, we’ll add KnownColor to be menu into both of ComboBox. Announce variable Array to String type for collecting all color name from KnownColor Enum. You’ll notice that there is Enum.GetNames(Type) Method used here to help by resetting the setting to String Array. Then, bring all the colors that collected in variable Array to be menu on both ComboBox. Here, you can type the command by following my lead. After finished, click Save. Next step, we will write a code to capture events that happen when SelectedIndex Property of ComboBox has changed. Double Click on ComboBox, it’ll enter the event ComboBox.SelectedIndexChanged. This command means when User choose a menu from ComboBox, the background color of ComboBox will be changed by the color chosen. Chosen menu in ComboBox is SelectedItem Property. Next command is to tell the mouse cursor to Focus to stop blinking at TextBox name txtSearch. Copy 2 lines of code to continue using with the second ComboBox. Double Click on the second or the lower ComboBox. Then, paste the copied code. Adjust the command a bit by changing the name from ComboBox 1’s name to ComboBox 2’s name. Next, we will write a code at Label name lblReset (for resetting settings.). Double Click on Label. The following code is a familiar command because I have shown the example since the first video. Please follow my lead. Next command, I’ll clear Filter of BindingSourced by using BindingSource.Filter Property. Next command is to reset the setting of DataGridView. Specify new DataSource for DataGridView. By using Table1BindingSource that we have cleared Filter. I have already explained some part of the code so I won’t mention it again. Here, if there is an Error, make the program to show Error Number with the explanation of the error. After showing the error, make it run the command ErrEx: Command ErrEx: is to tell the program to quit running. After finished, always Save. Next step, we will write a command on Searching Button. I will write a code to capture errors first. The steps are quite the same. I may not explain this one again. Please follow the example. I’ll check that TextBox name txtSearch is Empty or not. (You also can use String.IsNullOrEmpty(String) Method.) You’ll notice that there is If…Then Else Statement used here to help the program decide. If the set in txtSearch is Empty or User hasn’t typed anything into it, let the program quit running (Exit Sub). Next, if it’s not in the first condition, go to Else (if txtSearch is not Empty). Working here is to let the program searching data in Access. Announce a variable to accept the set from txtSearch to resume using. Here, we will use BindingSource.Filter Property to search data. Searching data here is similar to the command in SQL. Please write the code by following my lead. If we want to search for any Column, insert the name of the Column. Notice that there is LIKE keyword used here to help. This part code is quite long. Please recheck the correctness. We will check that it found the searched data or not by counting the rows, if not equal 0 means found. In the case that the data has been found, specify BindingSource that is already Filtered to DataSource for DataGridView. If the data hasn’t been found, make it show a text to inform User. Here, you can follow my lead. Then, clear BindingSource Filter. Then specify the set to DataSource of DataGridView and reset the settings. If coding is duplicated, we can write in Sub Procedure. We will copy written codes to continue using. Here, you can follow the example on the video. After finished, click Save. Recheck the correctness again. That’s all in this video. See you in the next one. Thank you very much.