DotNetWebTutor.com                                                    Link to Us

Databases
visual basic databases
ADO.Net (Active Data Objects) gives you many classes to access and manipulate data

BindingSource-
Handles the communication between your program and the database

BindingNavigator Control
A toolstrip that has data navigation controls on it

DataGridView Control
Allows you to view or edit data in a spreadsheet format

Example

  • Create a new project
  • Add an access database to your project
    • Select the data menu
    • Select Add New DataSource

data add new data source

    • Select the database icon and click Next

data source configuration wizard

    • Select New Connection

new connection

    • If Microsoft Access Database File is not listed in the DataSource click the Change button

change connection

    • Choose Microsoft Access
    • Click OK

change data source to access

    • Click Browse to find your database file

browse

    • Click OK

OK

    • Click the Next button

next

    • Visual Studio ask you to copy the file into your project. Click Yes

yes

    • Next it asks you to save the connection string. Click next to save your connection in a connection string

connection string

    • Select the your database table you want to access and click Finish

tables

    • It adds 3 files to your project
      • App.config
      • The database
      • And XSD file representing your dataset

dataset

The database get copied in two places.

  1. Your project folder
  2. The debug folder

Changes made to the database will be made to the copy in your debug folder because that is where the executable is located

  • Drag a DataGridView control (from toolbox) to the form

drag the datagridview control

  • Click on the smart tag

datagridview tasks

  • Choose your database table as the datasource
  • Drag a BindingNavigator to the form

bindingsource

  • Change the BindingSource property to your database binding source\

bindingsource property

  • Test it
  • Now you can navigate through your records and add and delete from it
  • You can also edit inside the grid by double clicking inside a cell and changing it

This only makes changes in the dataset.
The dataset is just an in memory representation of your database

You have to add some more code to actually change the real database

  • In the component tray select the dataset component and click on the smart tag

1

  • Select Edit in Dataset Designer
  • Right-click on the tableAdapter
    • Select Configure

configure

  • Click Next

tableadapter configuration wizard

  • Click Finish

fill a datatable

  • Add a button control
    • Drag it on top of the BindingsNavigator Control
    • Change the text to Update

update
=============Alternative========================

  • Add the button to the BindingsNavigator control

menu button
Chose an image for the button which will be doing the update to the database
=============Alternative========================

  • Add this code to the button-click event handler:

Try
Me.SurveyBindingSource.EndEdit()
Me.SurveyTableAdapter.Update(Me.SurveyDataSet.Survey)
MessageBox.Show("Database was updated")
Me.SurveyTableAdapter.Fill(Me.SurveyDataSet.Survey)
Catch ex As Exception
MessageBox.Show("Problem updating database")
End Try  

Explanation:
Me.SurveyBindingSource.EndEdit()
EndEdit tells the dataset you are done making changes

Me.SurveyTableAdapter.Update(Me.SurveyDataSet.Survey)
Update method of the tableAdapter accepts the survey table of the dataset and updates the actual Access database

Me.SurveyTableAdapter.Fill(Me.SurveyDataSet.Survey)
Finally, The fill method refreshes your database from the Access database. This is the same line that is in the page_load that originally  loads your dataGridView

 

When you test it and make changes and click Update you may notice that once you run it again your changes will not be made. Remember that When you added the database it added it to two places. In your project files and in the debug directory.

Every time you run your application from Visual Studio it recopies the database from your project to the debug, thus erasing any changes. To see the changes Open your project folder with Windows Explorer and go to the bin folder and then the debug. Run the EXE file and make changes. Close it and run it again and you will see your changes.
11
visual basic databases