VB .Net Working With DataBase Full Project Example

We already discussed in the book (this chapter) the application's senario and there are already code snippets, we're here for explaining what was going on so we'll study each event separately.
Before we start i want to mention that database table fields' header names must not be an access 2007 reserved words like (Between, Binary, Drop, ......... etc) or VB .Net reserved words like (AddHandler, Catch, MyClass, ........... etc), here is a full reserved words by both VB .Net 2010 and Access 2007.

The General Declaration Code
We called two liberaries
(System.Data.OleDB) and (System.IO)
The System.Data.OleDB Liberary is the.Net Frame-Work 4 (VB .Net 2010's Environment) Data Provider for the OLEDB, it calls the database Jet Engine installed on the machine to work with it inside your VB .Net 2010 Project Application, it's a link between our (Access DB) and our (VB .Net 2010 Project).
We will use its classes to connect to the database and retrieve\store data from\into it.
The System.IO Liberary is all the classes and procedures needed to work with files like reading, writing and storing including Stream Files like Sound Files, Images Files and Binaries.
We'll use its classes later when working with storing photos into database and retrieve it from there into form's control [Picture-Box].
Public Class Form1 Page (3)
PicLocation is a string used to locate the photo and we'll use it later in next chapter when working with photos.
Ctrl1, Ctrl2 are controls used to refer to the TextBoxes and the PictureBox in the Sub ClearAll() in order to clear them.
CN OleDBConnection is a class used to connect to the database path in the connection string which we will provide.
CMD OleDBCommand is a class used to carry\store the SQL Statement after connecting to the CN so CMD has 2 ways (1 connects to CN and 2 parses the SQL Statement).
DataR OleDBDataReader is a class used to read the data received from the CMD Sql Statement after parsing it, it represents the (Online Mode).
dataS DataSet is a class used to read\Write the data received from the CMD but this one from in-memory cache of data, it represents the (Offline Mode).
dataAd OleDBDataAdapter is a class used to fill the DataSet with Sql Statements after receiving CN connection in order to update the DataSource [Table].
SqlStr, SqlStr1, DBPath, DBStatus, SearchBox Strings are used in different locations in the project to represents strings variable used a lot, i.e:
SqlStr : Stores Connection String and the Sql Statement.
SqlStr1 : Stores the another Sql Statement
DBPath : Stores the MS-Access Data-Base file (*.accdb) path.
DBStatus : Stores the connection status (CN.State) of the database.
SearchBox : Stores the name of the field's record being searched from within the database table.
X, Y, SqlH, Onh Integers variable used in different locations in the project to represents strings variable used a lot, i.e:
X : Represents the (X) location of the animated rect. shape
Y : Represents the (Y) location of the animated rect. shape
SqlH : Represents the number of records affected in the table.
Onh : Represents the number of rowfield when we navigate through the table.
Form_Load() Event Page (4)
Executed when the form is shown on the screen.
- We define the database file (accdb) path in the application parent folder (Application.StartUpPath) = Bin\Debug\PhoneB.accdb.
- Make sure the database is ready (Opened) at start up, if its status is (Open) then (close) it.
- Store the database status in the (DBStatus) string var.
- Store the Connection String into the (SqlStr) string var.
- Open the database
- Store the database status in the (DBStatus) string var.
- Set Onh = Nothing at start up.
- Call the (Size1) Sub which holds values of the location(X,Y) of the animated shape before starts animated.
- Turn on both timer1, timer2, and call the (DataGrid_Work) Sub which fills the datagrid with data retrieved from (Table).
  
  These were a series of events occure (we want them to happen) when the application starts.
  There's a fixed pattern when talking about working with database, you should:
  1) Locate the Data-Base
  2) Close if Open
  3) Open the Data-Base
  Any Data-Base in the world should be handled with this pattern, the rest of logic and codes sippets are up to you 100%
The Save Button: Page (4)
Executed when the [New\Save] Button is clicked.
- We fill the textboxes with friend's Info and click Save Button.
- Make sure the FirstName Text (Tfnm.Text) is not empty at lease, otherwise display a warning message and exit the Sub Save.
- Clear the SqlStr value and refill it with new Sql Statement string ("Insert Into Phone1") the textboxes values.
- Connect the CMD to CN ,Pass the SqlStr value to the CMD to be parsed, the proccess returns the number of records affected, we store the number in SqlH integer var, and display a message, clear all the textboxes fields using (ClearAll) Sub and finally display the new record into the DataGridView using (DataG_Work) Sub.
The Search Button: Page (4)
Executed when the [Search] Button is clicked.
- We show search input box prompt the user to define the first name.
- We store the defined name into SearchBox string var.
- Derive the data equal to (SearchBox) and store it in SqlStr.
- Repeat the CMD cycle except this time the proccess returns data table info not a number, so we use DataR to receive the data returned using (ExecuteReader) then (Read) this data.
- Make sure the returned data are not Null or Empty, if yes then clear the textboxes if previous data was displayed, display a message and close the (DataR) otherwise display it on the form, display a message and close the (DaraR).
- Notes that this is the way it is done using the (Online Mode).
The Edit Button: Page (5)
Executed when the [Edit] Button is clicked.
- What are we editting?, we must make sure that a previous search proccess took place otherwise display a message about it.
- The textboxes are filled with precious data retrieved from search proccess, we edit these data in the textboxes and then click (Edit) Button.
- Repeat the CMD cycle using ("Update table Phone1 Set (Fname='" & Tfnm.Text & "', .............. Where Fname = '" & SearchBox & "'") this will edit the previously search data, display a message and clear the textboxes and finally display effects happened to field in the DataGridView.
The Delete Button: Page (5)
Executed when the [Delete] Button is clicked.
- What are we Deleting?, we must make sure that a previous search proccess took place otherwise display a message about it.
- As the previous edit proccess exactly, we will do the same here.
The Navigation System of the Data-base Table
Move To First Record: Page (6)
Executed when the [First] Label is clicked.
- There is not (DataReader.MoveFirst) here, it is a work around actually, it's a very successful work around.
- Here how is it gets done, we move the table cursor to the first field by openning the table again normally as if in the search proccess except that we are not search for anything this time we just want the cursor to be at the first record as this is the default status of the table's cursor when first opens the table, and display the returned data as done in the search proccess to the textboxes, finally we set (Onh) integer var. to (1) because we're going to use it to move the cursor using (For .... Next) statement.
Move To Next Record: Page (6)
Executed when the [Next] Label is clicked.
- Repeating the proccess of openning the table as in the search proccess, make sure it's not empty.
- Now the trick, where is the table cursor is stopped at so we can move it to the next record?!, if we ran (Ctrl+F5) the application the Form_Load() event sets (Onh) to Zero, so if we clicked (First) it becomes (1), if not then (0) still, ok?
- So, If (Onh) <= 0 Then (Onh) = 0 (If we previously clicked {First} then (Onh) = 1 so we will want to move on to 2, but if (Onh) < 0 means that the table opened and was empty so (Onh) = -1 so we will want to make it (0) because (0) means (First not empty or null record), now we want to the cursor to stop at each record, we will use (For C = 1 To Onh) and if Onh still 0 which caused by clicking (Next) at startUp without (First) then the cursor will stop at the first record and (Next) Label will display the first not Null record, else it will continue counting and stopping, and with each stop it will store the CMD Sql Statement into (DataR) to read it and display it in the TextBoxes and Onh = Onh +1 to start a new For-Next. Clear ?
- Now, when the DataR stops reading cause the cursor stopped at the end then we display message.
Move To Previous Record: Page (7)
Executed when the [Previous] Label is clicked.
- Exactly as the same previous operation {Next} except we will count down Onh value until it reaches the First Record using Onh = Onh - 1.
Move To Last Record: Page (7)
Executed when the [Last] Label is clicked.
- This is little different, it depends on the number of records in the table, so if the table have 6 records then we want to tell the cursor to stop at the 6th one.
- To accompish this, you need to receive the number of records in the table by, we will use (DataR.ExecureScalar) which is a ReadOnly method which will return the number of records available in the table opened using CMD Sql Statement ("Select * From Phone1") and store the number in (N) Integer var.
- Usinf For .... Next Statement (For C = 1 To N) Read the returned record which represents the last one at (N) number, you can say (For C = 0 To N-1) and is the same.
- At the end of {Last} proccess, what if we wanted to navigate {Previous} or {First} ?
- YES, we will set the (Onh) value equal to (N), i.e we have (4) records in the table, the last record is Number (4) so normally N=3 (0, 1, 2, 3), when we set (Onh = N) and move to {Previous} Onh = Onh - 1 so the cursor will stop at (2) which means the 3rd record which represents the previous to records number (3). Clear ?.
Sub ClearAll(): Not Listed In The Book
Executed when called
- To clear the contents of a form's controls professionally, we do this (ForEach Control In the controls conainer (In this case, the form) If TypeOf this Control (Ctrl1) is (TextBox) Then set its (Text) Value to {Null}, easy huh?!
- Note that if the Controls (TextBoxes) are in a container like (FlowLayOut Panel or any panel) we say (ForEach Ctrl1 in Me.(PanelName).Controls to the end of the Sub as shown in the Project Source Code.
Sub DataGrid_Work(): Not Listed In The Book
Executed when called
- To fill a DataGridView control with a table contents using either (Offline Mode or Online Mode) you need to connect it to the DataSource {Table}, and the {DataSource} must be of the correct type {DataTable}, so basicly it can not be done using the connected (Online) mode, because not part of its cycle is using DataTable, but a work around is available.
- The work around to retrieve table contents into a DataGridView control is like this :
  Define a DataTable {MyTable}
  Use the (DataR) with the CMD method
  Load the (DataR) into the (DataTable) coz they are both the same but in a different level but still the same structure, after all the (DataReader) holds data from a table but only one-way (ReadOnly).
  Then bind the DataGridView to the (DataTable) that holds your (DataReader) data returned from Sql Statement parsed by CMD which connected with CN.
- Don't forget to close the (DataR) after every READ in order not to get (There is already an opened DataReader Associated with .... ).
Timer1 & Timer2: Not Listed In The Book
Executed when called
- Thier job is to increase the size of the Rect. Shape1 while displaying {Label} showing the database status.
- We created a Sub Size1() to determine\Set the start point of the shape's both (X,Y) .
- In the Timer1 event we call it to start animating from there each time.
- So if the Shape's width exceeded a certain width value (140px) stop this Timer1 from increasing the shape's size, otherwize keep increasing using (X = X+10), and with each Tick set Shape's width equal to (X), and same method goes for Timer2, except that Timer2 represents the (Y) value of Shape's height and if (Y) exceeded a certain value (40) we stop this Timer2 too.
- While the Shape is being resized, we display the [Label] that holds the status of the database.
ContextMenuStrip: Not Listed In The Book
Executed when Form is Right-Clicked
- We designed a one button ContextMenuStrip with (ClearAll Button) to clear the textboxes contents using ClearAll() Sub.
- We trigger the ContextMenuStrip's button on Form_MouseDown() event as shown in the project Source Code.

Please, keep up with us.
In the next chapter3 we will update the MyPhoneBook Project and add PictureBox to insert\retrieve photos to/from the database table, and we will create (Requirements Doc, Use Case Definition and Event Planning Doc).

Links

The Chapter 1	The Chapter 2

The End