Create a Database using Access & VB.net Express 2008
In this tutorial I will explain how to make a really simple database using a Microsoft Access database in conjunction with VB.net Express 2008. The way the application is constructed requires minimal effort as we will utilize the capabilities of the VB.net Express IDE. This tutorial is aimed at people who are new to programming; I have assumed you have got a basic Knowledge of Microsoft Access, here goes.
1. Create a folder on you C drive or where ever you like just remember the path, for this example I used C:\Database. Add three photographs to this file in a JPG format.
2. Open access and create a blank database called DB1 and save it in your database file. Create a table in design view and add 4 fields
[ClientID] Data type = Auto Number. Set this as the primary Key
[FirstName] Data type = Text
[LastName] Data type = Text
[Picture] Data type = OLE Object
Save the table with the name Clients. Open the table and add this data into the first and last name fields don’t worry about the picture field.
Peter Jones
David Smith
Jane Doe
Save the database in the database file & close Access.
3. Open the VB express IDE and select File and New project. Select Windows forms Application and name it MyaccessDB press OK. You should now have form1 in your IDE
4. The next step is to add our data source to our project. On the left-hand side of the IDE select the Database explorer window, right click on the Data Connections and select Add connection. In the Add connection window select Microsoft Access Database File (OLE DB), and then browse to find our access database and select. Ignore the password and test the connection which should work. Press OK you should now have our DB1.mdb Database in our Data connections window.
Expand the database and then expand the tables folder you should now see the Clients table.
5. We next have to add a dataset to the project in the solutions explorer. Right click on myaccessDB and select Add then New Item. Select Dataset and then Add. You should now have a dataset in the solutions explorer and the Dataset1 tab will be open.
6. We need to add the Clients table to the Dataset, we do this by selecting the Clients table in the data connections window and drag the table onto the Dataset1 tabbed page.
You should now have a Clients Table Adaptor in the Dataset with an SQL fill query at the bottom of the table which has been automatically generated. Save the Dataset
Note: You should get a message at this point asking if you would like to copy a file to your project click Yes.
7. We now need to add the Dataset1 to the form. In the main area of the IDE select Form1. In the solutions explorer open the Data sources window. Expand the Dataset1 to show the clients table, highlight the clients table and from the drop down menu select details. Expand the clients table and highlight the Picture field and from the dropdown menu select Picture box. Now highlight the clients table and drag onto the form1. You will see appear automatically textboxes; labels a picture box and a binding navigator on the form. You now have a working database. Run the application and page through the records you have entered. You can now also Add, Delete and Save records
Although at this point we cannot show a picture which is next.
8. from the tool box select a button and drag it onto the form, call it Load Picture, double click the button to open the code editor and paste this code into the buttons sub.
01 | Private Sub LoadPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadPicture.Click |
02 | |
03 | Dim OpenFileDialog1 As New OpenFileDialog |
04 |
05 |
06 | With OpenFileDialog1 |
07 | .CheckFileExists = True |
08 | .ShowReadOnly = False |
09 | .Filter = "All Files|*.*|Bitmap Files (*)|*.bmp;*.gif;*.jpg" |
10 | .FilterIndex = 2 |
11 | If .ShowDialog = DialogResult.OK Then |
12 | ' Load the specified file into a PictureBox control. |
13 | PicturePictureBox.Image = Image.FromFile(.FileName) |
14 | |
15 | End If |
16 | End With |
17 | End Sub |
This code is used for the open file dialog which we use to select the picture we want to show
Run the application and press the load picture Button. Navigate to the database file and select a picture. Close the dialog box and press the save button on the Navigator strip, the picture will now be stored in the access database. Repeat this for all the records pictures.
Note: You may need to expand the picture box and play around with the settings to make the pictures look right.
And there we have it a fully functional database, if you open up the code editor you will see how little code is required to achieve what normally takes hundreds of lines of code which I think is pretty cool and its quick, although there is a lot more code required to make sure the data entered is correct and indeed all the required fields are filled in. The one thing that is missing is the ability to search the database using wild cards which I am now going to cover.
9. Drag another button onto form1 and name it Search. Double click the button to enter into the code editor page and add this line of code.
1 | Form2.Show() |
10. In the solution explorer add another form (Form2) and then add another dataset (Dataset2). Keep the dataset2 tap open and drap the client table from the DB1.mdb in the Database Explorer on to the dataset2. Again you will see the client table adaptor with the automatically create SQL fill query at the bottom of the table. We will have to edit this query to enable the wild card search facility.
11. Right click on the table adaptor and select Configure, the table adaptor configuration wizard will open. You will see the actual SQL Select query. Click Query builder. In the query builder page scroll down to the lastName record and then scroll across to the input box that says filter, Place a ? in this filter box then press Ok to close the query builder.
Keep pressing next then Finish to exit the configuration wizard. The SQL query should now look like this
Fill,Getdata(LastName)
Note: If you require further filters just add more ? marks in the other fields Filter box.
Save the Dataset2
12. Open the form2 tab and click into the datasource window to show the datasets 1 & 2. Again expand the Dataset2 and Highlight the Clients table, in the drop down menu select Details. Again expand the clients table and highlight the picture field and from the drop down menu select picturebox. Then just highlight clients table and drag onto form2. You should see again textboxes, labels a picture box and a binding navigator appear on the form with the addition of a fill tool strip with a LastName textbox and fill button.
Run the application press the Search and type a second name into the fill tool strip text box and press fill, you should have 1 record shown.
And there you have it a fully functional database with possibly the smallest amount of code used which is the main aim of a good programmer. Although it is neccasary to add more code to to do conditional checks etc. You can also add in combo boxes instead of the Lastname text box you just need to alter the client Table adaptor to suit see below :-
1 | Me.ClientsTableAdapter.Fill(Me.DataSet2.clients, LastNameToolStripTextBox.Text) |
2 |
3 | Me.ClientsTableAdapter.Fill(Me.DataSet2.clients, combobox1.Text) |
Just as a footnote I have used an access database however you can add an MS SQL database to your project by right clicking the project in the solutions explorer and add new item add Service based database. The database explorer will be populated with the database you have created where you can add your table and fill it with data. but I will leave you to figure this one out.
0 Comments:
Posting Komentar