Problem with Datagrid Update

[reycons]

Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbDataAdapter

Public Class frmGrid
Private Const SELECT_STRING As String = "SELECT * FROM departmentprofile"
Private Const CONNECT_STRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\akomykel\Desktop\schoolapp\SchoolApp\obj\Debug\sampledatabase.mdb"

' The DataSet that holds the data.
Private m_DataSet As DataSet
Private data_adapter As New OleDbDataAdapter
Private command_builder As New OleDb.OleDbCommand

Private Sub frmGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Create the SqlDataAdapter.
data_adapter = New OleDbDataAdapter(SELECT_STRING, _
CONNECT_STRING)

' Map Table to Contacts.
data_adapter.TableMappings.Add("Table", "departmentprofile")

' Fill the DataSet.
m_DataSet = New DataSet()
data_adapter.Fill(m_DataSet)

' Bind the DataGrid control to the Contacts DataTable.
DataGrid1.SetDataBinding(m_DataSet, "departmentprofile")
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChange
Dim tables As DataTableCollection = m_DataSet.Tables
Dim view1 As New DataView(tables(0))
Dim source1 As New BindingSource()
source1.DataSource = view1
DataGrid1.DataSource = source1
source1.Filter = "fullname Like '" & UCase$(Trim(Mid(TextBox1.Text, 1))) & "%'"
End Sub
End Class

As you can see in my code i had load the tables and filter the records. the only problem is how to update the record from the datagrid. Can anyone help me on what is the code for updating the record directly from the datagrid to the table.

 

[pmegan]

You have to assign insert and/or update commands to the data adapter. If you don't know how to create them, try using a CommandBuilder. Create an instance using your data adapter like this:

Dim cb as new OleDbCommandBuilder(data_adapter)

Then you can use the UpdateCommand, InsertCommand and DeleteCommand that are generated to get the commands for the data adapter. I don't like assigning the results directly from the command builder, usually I'll write them to the debug window, see if they need any tweaking and assign them manually.

Once you have your commands assigned you use data_adapter.update(tables(0)) to save the changes back to the data source.

 

[reycons]

Private Sub frmGridFinal_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

Dim cb As New OleDbCommandBuilder(data_adapter)

If m_DataSet.HasChanges() Then
Dim data_adapter As OleDbDataAdapter
Dim command_builder As New OleDbCommand
'Dim command_builder As OleDbCommand

' Create the DataAdapter.
data_adapter = New OleDbDataAdapter(SELECT_STRING, CONNECT_STRING)

' Map Table to Contacts.
data_adapter.TableMappings.Add("Table", "departmentprofile")

' Make the CommandBuilder generate the
' insert, update, and delete commands.

command_builder = New OleDbCommand(data_adapter)

' Save the changes.
data_adapter.Update(m_DataSet)
End If
End Sub

As you can see i have a code here that directly saved to the datagrid, but i do have a problem regarding here ----> command_builder = New OleDbCommand(data_adapter) - it generated an error that says "Value of system.Data.Oledb.OledbDaptaadapter cannot be converted to string.

 

[pmegan]

You've got something mismatched. I was kind of confused about the declaration for command_builder at the top of your class yesterday. You have:

[blue]Private command_builder As New OleDb.OleDbCommand[/blue]

This makes it unclear whether this is supposed to be a Command or a CommandBuilder

Since the command_builder object is OleDbCommand instance, it can't use an OleDbDataAdapter as a constructor parameter.