Avatar of vbcoder14
vbcoder14 asked on

Exception when trying to insert into AS400 DB2 from VB.NET

I have an ODBC connection setup to a DB2. I am trying to insert into a table from VB.NET, the code as follows:

Dim info as String = "'abc123'"
Dim insert as String = "INSERT INTO LIBRARY.FILE (LABEL) VALUES ('"& info &"')"
adpODBC.SelectCommand = New OdbcCommand(insert, odbcConnection)
adpODBC.Fill(ds)

After doing the Fill command, I get the following excepton:
System.OverflowException: "Arithmetic operation resulted in an overflow"

The LABEL (or FIELD) in the TABLE (or FILE) that I am using is of length 150. The Value trying to be inserted is much smaller than that, hence, I am stuck finding out why an overflow exception. Could it be a wrong syntax?
Visual Basic.NETDB2IBM System i

Avatar of undefined
Last Comment
vbcoder14

8/22/2022 - Mon
SOLUTION
Gary Patterson, CISSP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
vbcoder14

Hi Gary,

I think we might be on the right track. Can you help me elaborating on how to use the Update method? What parameters is it expecting? Because now I get another set of errors trying to use the Update method.

Thanks!
Gary Patterson, CISSP

Can you please post your code?  Lot less guesswork for me.
Gary Patterson, CISSP

Good idea if you first understand the basics of using data adapters to update a data source:

http://msdn.microsoft.com/en-us/library/33y2221y.aspx

IBM publishes a good Redbook full of .NET examples.  It is a little old now, but still a good reference.  This is usually where I go when people ask me .NET questions related to AS/400 / iSeries / IBM i.

http://www.redbooks.ibm.com/abstracts/sg246440.html

- Gary Patterson
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
vbcoder14

Dim info as String = "'abc123'"
Dim insert as String = "INSERT INTO LIBRARY.FILE (LABEL) VALUES ('"& info &"')"
adpODBC.InsertCommand = New OdbcCommand(insert, odbcConnection)
adpODBC.Update(??)

I am not sure what to put into the parameters of the Update method
Gary Patterson, CISSP

Have you looked up odbcdataadapter.update on MSDN?  That ought to be your first stop when working with a class or method you don't know very well.

You use the data adapter classes to connect a data source (DB2 in this case) to a dataset, which is basically a copy of a table (or part of a table) in memory.  Data adapters allow you to pull data down from your data source into a data set on your local system, make changes to the local copy of the data (inserts, updates, deletes), and then push those changes back up to the data source.

I've usually seen data adapters used when you pull down a set of rows from the data source, change them in some way (add more rows, delete or update), and then push all the changes back to the data source at once.

If all you want to do is insert rows, there's an easier way.  For example, see the executeNonQuery example on page 72 of the Redbook.

Also, note that the examples in the redbook all use ADO.NET, and not ODBC.  ADO.NET is probably better from a .NET performance standpoint anyway, since you don't have to flip in and out of managed code like you do when using ODBC.

- Gary Patterson
ASKER CERTIFIED SOLUTION
vbcoder14

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gary Patterson, CISSP

Glad you got it resolved.

That's a fix for DB2 LUW, not DB2 for AS/400 / iSeries / IBM i, so I'm not sure how it applies, but it isn't surprising that the problem turned out to be a 32/64 bit issue.

- Gary
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
vbcoder14

There was never a problem, it turns out the exception came from a known bug in AS400.