
Grid update sqldataadapter dataset UpdateCommand Object Reference
I have a grid, whose source of data is a dataset. When the grid loads,
it is populated with the data in the dataset. A user can change any
data in the datagrid. When the user closes the grid, I want that data
to be saved in the database.
In the OnClosing event, I have tried various things, but I keep
getting different errors. What do I need to do to allow users to
update, delete, insert right from the grid? I know for delete and
insert I need the appropriate delete and insert command, which are
still similar to UpdateCommand.
Help is appreciated
--------------
Version 1 of the OnClosing event
If DS.HasChanges() Then
sqladpt.Update(DS.GetChanges, "Tab1")
End If
An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll
Additional information: Update requires a valid UpdateCommand when
passed DataRow collection with modified rows.
----------------------------------------------------
Version 2
If DS.HasChanges() Then
sqladpt.UpdateCommand.CommandText = upstr
sqladpt.Update(DS.GetChanges, "Tab1")
End If
An unhandled exception of type 'System.NullReferenceException'
occurred in BO1.exe
Additional information: Object reference not set to an instance of an
object.
------------------
'Code
Public Class Grd2
Inherits System.Windows.Forms.Form
--Windows Forms Designer generated code
Dim cnn As New SqlConnection("SERVER=S1;DATABASE=DB1;USER
ID=sa;PASSWORD=xyz")
Private DS As New DataSet()
Dim selstr As String = "select * from Tab1"
Dim sqladpt As SqlDataAdapter = New SqlDataAdapter(selstr, cnn)
Public Sub Grd2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
sqladpt.Fill(DS, "Tab1")
MyDataGrid.DataSource = DS.Tables("Tab1").DefaultView
End Sub
Protected Overrides Sub OnClosing(ByVal e As
System.ComponentModel.CancelEventArgs)
If MsgBox("Are you sure?",MsgBoxStyle.YesNo,"")=MsgBoxResult.No Then
e.Cancel = True
Else
If DS.HasChanges() Then
sqladpt.Update(DS.GetChanges, "Tab1")
End If
End If
End Sub
End Class