DataAdapter To As400 / Iseries

[Skittle]

I am using Visual Studio 2010 with VB.Net to connect to an AS400 using the IBM recommended IBM.Data.DB2.Iseries drivers.

I have successfully retrieved data from the AS400/Iseries using SELECT commands from VB.Net.
I have called stored procedures on the AS400/Iseries from VB.Net.
I cannot heowever set up a datadpater to actually update data.
I have populated a dataset from a DataAdapter.Fill and pointed it to a DataGridView but when I change data
in the DataGridView and issue a DataAdapter.Update I get the error, 'Update requires a valid UpdateCommand when passed DataRow collection with modified rows'.

I created the dataAdapter like this behind a form button to read the data and display it in a DataGridView. This works fine and I can see the data on the grid.

 Try

            MyConnection6.ConnectionString = "DataSource=MyAS400;"

            MyConnection6.Open()

            Dim MyCommand6 As New IBM.Data.DB2.iSeries.iDB2Command
            MyCommand6.Connection = MyConnection6
            MyCommand6.CommandType = CommandType.Text
            MyCommand6.CommandText = "SELECT * FROM mylib.myfile"

            MyAdapter.SelectCommand = MyCommand6

            MyAdapter.Fill(MyDataSetA)
            DataGridView1.DataSource = MyDataSetA
            DataGridView1.DataMember = MyDataSetA.Tables(0).TableName

            MyConnection6.Close()


        Catch ex As Exception
            MessageBox.Show("Unexpected Program Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

        End Try

I then edit a field in the grid and hit an 'Update' button to call this code:-

Try
            MyConnection6.ConnectionString = "DataSource=MyAS400;"
            MyConnection6.Open()
            MyAdapter.Update(MyDataSetA)
            MyConnection6.Close()
        Catch ex As Exception
            MessageBox.Show("Unexpected Program Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

When the MyAdatper.Update(MyDataSetA) is executed I get the error,'Update requires a valid UpdateCommand when passed DataRow collection with modified rows'.

My assumption is that I somehow need to set UpdateCommand attached to the DataADapter but I am unclear how to do this. Can anybody help?








Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

I have resolved this.

I needed to build the UpdateCommand, DeleteCommand and InsertCommand via a CommandBuilder.

 Dim MyAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New iDB2DataAdapter("SELECT * FROM MyLib.MyFile", MyConnection6)
 Dim MyBuilder As iDB2CommandBuilder = New iDB2CommandBuilder(MyAdapter)

The command settings are then all filled in for me and the MyAdapter.Update(MyDataSETA) then works.

However I have a follow up question.
The Command Builder has generated an update command statement that seems very inefficient. From the immediate window
I identified the statement value:-

?MyBuilder.GetUpdateCommand.CommandText
"UPDATE MyLib.MyFile SET FC001 = @p1, FC002 = @p2, FC003 = @p3, FC004 = @p4, FC005 = @p5, FC006 = @p6, FC007 = @p7, FC008 = @p8, FC009 = @p9, FC011 = @p10 WHERE ( (FC001 = @p11) AND (FC002 = @p12) AND (FC003 = @p13) AND (FC004 = @p14) AND (FC005 = @p15) AND (FC006 = @p16) AND (FC007 = @p17) AND (FC008 = @p18) AND (FC009 = @p19) AND (FC011 = @p20) )"

A condition is included for every field in the WHERE clause.
Would a better practice be to code the UpdateCommand so the WHERE clause just uses the unique identity of the row?
The assumption being the unique id cannot be changed in any updates I perform against the dataset?



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.