Update error using IBM DB2 UDB for iSeries .NET provider - IBM AS400

This is a discussion on Update error using IBM DB2 UDB for iSeries .NET provider - IBM AS400 ; My select statement works like a charm, but I can't get update to work. Here's my code: This works just fine: Dim connectionString As String = "DataSource=myiSeries; Default Collection=MyDB" Dim cnn As New iDB2Connection(connectionString) Dim ds As New DataSet Dim ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Update error using IBM DB2 UDB for iSeries .NET provider

  1. Update error using IBM DB2 UDB for iSeries .NET provider

    My select statement works like a charm, but I can't get update to
    work.

    Here's my code:

    This works just fine:

    Dim connectionString As String = "DataSource=myiSeries;
    Default Collection=MyDB"
    Dim cnn As New iDB2Connection(connectionString)
    Dim ds As New DataSet
    Dim da As New iDB2DataAdapter

    da.SelectCommand = New iDB2Command("select * from table1",
    cnn)
    da.Fill(ds)

    This does not:

    Dim connectionString As String = "DataSource=myiSeries;
    Default Collection=myDB"
    Dim cnn As New iDB2Connection(connectionString)
    Dim ds As New DataSet
    Dim da As New iDB2DataAdapter

    da.UpdateCommand = New iDB2Command("update table1 set size =
    '5' where style = 'AB001'", cnn)
    da.Update(ds, "table1")


    I get this error message: "Update unable to find
    TableMapping['table1'] or DataTable 'table1'."



    Can anybody help or at least point me in the right direction to get
    help?


  2. Re: Update error using IBM DB2 UDB for iSeries .NET provider

    On Oct 30, 11:41 pm, moondog wrote:
    > My select statement works like a charm, but I can't get update to
    > work.
    >
    > Here's my code:
    >
    > This works just fine:
    >
    > Dim connectionString As String = "DataSource=myiSeries;
    > Default Collection=MyDB"
    > Dim cnn As New iDB2Connection(connectionString)
    > Dim ds As New DataSet
    > Dim da As New iDB2DataAdapter
    >
    > da.SelectCommand = New iDB2Command("select * from table1",
    > cnn)
    > da.Fill(ds)
    >
    > This does not:
    >
    > Dim connectionString As String = "DataSource=myiSeries;
    > Default Collection=myDB"
    > Dim cnn As New iDB2Connection(connectionString)
    > Dim ds As New DataSet
    > Dim da As New iDB2DataAdapter
    >
    > da.UpdateCommand = New iDB2Command("update table1 set size =
    > '5' where style = 'AB001'", cnn)
    > da.Update(ds, "table1")
    >
    > I get this error message: "Update unable to find
    > TableMapping['table1'] or DataTable 'table1'."
    >
    > Can anybody help or at least point me in the right direction to get
    > help?


    If you want to update a table in .net using an sql statement you
    should use something like:
    Dim command As New iDB2Connection("update table1 set size =
    > '5' where style = 'AB001'", cnn)

    dim x as integer
    x=command.ExecuteNonQuery ()

    x will be the return value of the sql which should be the number of
    rows in this case. You may find the command type needs changing as I
    dont have a iDB2Connection to hand & use the mssqlserver command
    types. You may find that just adding table1 to your dataset works.
    x.Tables.Add("table1")
    I think the da.update should be used when you have pulled down data
    from the db & updated several rows. The sql should then refer to the
    new values of each row one at a time.

    HTH
    Jonathan


  3. Re: Update error using IBM DB2 UDB for iSeries .NET provider

    On Oct 31, 10:31 am, Jonathan Bailey
    wrote:
    > On Oct 30, 11:41 pm, moondog wrote:
    >
    >
    >
    >
    >
    > > My select statement works like a charm, but I can't get update to
    > > work.

    >
    > > Here's my code:

    >
    > > This works just fine:

    >
    > > Dim connectionString As String = "DataSource=myiSeries;
    > > Default Collection=MyDB"
    > > Dim cnn As New iDB2Connection(connectionString)
    > > Dim ds As New DataSet
    > > Dim da As New iDB2DataAdapter

    >
    > > da.SelectCommand = New iDB2Command("select * from table1",
    > > cnn)
    > > da.Fill(ds)

    >
    > > This does not:

    >
    > > Dim connectionString As String = "DataSource=myiSeries;
    > > Default Collection=myDB"
    > > Dim cnn As New iDB2Connection(connectionString)
    > > Dim ds As New DataSet
    > > Dim da As New iDB2DataAdapter

    >
    > > da.UpdateCommand = New iDB2Command("update table1 set size =
    > > '5' where style = 'AB001'", cnn)
    > > da.Update(ds, "table1")

    >
    > > I get this error message: "Update unable to find
    > > TableMapping['table1'] or DataTable 'table1'."

    >
    > > Can anybody help or at least point me in the right direction to get
    > > help?

    >
    > If you want to update a table in .net using an sql statement you
    > should use something like:
    > Dim command As New iDB2Connection("update table1 set size => '5' where style = 'AB001'", cnn)
    >
    > dim x as integer
    > x=command.ExecuteNonQuery ()
    >
    > x will be the return value of the sql which should be the number of
    > rows in this case. You may find the command type needs changing as I
    > dont have a iDB2Connection to hand & use the mssqlserver command
    > types. You may find that just adding table1 to your dataset works.
    > x.Tables.Add("table1")
    > I think the da.update should be used when you have pulled down data
    > from the db & updated several rows. The sql should then refer to the
    > new values of each row one at a time.
    >
    > HTH
    > Jonathan- Hide quoted text -
    >
    > - Show quoted text -


    Oops- the command should have not been an iDB2Connection but
    iDB2Command.

    Jonathan


  4. Re: Update error using IBM DB2 UDB for iSeries .NET provider

    Thanks. I've made the change and am now getting a different error,
    which is progress.

    New error message: "The operation cannot complete because the
    connection is not valid."

    Which is strange because the select statement still works just fine
    with the exact same connection string.


    The code now looks like this:

    Dim connectionString As String = "DataSource=myiSeries;
    Default Collection=MyDB;"
    Dim cnn As New iDB2Connection(connectionString)
    Dim ds As New DataSet

    Dim x As Integer = 0
    Dim command As New iDB2Command("update table1 set size = '5'
    where style = 'AB001'", cnn)
    x = command.ExecuteNonQuery



  5. Re: Update error using IBM DB2 UDB for iSeries .NET provider

    On Oct 31, 4:58 pm, moondog wrote:
    > Thanks. I've made the change and am now getting a different error,
    > which is progress.
    >
    > New error message: "The operation cannot complete because the
    > connection is not valid."
    >
    > Which is strange because the select statement still works just fine
    > with the exact same connection string.
    >
    > The code now looks like this:
    >
    > Dim connectionString As String = "DataSource=myiSeries;
    > Default Collection=MyDB;"
    > Dim cnn As New iDB2Connection(connectionString)
    > Dim ds As New DataSet
    >
    > Dim x As Integer = 0
    > Dim command As New iDB2Command("update table1 set size = '5'
    > where style = 'AB001'", cnn)
    > x = command.ExecuteNonQuery


    I think the dataadapter classes are smart enough to open & close
    connections as required. You might want to try cnn.open before the
    executenonquery.

    Jonathan.


  6. Re: Update error using IBM DB2 UDB for iSeries .NET provider

    Oops, you're right. Must open connection first.

    It works!!!!

    Thanks guys.

    Here is my final "update" function for anybody that's interested:

    Function UpdateData(ByVal strSQL As String)
    Dim ConnectionString As String = "DataSource=123.45.67.89;
    Default Collection=MYDBASE;"
    Dim cn As New iDB2Connection(ConnectionString)
    Dim ds As New DataSet

    cn.Open()

    Dim x As Integer = 0
    Dim command As New iDB2Command(strSQL, cn)
    x = Command.ExecuteNonQuery

    cn.Close()

    Return x

    End Function




+ Reply to Thread