• (2 Pages)
  • +
  • 1
  • 2

A Really Simple Database Create a Database using Access & VB.net Express 2008 Rate Topic: ----- 1 Votes

#1 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Post icon  Posted 29 November 2008 - 11:00 AM

*
POPULAR

Hi All

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.

Attached Image

Expand the database and then expand the tables folder you should now see the Clients table.

Attached Image

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


Attached Image

Note: You should get a message at this point asking if you would like to copy a file to your project click Yes.

Attached Image

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.

Attached Image

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.

Private Sub LoadPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadPicture.Click
	   
 Dim OpenFileDialog1 As New OpenFileDialog


		 With OpenFileDialog1
			.CheckFileExists = True
			.ShowReadOnly = False
			.Filter = "All Files|*.*|Bitmap Files (*)|*.bmp;*.gif;*.jpg"
			.FilterIndex = 2
			If .ShowDialog = DialogResult.OK Then
				' Load the specified file into a PictureBox control.
				PicturePictureBox.Image = Image.FromFile(.FileName)
				
			End If
		End With
	End Sub


This code is used for the open file dialog which we use to select the picture we want to show

Attached Image

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.

Attached Image

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.

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.

Attached Image


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.


Attached Image

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 :-

Me.ClientsTableAdapter.Fill(Me.DataSet2.clients, LastNameToolStripTextBox.Text)

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.

Is This A Good Question/Topic? 5
  • +

Replies To: A Really Simple Database

#2 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2268
  • View blog
  • Posts: 9,482
  • Joined: 29-May 08

Posted 29 November 2008 - 03:09 PM

That's a great first tutorial. :^:

This post has been edited by AdamSpeight2008: 29 November 2008 - 03:11 PM

Was This Post Helpful? 0
  • +
  • -

#3 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Posted 30 November 2008 - 06:10 AM

View PostAdamSpeight2008, on 29 Nov, 2008 - 10:09 PM, said:

That's a great first tutorial. :^:


Thanks for that AdamSpeight2008 its always nice to know :D
Was This Post Helpful? 0
  • +
  • -

#4 abayomi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 19-May 08

Posted 03 December 2008 - 04:03 AM

This tutorial is really very good and it has helped me a lot. Many thanks
Was This Post Helpful? 0
  • +
  • -

#5 dwains  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 27-December 08

Posted 27 December 2008 - 10:22 AM

In is tutorial using VB.Net 2008 was very informitive however in 2008 you dont get the picturebox as shown I cant seem to figure out how to diplay the image in the datagridview. Any help would be greatly appreciated. Thanks Dwain Snickles
The following code dosn't work:
Sub LoadPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadPicture.Click

Dim OpenFileDialog1 As New OpenFileDialog


With OpenFileDialog1
.CheckFileExists = True
.ShowReadOnly = False
.Filter = "All Files|*.*|Bitmap Files (*)|*.bmp;*.gif;*.jpg"
.FilterIndex = 2
If .ShowDialog = DialogResult.OK Then
' Load the specified file into a PictureBox control.
This is where I cant seem to figure out using vb.net 2008?
PicturePictureBox.Image = Image.FromFile(.FileName)

End If
End With
End Sub
Was This Post Helpful? 0
  • +
  • -

#6 steveshergill  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 29-December 08

Posted 05 January 2009 - 07:01 AM

i am getting an error. When I try to save a record i get the following message.
"UPDATE REQUIRES A VALID DELETECOMMAND WHEN PASSED DATAROW COLLECTION WITH DELETED ROWS.
IT POINTS TO THIS LINE OF CODE.
CODE:
ME.TABLEADAPTERMANAGER.UPDATEALL(ME.PRINTERS4UDATASET)
Was This Post Helpful? 0
  • +
  • -

#7 marksteele  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 13-January 09

Posted 13 January 2009 - 07:14 AM

the code works fine for me when I'm trying to look through my database but the add button will not work. It works until i close VB but it will not actually save the new information to the database
Was This Post Helpful? 0
  • +
  • -

#8 shankarbtn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 28-January 09

Posted 30 January 2009 - 06:31 PM

The Code Helped Me To Work With Ms Access........
But In That Explaination 6th & 7th Points Are Unable To Understand...
Was This Post Helpful? 0
  • +
  • -

#9 cjscs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 14-August 09

Posted 05 September 2009 - 11:47 AM

Great Tutorial! Very informative. I need to look close to see if it answers my problem.

Thanks
Was This Post Helpful? 0
  • +
  • -

#10 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Posted 12 November 2009 - 01:35 PM

Note: You should get a message at this point asking if you would like to copy a file to your project click Yes.

Attached Image

Hi all just to say at this point in the tutorial if you click yes a reference of the database will be added to your project so the project will only access this and not your original database. If you click no then the project will use the database you originally created.

This has recently come up i a few posts.
Was This Post Helpful? 0
  • +
  • -

#11 anarcy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 22-November 09

Posted 22 November 2009 - 08:28 PM

hey great tutorial one problem how do i open or add the option to open a different database? and also when i add a new feild it counts backwards from 10 like i click add and the client id starts at -1 and it goes all weird
email me at fps_anarcy@hotmail.com

p.s disregard last post problem fixed help on this one plz ^
|

This post has been edited by anarcy: 22 November 2009 - 08:31 PM

Was This Post Helpful? 0
  • +
  • -

#12 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Posted 03 December 2009 - 11:16 AM

Hi Arncy

That is normal when you add a new row. It will show -1 till you save to the database then it will be given a unique number. If that row is deleted the id number it was assigned will never be used again as to avoid confusion. So if you have

1
2
3
4

and delete 3

it will always show

1
2
4

Hope this helps
Was This Post Helpful? 0
  • +
  • -

#13 Guest_Heeren*


Reputation:

Posted 06 June 2010 - 11:56 PM

I've added Service-Based Database (MSSQL) to my VB.net application. and connected very well to it. But, How to deploy that database in a setup project?? OR can client use that database file "MDF" in different PCs having same software application? like the main server is also having same software, and after making all those data entries in desktop server pc .. and then copy that data file to client's laptop which is having same application with server? but, everytime I install software.. it shows an error.. of naming instance or something.. and database is already exist.. sumthin like that. and when I copy that data file to another PC.. that shows no data in it.
Was This Post Helpful? 0

#14 Guest_Guest*


Reputation:

Posted 10 June 2010 - 06:47 AM

I need some help. I have got the database to run properly however when I publish it it keeps on referring to an Apps file that it could not find.

Any one out there who could help and give assistance?
Was This Post Helpful? 0

#15 Guest_Guest*


Reputation:

Posted 22 December 2010 - 01:48 PM

View Postsharpy, on 29 November 2008 - 10:00 AM, said:

Hi All

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.

Attachment attachment

Expand the database and then expand the tables folder you should now see the Clients table.

Attachment attachment

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


Attachment attachment

Note: You should get a message at this point asking if you would like to copy a file to your project click Yes.

Attachment attachment

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.

Attachment attachment

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.

Private Sub LoadPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadPicture.Click
	   
 Dim OpenFileDialog1 As New OpenFileDialog


		 With OpenFileDialog1
			.CheckFileExists = True
			.ShowReadOnly = False
			.Filter = "All Files|*.*|Bitmap Files (*)|*.bmp;*.gif;*.jpg"
			.FilterIndex = 2
			If .ShowDialog = DialogResult.OK Then
				' Load the specified file into a PictureBox control.
				PicturePictureBox.Image = Image.FromFile(.FileName)
				
			End If
		End With
	End Sub


This code is used for the open file dialog which we use to select the picture we want to show

Attachment attachment

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.

Attachment attachment

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.

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.

Attachment attachment


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.


Attachment attachment

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 :-

Me.ClientsTableAdapter.Fill(Me.DataSet2.clients, LastNameToolStripTextBox.Text)

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.

Was This Post Helpful? 0

  • (2 Pages)
  • +
  • 1
  • 2