Breadcrumbs

[smashapps]

Simple Database Tutorial

This tutorial will show you how to create a database in Microsoft Access 2010, Create a new DataSource and connect it to the database, Add a DataGridView to display everything in the DataBase and as a bonus I’ll show you a few things with the BindingSource and BindingNavigator controls, and then display the information in your own text fields and navigate through the records in your database.

Difficulty: 2/5

If I wasn't clear about anything let me know.

Requirements:

Microsoft Visual Basic Express 2010/Microsoft Visual Studio 2010
Microsoft Access 2010 (previous versions will work though)

No credits needed to anyone, I've written the tutorial myself.

Step 1: Creating the DataBase

Open up Microsoft Access. You should be looking at the Available Templates when you open up the application. Select Black DataBase, on the right side of the screen set the File Name to whatever you want such as MainDatabase. It can be anything you want. I am naming mine MainDatabase.
Click Create.

By default you are given a table called Table1. Table1 only has the field ID. Leave this field alone it has the primary key which we need to be able to use the database and other relational stuff etc. just leave it. It is also an Auto field so you don’t touch it when adding data to the DataBase, it is incremented automatically. Which we will need to change in Visual Basic as this is not set.

Looking at the Table in Access you see ID, this is one of the columns, to the right of that you are shown “Click to add”. Click on it and select Text and then type in the name of the fields.
I am going to add:

First Name
Last Name
Phone
Mobile
Email

Each one of those fields above should have their own column as shown in the picture below. Also note that when you add the Phone and Mobile fields you should select Number, not text as this is a field for digits not text.
For the tutorial just add some just to the fields below so when we use our database we actually have data to display like shown below:



Now we have created our DataBase named MainDataBase I am saving my Table1 and renaming it to MainTable. You need to right-click on the table’s tab and close it to be able to rename it or rename it when you save it, it will show a dialogue.

Step 2: Creating a new DataSource and our Project

We are finished with Access now. You can close it. Next off open up Visual Basic and create a new project. I named mine Simple Database Tutorial. On the left side of the screen you have your ToolBox, right next to it on the left is a tab called Data Sources. Select it with the left mouse button.

Select: “Add New Data Source…”. The Data Source Configuration Wizard is displayed.

We want our application to get the data from a Database so select it and click Next. The database model is a Dataset so select that and then click next. Next up is our Data Connection. Click the button that says “New Connection…” We need to change the DataSource so click “Change…” and from the list that pops up select Microsoft Access Database File and leave the Data Provider as default. Click Ok. Next up we’re adding our connection. Where it says Connection Properties we have a field for “Database: “ here we click “Browse…” so click that and browse to where you selected your database and then click Ok.

In Access if you added a Username and password this is where we would of added the username and password, but we left that default and so we don’t need to connect with authentication, just leave as default. Select ok and then next, a popup comes up click yes and then next and then we need to choose our Database objects that we’re getting our data from so click the option that has a checkbox and says “Tables”. We only have one table so it doesn’t matter if all tables are selected. Then click finish.

Step 3: Creating the DataGrid and displaying the Databases data.

In the ToolBox Double click the control named DataGridView. Select the > icon on the top right of the control that is added to your form and then go to “Choose Data Source: Other Data Sources… then Project Data Sources… then MainDatabaseDataSet then finally the MainTable.

After you select the DataSource for the DataGrid Columns are added to the control which represent the columns from the Database we created earlier.

For the data to be added to this control we need to fill it using the TableAdapter (created automatically). We don’t need to do anything with this though because it is added to the Form’s load event automatically although there may be times you need to use it. That is how. If you used the same names as I did you would use:

Code: Select all

Me.MaintableTableAdapter.Fill(Me.MainDatabaseDataSet.Maintable)

If you run the program you will now see a DataGridView with the data we added in our Database. This step is now finished and I’ve finished explaining how to display data from a Database in a DataGridView. Now onto the Binding Source and Binding Navigator. You can delete the code the control added and remove the control if you like or just make room.
You’re going to need 6 labels and 6 Textboxes now. Add the 6 of each and label the labels as so:

lblID
lblFirstName
lblLastName
lblPhone
lblMobile
lblEmail

It should be obvious what to set their text property to.
Change the names of the text fields to this:

txtID
txtFirstName
txtLastName
txtPhone
txtMobile
txtEmail

First off add the BindingSource and BindingNavigator from the Toolbox to the form. We’re going to use the BindingNavigator first.

Now you have added these two controls select the BindinNavigator and go it’s properties. Go to BindingSource Property and select BindingSource1.

Go to our BindingSource1 and go to its properties. For the DataSource Property select MainDatabaseDataSetBindingSource and DataMember property to MainTable.

Now we have created our BindingNavigator and linked it to our BindingSource that we created and linked the BindingSource to the DataSource and have our text fields ready to display the data from the tables we can continue.

Step 4: Displaying the data in the text fields

I’ll show a picture for this one. Select the ID text field and look at the properties. If you’re sorting properties from A to Z then go to the top of the properties. You should see a collapsed Row called (DataBindings). Expand it. Then Select the Text field > DropDown Arrow > BindingSource1 > ID.



Repeat this for each text field. First Name will be BindingSource1 > FirstName etc.

Select the MaintableTableAdaper that is shown on the bottom-centre of the program and in the properties we want to change the ClearBeforeFill value to False.

Next up we want to first that ID field. Go back to DataSources. Go to MainDatabaseDataSet > Maintable and then right-click edit Dataset with Designer. On the Table that is shown in the design view select the ID field, then in the properties change AutoIncrementSeed and AutoIncrementStep to 1 from -1. Changing this will mean when you add new records it will increment from 1. Usually the starting seed is 0 but I prefer 1. I do this because you know i.e. you could be looking at record 49 of 50 but the ID will be 48, it just makes it a little less confusing.

I also like the txtID control to be disabled, just go to the properties and change the property Enabled to False. I do this because this field is auto. We don’t want to modify an auto field.

If you debug the application at this stage the txt fields are filled with data from the database and you can browse through the records.

Now we can view all the records.



I am going to stop the tutorial but to save data use Tableadapter.Update. If you need more help with Databases feel free to contact me.

This tutorial took me about an hour and a half to type, I really did put a lot of effort into this one and I hope it helps someone out. If you like it please +rep it.

Thanks.

If you want the PDF to this tutorial link is below as well.

[Shim]

this is very good tutorial , thanks a lot

+rep

[Scottie1972]

nice tutorial it really is.
but like all the other tuts i have seen in the digital world.
i have no use for a TableAdapter or a DataSet.
they are unflexible and limited.

It would be nice to see a tutorial like that uses Controls that everyone else wants to use.
Like ListBox, ListViews, Dynamically created Controls (Buttons, PictureBox, Panels, etc..)

dont get me wrong. this is a nice tut.
but it it the very same as all the other "How To~Database" tuts on every other website on the web.

you are an awesome coder, and have alot of talent!
I would download this, i would even buy 10 credits if it was useful.
But being that it is just like all the other tuts I have seen everywhere else, I personally have no use for it.

Now! If you can show me how to use the DataBase file to populate a ListView and use the ListView to run database code (delete,update,insert,etc..) then that would be awesome. But really, a DataSet is worthless unless your writing some type of very simple listings program.

Dare to be different, Stand out from the large crowd that wears the same shirt.

[noypikami]

@ scottie

yes, i agree with your reply on this post. bacause wasted a lot of
effort on dataset, it works but its limited.
i just made a database using that but, the adapter only saves a plain text.
because i edited some of the words and text, change colors, styles in each word
put some bullets and align but when you save the data, the database saves it
but the styles and font specifically is lost. however it still saves and store data the
normal way.

[Bloodz_Ninja]

Great share, love it, was looking for something like this to complete mines cooll;

[smashapps]

There was a post about the way Databases saved text. If you're adding bullets etc. it is RichText not Text and you need to save it to the database differently.

Here is a link that discussed this:

viewtopic.php?f=21&p=74063