update data in datagrid

[jason]

Hi, all
How can I update data (multiple rows, but not every rows) using dataset in
datagrid?

I mean is there any way I can let datagrid know which row(s)/column(s) has been
modified and update them at database?
Thanks.

 

[Scott M.]

If the DataSet was populated from a database using a DataAdapter, then when
you call the DataAdapter's Update method it will know which rows are
different from the original.

If you built the table in the DataSet yourself, I believe there is a method
of a row that tells you if it is "dirty" (hasn't been updated).

 

[Cor Ligthert]

Hi Jason,

Your datagrid shows always dynamicly the data in the datasource.

So when that is by instance a dataview with a rowfilter and your new datarow
in the datasets is conform that rowfilter it will be showed up when you add
it to the dataset.

I hope this gives an idea?

Cor

 

[jason]

Here is the code, Button1 will load data from local Access (*.mdb) file, and
Button2 will save modfied data to Access file. But somehow, after I press
Button2, nothing happened. Please help.
Thanks in advance.


Imports System.Data.OleDb

Public Class Form1
Inherits System.Windows.Forms.Form

Public ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=D:\test.mdb"

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
' & "Data Source=D:\test.mdb"

' Open the Access database.
Dim cn As New OleDbConnection
cn.ConnectionString = ConnString

Try
cn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
' Ensure that the connection is closed.
' (It does not throw an exception even if the Open command failed.)
cn.Close()
End Try

' Define the command
Dim sqlString As String = "SELECT DCN,Field1 from Table1"

Dim da As New OleDbDataAdapter(sqlString, cn)

Dim ds As New DataSet

da.Fill(ds, "Table1")

DataGrid1.DataSource = ds.Tables("Table1")


' Close the connection only if it was opened.
'ConnectionState.Closed = 0
If (cn.State And ConnectionState.Open) <> 0 Then
cn.Close()
End If
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click

Dim cn As New OleDbConnection(ConnString)
cn.Open()

Dim da As New OleDbDataAdapter("SELECT DCN,Field1 from Table1", cn)

Dim ds As New DataSet

Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)

da.Fill(ds, "Table1")


' Update database with modified data
da.ContinueUpdateOnError = True
da.UpdateCommand = cmdBuilder.GetUpdateCommand()
da.Update(ds, "Table1")


' Close the connection only if it was opened.
'ConnectionState.Closed = 0
If (cn.State And ConnectionState.Open) <> 0 Then
cn.Close()
End If
End Sub

 

[Cor Ligthert]

Hi Jason,

The answer is why it is not updating is is simple, however why that strange
closing code.
In my opinion it is even better to open an access database at the begin of
the program and to close it at the end. That prevent users from moving the
mdb file in the maintime (this is only for access).

The closing statement is strange, where did you read that, after all is
working it is better to place all in a try, catch finally end try block.
However because the sentence above you do not need a finally.

The answer on why it is not updating is simple, you fill the dataset with
old information before you do the update so what you have entered in the
datagrid will be disapeared.

You probably need more code however first delete the sentence I point you on
in this procedure and better change it in a good way.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button2.Click
Dim cn As New OleDbConnection(ConnString)
cn.Open()
Dim da As New OleDbDataAdapter("SELECT DCN,Field1 from Table1", cn)
Dim ds As New DataSet
Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
da.Fill(ds, "Table1")

This one above fills the dataset again with the old data from the datagrid
so you have to remove this.

' Update database with modified data
da.ContinueUpdateOnError = True

This one is a killer I would replace this with a Try, Catch and EndTry block
like this.
(This as well in the button1 at the fill, I show you the most simple one)

da.UpdateCommand = cmdBuilder.GetUpdateCommand() Try
da.Update(ds, "Table1")

Catch ex as OleDbexception
messagebox.show(ex.tostring)
'this has to be if it is good in an if block where you do your
actions when there is an error
End Try

This is absolute not complete, however to give you a go.

(all typed in this message so watch typos or other errors)

I hope this helps?

Cor

 

[Jason]

Thanks Cor, it works.

By the way, "The closing statement is strange, where did you read that?" Ha...
From Microsoft Press, "Programming Microsoft Visual Basic.Net Version 2003, pg
714, Opening and Closing the Connection".