Showing posts with label VB2008Express. Show all posts
Showing posts with label VB2008Express. Show all posts

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!

Storing and retrieving an image from a database file
(Visual Basic 2008 Express Edition)

One of the frequent problems encountered by most newbie database developers and designers is the difficulty in storing or retrieving an image data from a database file. Though there are a number of ways to store an image, the most comfortable way (for me) is to create a fieldname and store the URL (Uniform Resource Locator) or location and filename of an image as its value. One of the advantages of this approach is it allows you to access an image from a database file painlessly and swiftly compare to using ole objects. Prior to the image retrieval process, I want you to make a database file named “dbicon” containing a table named “tblicons” . This table will store the name and icon of a particular application. Use the following specifications:

Field Name
Data Type
Description
chrdesc
Text
This will store the name or descriptionof an application
chrimage
Text
This will store the icon of an application.I suggest that you use the maximum value for this field which is 255 because most image URLs contain multiple characters. Alternatively, you can use the memo data type.

To retrieve the image from the database file and display it into a picture box on your client application, use the ImageLocation property of the picture box then assign the value of your image fieldname as the ImageLocation’s value.

After designing the structure of your table, you can enter appropriate values for each field. For instance:

Chrdesc
chrimage
Apache Web Server
C:\Program Files\Apache Group\Apache\manual\images\favicon.ico
PHP
C:\Program Files\PHP\php.gif


Now that you are done designing your table, it’s time to retrieve the image from your database file and display it in a picture box on your client application. To achieve this, the idea is to make use of the ImageLocation property of the Picturebox and assign the value of your image fieldname to it. The following steps demonstrate how:

1. Click Start>Select All Programs then Click Visual Basic 2008 Express Edition. The Visual Basic 2008 Express Edition IDE should then appear.

2. Click the File menu then Select New Project.

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

4. A new form will appear. Before adding appropriate controls to our form let us first establish a connection to our dbicons 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.

5. 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.

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

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

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

9. 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.

10. A Generate SQL statement, type “SELECT * FROM tblicons” (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.

11. 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.

12. 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 “Name:” (no quotes).

13. Double-click the textbox tool and put it in the right-side of your label. Change its name to Nametextbox.

14. Add another label and change its text property to “Icon” (no quotes).

15. Add a PictureBox and Position it below the Icon label. Change its name to IconPictureBox .

16. Now, double-click your form, the load procedure event should then come into view:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    
End Sub
End Class

17. Type the following codes between the Private Sub and End Sub:

'This will fill our dataset with record 
'values specified in our OleDbDataAdapter1
OleDbDataAdapter1.Fill(DataSet11)
'Displays the first record of the 
'first fieldname from our tblicons     table
NameTextBox.Text =DataSet11.Tables("tblicons").Rows(0).Item("chrname")
'Extract the URL of our image stored 
'on the first record value of our   chrimage field and
'store it as a value of our picturebox ImageLocation property
PictureBox1.ImageLocation =DataSet11.Tables("tblicons").Rows(0).Item("chrimage")
18. Your code will now look like this:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'This will fill our dataset with 
'record values specified in our OleDbDataAdapter1
   OleDbDataAdapter1.Fill(DataSet11)
'Displays the first record of the 
'first fieldname from our tblicons table
NameTextBox.Text = DataSet11.Tables("tblicons").Rows(0).Item("chrname")
'Extract the URL of our image stored 
'on the first record value of our   chrimage field and
'store it as a value of our 
'picturebox ImageLocation property
   PictureBox1.ImageLocation = DataSet11.Tables("tblicons").Rows(0).Item("chrimage")

    End Sub
End Class

18. Press F5 to see the culmination of your effort.

19. You should now see “Apache” beside the Name label and a quill icon beside the Icon label.

Connecting to an Ms-Access 2003 database file using Visual Basic 2008 Express Edition

The simplest way to retrieve a data from an Ms-Access 2003 database file in VB 2008 Express Edition is to use Database Explorer. Database Explorer is a database management tool that enables user to access server-based or local-based databases. There are other handy database access tools such as OleDbDataAdapter or SqlDataAdapter but I prefer using Database Explorer because it is simpler to use and it allows you to link to a database file conveniently and effortlessly.

To connect to an Ms-Access database file, you should first specify the location and file name of the database file that you wanted to link to. The following steps show you how:

1. Create an Ms-Access 2003 database file.(If you are familiar with database programming, this one is just common sense to you).

2. Start Visual Basic 2008 Express Edition.

3. Click File>New Projects>Select Windows Forms Application from the Visual Studio installed templates option>Click the Ok button.
This will display a blank form on the screen.

4. Next, click the View menu>Select Database Explorer. This will display the Database Explorer panel on the left portion of your VB 2008 Express Edition Integrated Development Environment. Alternatively, you can press CTRL+ALT+S.

5.In the Database explorer panel, locate the Add Connections option>Right-click it, then select Add connection…

6. The Add Connection Dialog box will appear. Click the Change… button beside the data source label>Select Microsoft Access Database File form the data source list box>Click Ok.

7. Click the Browse… button beside the Database file name label then locate and double-click your (Ms-Access database file).mdb file>Click Ok.

Click the Test Connection button to test if the database connection is working properly. Just make sure that you database file is not password protected or else you will get a “Not a valid Password” error.

After choosing the file name of the Ms-Access file that you wanted to connect to, you will need to select the data source or the data that you wanted to work with your application. Data sources can be made from a database application such as Ms-Access, or from a web server.

To define the data source, follow these steps:

1.Click the Data menu>Click the Add new data source… option.

This will bring out the Data Source Configuration Wizard. Since we will be getting our data from a database application and not from a web-server and definitely not from any bound object embedded on a form,select database>Click the Next button.

2.In the next screen, you will see your database file name in the Data Connection list box>Just click the next button.

3. After clicking the next button, 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 copy the file to you project and modify the connection?
If you copy the file to your project, blah blah blah”. Just click the Yes button.

4.The connection string dialog box will appear. A connection string is simply a string containing information about the connection.Just click the Next button.

5.The Database object screen will be shown containing a tree control with Tables and Views parent items. Tables contains your actual table while Views stores virtual tables,To manipulate your actual table, just expand the Tables parent item, and check the table that you wanted to be available in you project. Alternatively, you can check the Tables parent item if you wanted all you tables to be made available on your project.

6.Finally, click Finish.

After designating the table where you wanted to get your records from, the next thing that you need to do is to show the record values on your form. These steps teach you how:

1.Click the Data menu> Select Show Data Sources. This will show the available data sources or tables in a tree control. Alternatively, you can also press Shift+Alt+D.

2. In the Data Sources Panel, expand the table that you wanted to view the data in a form>Click and drag the individual fieldnames from the data sources panel to your form.

3. A bound control will be displayed for each field name. Together with a binding navigator that contains next, top, bottom, previous, add, delete, and save buttons.

4. Press F5 to run your application.

Try to click any of the navigational buttons and see how they worked perfectly like a charm. If you missed any of the steps during the connection process then your application will definitely fail. But don't despair, as Henry Ford once said, "Failure is simply the opportunity to begin again, this time more intelligently".