Visual Basic .Net : Search in Access Database – DataGridView BindingSource Filter Part 1/2

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.

Only registered users can comment.

  1. ★ Suggested Videos ★
    ~ YouTube Playlist: VB.Net + MS Access – Sales and Inventory System
    _ _ _

  2. The video may be helpful, but the fastest solution is to download Hetman office recovery tool., that solved my problem

  3. สวัสดีครับ อาจารย์ ผมขอถามได้ไม่ครับ คืผมต้องการอยากเปลี่ยนข้อความใน MessageBox ตามสาพาไทยที่เราต้องการมีวิธีเปลี่ยนมั้ย อาจารย์ ช่วยแนะนำหน่อยครับ ขอบคุณครับ

  4. Halo orang-orang baik saya di Asia, semoga ALLAH dipuji, nama saya Mahmud Simon, warga negara INDONESIA tetapi saya tinggal di sini di MALAYSIA; Saya ingin menggunakan media ini untuk memberi tahu Anda semua tentang kebaikan Tuhan yang akhirnya membawa saya pada pemberi pinjaman yang baik ini melalui Ny. Nurliana Novi, pemberi pinjaman pinjaman yang sangat asli bernama Elina Johnson, direktur ELINA JOHNSON LOAN FIRM, setelah saya ditipu oleh pemberi pinjaman palsu, saya bangkrut, putus asa dan tidak tahu harus berbuat apa dan siapa yang harus percaya, kemudian dia datang dan tersenyum lebar di wajah saya dalam kejutan terbesar saya. Saya tahu sebagian besar dari Anda juga telah menjadi korban penipuan, Anda tidak perlu repot lagi karena saya harus menyampaikan kabar baik dan satu-satunya pemberi pinjaman yang dapat Anda percayai,

    Sekali lagi setelah saya mendapat pinjaman darinya saya berinvestasi dalam bisnis dan saya melihat bahwa saya membutuhkan lebih banyak, saya terkejut ketika ibu memberi saya pinjaman lain selain pinjaman sebelumnya, orang-orang baik saya telah melihatnya dan saya adalah bagian dari pengalaman, hari ini hidupku mendapat giliran baru terima kasih kepada ibu Elina, ya kamu mungkin takut karena kesaksian palsu yang kamu dengar tapi aku saksi dan jika aku berbohong mungkin ALLAH mengambil nyawaku, orang-orangku di sana jika kamu mau pinjaman murni dia adalah satu-satunya yang dapat Anda percayai,

    Nyonya Elina Johnson dari ELINA JOHNSON LOAN FIRM cukup hubungi dia hari ini melalui email: [email protected] »untuk info lebih lanjut tentang cara mendapatkan pinjaman Anda atau Anda dapat menghubungi saya melalui email saya untuk panduan lebih lanjut; [email protected], dan saran tentang cara mengajukan pinjaman dari Ny. Nurliana Novi di [email protected] Saya menunggu untuk mendengar kabar baik Anda sendiri, Terima kasih semua dan semoga ALLAH dipuji.

  5. Hallo allerseits, ich möchte mich bei allen für die Gelegenheit bedanken, mit einem so angesehenen Unternehmen Geld zu verdienen. Ich danke / @t

  6. Сколько шума вокруг этого проекта в определенных кругах… Ну посмотрим

  7. A solution is to open a new College or University Program for those who want to do duty. after many years it will be many different people to select from

  8. It would be better to only allow the Search button to be clicked if there was something in the text box.  Then you can get rid of the on error routine.

  9. Jose Maldonado, there is plenty of technical info. had desired something for a novice in computers as myself. looking forward to rewatching and learning more…

  10. This is a very important video as most programs we use in stores and other workplaces incorporate this mechanism. Before that, cobol was used.

  11. спасибо за инфу. проект любопытный с хорошей перспективой

  12. Now that was a bit of luck coming across your explanation. Cleared everything as you suggested and now I am downloading. It is very misleading on the box as to the amount of disk-space available. Thank you for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *