Grid update sqldataadapter dataset UpdateCommand Object Reference 
Author Message
 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



Tue, 16 Aug 2005 05:01:57 GMT  
 Grid update sqldataadapter dataset UpdateCommand Object Reference
Hi,
   The "UpdateCommand" of your "sqladpt" object is empty when you call
sqladpt.Update(DS.GetChanges, "Tab1"). That is why you get the error "An
unhandled exception of type 'System.InvalidOperationException' occurred in
system.data.dll" .
   What you need to add to your code is this one line before you call the
update command:

    sqladpt.UpdateCommand = New SqlClient.SqlCommand(upstr)

Hope this helps,
Kris & Hema
VB Dot Net Team

--
This posting is provided "AS IS" with no warranties, and confers no rights.

___

Quote:
> 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



Tue, 16 Aug 2005 05:33:45 GMT  
 Grid update sqldataadapter dataset UpdateCommand Object Reference
 If DS.HasChanges() Then
     sqladpt.UpdateCommand.CommandText = upstr
     sqladpt.Update(DS.GetChanges, "Tab1")
   End If

Did you set Connection for Update command as MS recommends?:
myDataAdapter.UpdateCommand.Connection = (SqlConnection)
myDataAdapter.SelectCommand.Connection;

HTH
Alex



Tue, 16 Aug 2005 05:40:05 GMT  
 Grid update sqldataadapter dataset UpdateCommand Object Reference
SDK help then is giving invalid sample code - causes exception during run -
see the topic
ms-help://MS.NETFrameworkSDK/cpref/html/frlrfsystemdatasqlclientsqldataadapt
erclassinsertcommandtopic.htm

The code there doesn't initialize InsertCommand property with new
SqlCommand.

Alex



Quote:
> Hi,
>    The "UpdateCommand" of your "sqladpt" object is empty when you call
> sqladpt.Update(DS.GetChanges, "Tab1"). That is why you get the error "An
> unhandled exception of type 'System.InvalidOperationException' occurred in
> system.data.dll" .
>    What you need to add to your code is this one line before you call the
> update command:

>     sqladpt.UpdateCommand = New SqlClient.SqlCommand(upstr)

> Hope this helps,
> Kris & Hema
> VB Dot Net Team

> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.

> ___


> > 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



Tue, 16 Aug 2005 05:45:51 GMT  
 Grid update sqldataadapter dataset UpdateCommand Object Reference
Thanks much. I was missing a few steps.


Quote:
> Hi,
>    The "UpdateCommand" of your "sqladpt" object is empty when you call
> sqladpt.Update(DS.GetChanges, "Tab1"). That is why you get the error "An
> unhandled exception of type 'System.InvalidOperationException' occurred in
> system.data.dll" .
>    What you need to add to your code is this one line before you call the
> update command:

>     sqladpt.UpdateCommand = New SqlClient.SqlCommand(upstr)

> Hope this helps,
> Kris & Hema
> VB Dot Net Team

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.

> ___


> > 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



Tue, 16 Aug 2005 22:54:48 GMT  
 Grid update sqldataadapter dataset UpdateCommand Object Reference
I face different type of error. Hope you all can help me.
when i run this simplified code. It give me error "ERROR-no error
information available". This is an Odbc Exception and my table
"TEMPDATA" has PK and my field "Status" is varchar with length = 10.

OdbcCommand cmd = new OdbcCommand("SELECT * FROM TEMPDATA order by
ID",Conn);
da.SelectCommand = cmd;
new OdbcCommandBuilder(da);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds,"TEMPDATA");
foreach(System.Data.DataRow dr1 in ds.Tables["TEMPDATA"].Rows)
{
 dr1.BeginEdit();
 dr1["Status"]="Due";
 dr1.EndEdit();

Quote:
}

 try
{
 da.Update(ds,"TEMPDATA");
 MessageBox.Show("OK!");
Quote:
}

catch (OdbcException ex)
{
 MessageBox.Show(ex.Message.ToString());
Quote:
}



Wed, 17 Aug 2005 08:37:38 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Dataset changes updates grid , How to update SQL table

2. What is difference between SqlDataAdapter and DataSet ?

3. controling transaction using dataset and sqldataadapter

4. datagrid updatecommand event args does not recieve updated information

5. Loading tables from a database via SQLDataAdapter and SQLDataAdapter.Fill

6. dataset problems - cant update sql after editing dataset

7. Updating the database using the DataSet object

8. Help me: Update SQL Server table through sqlDataAdapter...

9. Passing an object reference by reference where the object has a default property

10. Problems with datasource for a grid control / sqldataadapter / dataset.

11. References: Update References in MDE to other MDEs

12. referencing a dataset field name

 

 
Powered by phpBB® Forum Software