FAQ: How do I make a basic Data Access application (Next, Previous, First, Last, Update, Delete, Insert, Save)?

Question

How do I make a basic Data Access application (Next, Previous, First, Last, Update, Delete, Insert, Save)?

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 1

Here are four approaches (Take MS Access database file for example):

1) Via DataGridView: Update (Insert/Update/Delete) the data back into a MS Access database from DataGridView.

Code sample:

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/

Imports System.Data.OleDb
Public Class Form1
  Dim myDA As OleDbDataAdapter
  Dim myDataSet As DataSet
  Private Sub Form1_Load(ByVal sender As System.Object, _
           ByVal e As System.EventArgs) Handles MyBase.Load
    Dim con As OleDbConnection = New OleDbConnection( _
           "Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
    con.Open()
    myDA = New OleDbDataAdapter(cmd)
    'Here one CommandBuilder object is required.
    'It will automatically generate DeleteCommand, 
    'UpdateCommand and InsertCommand for DataAdapter object  
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
    myDataSet = New DataSet()
    myDA.Fill(myDataSet, "MyTable")
    DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
    con.Close()
    con = Nothing
  End Sub

  ' Save data back into database  
  Private Sub Button1_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button1.Click
    Me.Validate()
    Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
    Me.myDataSet.AcceptChanges()
  End Sub
End Class

     Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"

2) Execute Select/Insert/Delete/Update T-SQL commands in code

Code samples for MS Access databases

http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/

 

Code samples for SQL Server databases

http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/

 

 

3) Use the Data Wizard with a BindingNavigator control.

Please check the 11th post in this thread for a detailed walkthrough:

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

 

Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer" 

 

 

4) Operate DataSet/DataTable in code

Please check the 12th and 13th posts in this thread for a detailed code sample:

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

 

For more FAQ about Visual Basic .NET General, please see Visual Basic .NET General FAQ

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.