Link to home
Start Free TrialLog in
Avatar of rrmccall
rrmccall

asked on

Updating data with DataAdapter, Dataset and Grids

Imports System.Data.OleDb
Public Class Form1
    Inherits System.Windows.Forms.Form
   
    Dim CN As New ADODB.Connection
    Dim  RsList As ADODB.Recordset
    Dim Cncmd As ADODB.Command

    Dim RS, RS1 As New ADODB.Recordset()
    Dim myDA As OleDbDataAdapter = New OleDbDataAdapter()
    Dim myDS As DataSet = New DataSet()
Dim strSQL as string = "SELECT cheminv.lot_number, chemical.chemical_id, chemical.current_cheminv_id, chemical.name, chemical.generic_name WHERE cheminv.lot_number LIKE  'R%'"
                                                           
Private Sub Form1_load (byval sender as . . .)
   
        CN = New ADODB.Connection     'ACCESS CONNECTION
        CN.Open("DRIVER={Firebird/Interbase(r)driver};Database=192.168.23.12:C:\Program                                                                                                                   Files\PKS\cmpdwin.dat;", "bob", "123")
        Cncmd = New ADODB.Command()
        Cncmd.ActiveConnection = CnnList
        Call Data() 'DISPLAY TABLE INFORMATION ON  Data Grid
end sub

Private sub data()
       ' diplay Table information on Data Grid
        RS = New ADODB.Recordset
        RS.Open(strsqlc, CN, 1, 2)

        '  Dim myDA As OleDbDataAdapter = New OleDbDataAdapter()
        '  Dim myDS As DataSet = New DataSet()
        myDA.Fill(myDS, RS, "cheminv")
        DataGrid1.DataSource = myDS.Tables("cheminv")
        DataGrid1.Refresh()

end sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cb As OleDbCommandBuilder
        DataGrid1.Update()

        cb = New OleDbCommandBuilder(Me.myDA)
        Me.myDA.SelectCommand.CommandText = strsqlc
        cb.RefreshSchema()
        Me.myDA.InsertCommand = cb.GetInsertCommand
        Me.myDA.UpdateCommand = cb.GetUpdateCommand
        Me.myDA.DeleteCommand = cb.GetDeleteCommand
        Me.myDA.Update(myDS.GetChanges, "cheminv") ,or
        Me.myDA.Update(myDS.Tables, "cheminv")
     
        myDA.Update(myDS.Tables("cheminv"))
        Me.myDA.SelectCommand.CommandText = strsqlc
        cb.RefreshSchema() ' Use this every time the SelectCommand Command Text is altered
        myDA.update(myDS.tables("next table")
                                     .
                                     .
                                     .    
End Sub

When I select button2 to update the data i get an An unhandled exception of type 'System.NullReferenceException' occurred in chemicalgrid.exe: Additional information: Object reference not set to an instance of an object.  I do not understand the relationship between the grid and myda adapter and command builder.  Could somone explain to me?

Thanks

Avatar of gajender_99
gajender_99

one of the fileds you are entring is null where on the tables it is not or you are trying to enter data null value in a mandatory column

gajender
>>> Me.myDA.Update(myDS.GetChanges, "cheminv")
try checking for changes first

If myDS.HasChanges Then
      myDA.Update(myDS.GetChanges, "cheminv")
....

If there are no changes in the DataSet the myDS.GetChanges returns "Nothing" or a NULL Reference the error might be because of that.

I am not sure because you have not pointed out the line at which the error is thrown, if this solves your problem alright otherwise point out the line at which Error is thrown...

>> 'System.NullReferenceException' occurred in chemicalgrid.exe: ...
It has nothing to with the Grid "chemicalgrid.exe" is the name of the EXE and not a grid, Naming Projects intuitively saves headaches at times :-)
ASKER CERTIFIED SOLUTION
Avatar of Havagan
Havagan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rrmccall

ASKER

Gajender 99,

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cb As OleDbCommandBuilder
        DataGrid1.Update()

        cb = New OleDbCommandBuilder(Me.myDA)
        Me.myDA.SelectCommand.CommandText = strsqlc <ERROR' OCCURED HERE
        cb.RefreshSchema()
        Me.myDA.InsertCommand = cb.GetInsertCommand
        Me.myDA.UpdateCommand = cb.GetUpdateCommand

Paul,
Thanks for the lesson.


Regards,
Rick