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

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

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

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.

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