Datagrid Emulate Access

[lrfcbabe]

I just hope this is not a repost of someone elses question, probably is. What I am thinking is how do I best use the datagridview to update an Access database. As if I was clicking on the save button in Access. Or should I create a dataset of the datagridview, delete the old/existing table, then recreate a new table of the same name from the dataset/datagrid. That sounds good and may be faster than trying to loop through each record updating them one at a time. I have the data loaded in my datagrid, I can change modify the data but cannot get it back into the database. Again Sorry for the repost but I am struggling with .Net and Access. Maybe it is to easy for me to figure out. Also what is the proper tag to use for posting .Net code?

<code vb>
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.String
Imports System.Text
Imports System.Windows

Partial Public Class CPFManager
Private CPFile As String
Private CPFileType As String
Private conn As New OleDb.OleDbConnection
Private coCPFMan As OleDbCommand
Private cbCPFMan As OleDbCommandBuilder
Private daCPFMan As OleDbDataAdapter
Private dsCPFMan As DataSet
Private dtCPFMan As DataTable = New DataTable()
Dim strConn As String = "Provider=Microsoft.Jet.oledb.4.0;Data Source="
Public Shared CellXInd As Double
Public Shared CellYInd As Double

Private Sub CPFManager_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Me.CenterToScreen()

End Sub

Public Function TestDBConn() As Boolean

Try
conn = New OleDbConnection(strConn & CPFile)
conn.Open()
TestDBConn = True
Catch ex As Exception
MessageBox.Show(CPFile & " " & ex.Message)
TestDBConn = False
End Try

End Function


Private Sub LoadDatasetToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadDatasetToolStripMenuItem.Click

OpenFileDialog.InitialDirectory = Application.StartupPath
OpenFileDialog.Filter = "valid files (*.mdb)|*.mdb"
If OpenFileDialog.ShowDialog() = DialogResult.OK Then
If OpenFileDialog.FileName.ToString.Length > 0 Then
CPFile = OpenFileDialog.FileName.ToString
If TestDBConn() Then

Dim SchemaTable As DataTable
Dim i As Integer
Dim TName As String
SchemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})
If Not dsCPFMan Is Nothing Then
DataGridView1.DataSource = Nothing
DataGridView1.DataMember = Nothing
dtCPFMan = Nothing
dsCPFMan = Nothing
daCPFMan = Nothing
ListBox1.Items.Clear()
End If

For i = 0 To SchemaTable.Rows.Count - 1
TName = SchemaTable.Rows(i)!TABLE_NAME.ToString
'Debug.Print(TName)
'Debug.Print(TName.Substring(0, 4).ToUpper)
If TName.Length >= 4 Then
If TName.Substring(0, 4).ToUpper <> "MSYS" Then
ListBox1.Items.Add(TName)
End If
End If
Next
conn.Close()
End If
End If
End If

End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

If TestDBConn() Then
CPFileType = ListBox1.SelectedItem.ToString
GetData("SELECT * FROM " + CPFileType + ";")
End If

End Sub

Public Function GetData(ByVal selectCommand As String) As DataSet

If TestDBConn() Then
daCPFMan = New OleDbDataAdapter(selectCommand, conn)
dsCPFMan = New DataSet()
daCPFMan.Fill(dsCPFMan, CPFileType)
DataGridView1.DataSource = dsCPFMan.Tables(0)
DataGridView1.DataMember = CPFileType
DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
conn.Close()
End If

Return dsCPFMan
End Function

Private Sub UpdateDatasetToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateDatasetToolStripMenuItem.Click

If TestDBConn() Then

Dim sql As String = "SELECT * FROM " + CPFileType + ";"
Dim daCPFMan As OleDb.OleDbDataAdapter = New OleDbDataAdapter(sql, conn)
Dim cbCPFMan As OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daCPFMan)
daCPFMan.Update(dsCPFMan, CPFileType)
End If

GetData("SELECT * FROM " + CPFileType + ";")
conn.Close()

End Sub

End Class
</code>

 

[MajP]

The easiest way is to drag/add a datasource onto your form it will create and a typed binding source, and tableAdapter. Make the bindingsource the datasource of the datagrid. The typed tableadapter will will already have the adaper commands to update, delete, and insert to the database. You can do all this manually instead using a dataadapter but then you have to write all of those commands and create the parameters for those commands. The syntax is not that easy to write error free and it is very time consuming for a large table/query. Using an instance of the typed tableadapter, then you simply use the update command of the tableadapter and it updates the database from the dataset, insert, delete, and update commands. It updates the dataset from the database with the appropriate select command. The bindingsource does all the work of keeping the dataset updated from the datagridview.

http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

http://msdn.microsoft.com/en-us/library/0yy0c9z8.aspx

With this method you can basically emulate a bound form with very little code.