Binding a fieldname to a control programmatically in Visual J#

Before the Database Connection and Binding process I want you to make an Ms-Access database file named “dbEmployee” containing a table named “tblEmployee”. Use the following specifications:
Field Name Data Type Description
chrempno text Handles employee id
chrfname text Handles employee’s name
chrlname text Holds employee’s last name

After designing the structure of your table, you can enter appropriate values for each field. For instance:
chrempno chrfname Chrlname
2010A John Doe

Now that we are done creating a table and adding appropriate values to it, we can now link to that table and bind the fields to our windows application control by following these steps:

1. Start>All programs>Visual J# 2005 Express Edition.

2. Click File>New>Project>Select Windows Application from the windows studio installed templates then click Ok.

3. A new form will appear. Before adding appropriate controls to our form let us first establish a connection to our dbEmployee database file. To do this we will use a database access tool called OleDbDataAdapter. OleDbDataAdapter will enable us to set the filename of a database file and the name of the table that we wanted to be made available in our project. It also permits us to set-up how the record values will be displayed on our form by using an appropriate SQL statement.

4. By default, OleDbDataAdapter is not shown on the Visual J# 2005 Express control toolbox, to add it, click Tools>Choose control toolbox>Type “ole” (no quotes ) on the filter textbox then check all the items that starts with “ole” and finally click the Ok button. OleDbDataAdapter should now appear on your toolbox.

5. Expand the All Windows Forms toolbox category then double-click OleDbDataAdapter.

6. A Data Adapter configuration wizard will then appear. Click the new connection button>Select Microsoft Access Database File from the data source list.

7. Click the browse button then locate your dbEmployee.mdb file then click the Ok button.

8. Click Next>A message box containing the following prompt will appear:
“The connection you selected uses a local data file that is not in the current project would you like to your project for the connection? If you copy the data file to your project, it will be copied to the project’s output directory”> just click the Ok button then click next.

9. A Generate SQL statement, type “SELECT * FROM tblEmployee” (no quotes) this will export all the record value of our table to our project then. Click Next after typing the SQL statement and finally click the Finish Button.

10. After specifying the table name, right-click OleDbDataAdapter1 from the bottom portion of Visual J# IDE then click Generate Data Set then click the Ok button. If you are wondering what a dataset is, a Dataset is an imaginary box the holds the field names of your table. It is use a temporary storage box for table data.

11. At this point, you will need to design the interface of your application. Double-click the label tool. In the properties window, change its text to “Employee number:” (no quotes).

12. Double-click the label tool and put it in the right-side of your Employee number label. Change its name to Empnumberlabel.

13. Add another label below the Employee number label and change its text to “Firstname:” (no quotes). Put a label on its right side and name it Fnamelabel.

14. Add another label below the Firstname label and change its text to “Lastname:” (no quotes). Put another label on its right side and name it Lnamelabel.

15. Double-click your form, type the following code:
//fills our dataset with record values
this.oleDbDataAdapter1.Fill(this.dataSet11);
//Use the dataset methods collection to retrieve the record values
//The get_Item() method after the get_Rows() method retrieves the table row value
//while the last get_Item retrieves the table column value
Empnumberlabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(0)));
//The index number of the first table row is 0, the second row is 1 and so on.
//Column index number starts from 0, the second table column is 1 and so on.
Fnamelabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(1)));
//Dataset collection methods returns an object data that is why we use the 
//String.valueOf data conversion function to convert it to string.
Lnamelabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(2)));
16. Your code should now look like this:
private void Form1_Load(Object sender, System.EventArgs e)
{
this.oleDbDataAdapter1.Fill(this.dataSet11);
Empnumberlabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(0)));
Fnamelabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(1)));
Lnamelabel.set_Text(String.valueOf(dataSet11.get_Tables().get_Item("tblEmployee").get_Rows().get_Item(0).get_Item(2)));
}
17. Press F5 to test your application.You should now see the following output:


Search a Record Value in Microsoft Visual Basic 2008 Express Edition

As you may probably know, standard navigational buttons are only effective if you are managing few record values but when it comes to billions of records a search module is considerably significant. Search module allows you to quickly test whether a specific record value exists in a multi-records database.
I’d been using Visual Basic 2008 Express Edition for a while now but I can hardly find a method appropriate in searching record. No wonder why most questions that relate to searching a record values in nearly all web forums are unanswered. Perhaps there are other methods that I may not know but here’s an approach that I discovered through inquiries and experimentation.

To give you an idea of how to do it, let us first examine the common algorithm shared by most search modules.

1. First, a search text is entered in a text box as an input.

2. That text is then compared to the first record or beginning of file(BOF). If the first record is equal to the search text, a corresponding record is then displayed.

3. If the search text is not equal to the first record, the record pointer is moved to the next record and a search text is then compared to that record.

4. The record pointer is continuously incremented until a match is found and the end of file(EOF) has not yet been reached.


At this point we will now apply these pseudo codes into our search module. Though that may seem simple, it is more complicated to use BOF and EOF functions in Visual Basic 2008 than in lower versions of Visual Basic. I barely know how to use them on text files, which leave us no choice but to do everything the lengthy yet easiest way. The following steps demonstrate how:


1. Design an Ms-Access 2003 database file named “dbnames” using the following field structure:

Field nameData TypeDescription
chrfname text Handles the firstname of whoever
chrlname Text Handles the lastname


2. Add the following values.

chrfname chrlname
John Doe
Jean Doe
Jane Doe

3. Save your table as “tblNames”.

4. After designing your table, close Ms-Access then click Start>Select All Programs then Click Visual Basic 2008 Express Edition. The Visual Basic 2008 Express Edition IDE should then appear.

5. Click the File menu then Select New Project.

6. The New Project dialog box should then come into view >Double-click Windows Forms Application from the available templates.

7. A new form will appear. Before adding appropriate controls to our form let us first establish a connection to our dbnames database file. To do this we will use a database access tool called OleDbDataAdapter. OleDbDataAdapter will enable us to set the filename of a database file and the name of the table that we wanted to be made available in our project. It also permits us to set-up how the record values will be displayed on our form by using an appropriate SQL statement.

8. By default, OleDbDataAdapter is not shown on the VB 2008 control toolbox, to add it, click Tools>Choose control toolbox>Type “ole” (no quotes ) on the filter textbox then check all the items that starts with “ole” and finally click the Ok button. OleDbDataAdapter should now appear on your toolbox.

9. Expand the All Windows Forms toolbox category then double-click OleDbDataAdapter.

10. A Data Adapter configuration wizard will then appear. Click the new connection button>Select Microsoft Access Database File from the data source list.

11. Click the browse button then locate your dbicons.mdb file then click the Ok button.

12. Click Next>A message box containing the following prompt will appear:
“The connection you selected uses a local data file that is not in the current project would you like to your project for the connection? If you copy the data file to your project, it will be copied to the project’s output directory”> just click the Ok button then click next.

13. A Generate SQL statement, type “SELECT * FROM tblNames” (no quotes) this will export all the record value of our table to our project then. Click Next after typing the SQL statement and finally click the Finish Button.

14. After specifying the table name, right-click OleDbDataAdapter1 from the bottom portion of VB 2008 IDE then click Generate Data Set then click the Ok button. If you are wondering what a dataset is, a Dataset is an imaginary box the holds the field names of your table. It is use a temporary storage box for table data.

15. At this point we will now design the interface of our application. Arrange your controls as follows:



Note: Those texts that appear beside each control are the suggested names for our form controls in this module. To change the value of the name property of each control, just click each individual control then locate the name property in the properties window then assign those descriptive names in our illustration correspondingly.

16. Click the label named Namelabel. In the properties window, locate data bindings>text>form1 instance>Dataset11>tblnames>chrfname. This will bind your field value to your Namelabel control.

17. Click the label named Lnamelabel. In the properties window, locate data bindings>text>form1 instance>Dataset11>tblnames>chrlname. This will bind your field value to your Lnamelabel control.

18. Double-click your form, type the following code:
'Populates your dataset with record values
OleDbDataAdapter1.Fill(DataSet11)

19. Double-click your control named SearchButton then type the following:

'handles the row index number of our table
Dim introws as integer
'determines whether the record is found or not
Dim blnfound as Boolean
'at the start let us assume that a match has no yet been found
'this is called pessimistic programming or something
blnfound=false
'Holds the search text from the textbox 
Dim strtext as String
'Holds the record value from the chrfname field 
Dim strname as String
'Holds the total number of records
Dim inttotrec as Integer
'this is our EOF
inttotrec=Dataset11.Tables("tblNames").Rows.Count
'Moves the record pointer to the first record
'which has a row index number of zero
introws = 0
'Converts the value of the first record of our chrfname field to capital
'letter and assign it to a variable named strname
strname = UCase(DataSet11.Tables("tblNames").Rows(introws).Item("chrfname"))
'Converts the text entered in our search textbox to Uppercase
'The purpose of converting both record values and search text to upper case
'is to compare both values in uppercase form regardless of whatever
'case they were typed initially
strtext = UCase(SearchTextBox.Text)
'If the searchtext is equal to our record then
If (strtext = strname) Then
'assign true to our blnfound variable
'will be used later whether to display or not
'to display our message box
blnfound = True
'display the record values on their corresponding controls
'to understand how to view records on your form
'visit www.homeandlearn.com
'this site helped me a lot when I was just starting .Net programming
'thanks www.homeandlearn.com
NameLabel.Text=DataSet11.Tables("tblNames").Rows(introws).Item("chrfname")
LnameLabel.Text = DataSet11.Tables("tblEmployee").Rows(introws).Item("chrlname")
End If
'if not equal to the first record then
While (strtext <> strname) And (introws < inttotrec - 1)
'increment the record pointer 
introws = introws + 1
'assign the value of the next record pointer to strname
strname = UCase(DataSet11.Tables("tblNames").Rows(introws).Item("chrfname"))
'tests if the next record value is equal to the search text
'if yes then
If (strtext = strname) Then
'assign true to our blnfound variable
blnfound = True
'display the record values on their corresponding controls
NameLabel.Text=DataSet11.Tables("tblNames").Rows(introws).Item("chrfname")
LnameLabel.Text = DataSet11.Tables("tblNames").Rows(introws).Item("chrlname")
End If
'Continue incrementing the record pointer until a match is found
'and the end of file has not been reached
End While
'if the record is not found,  display Record not found in our  messagebox
If blnfound = false Then
MsgBox("Record not found", MsgBoxStyle.Information, "Search a Record")
End If

20. Press F5 to test your application.

21. Try searching names that exists in a database. You can also try searching records that doesn't exist. Notice how our search module works splendidly and effectively. Feels like birthday, right?

22. For future improvements of this module, I suggest adding search options such as search by name or by last name. And I leave that to you to figure out wahaha!