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?
vbcoder14Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
If you are concerned about your insert syntax, then display the value of your insert variable in debug (or just throw a msgbox temporarily).  

I'm not a VB.NET expert, but so I may just be missing something, but I usually see:

OdbcDataAdapter.SelectCommand used to set up an SQL SELECT command, not an INSERT.
OdbcDataAdapter.InsertCommand used to set up an SQL INSERT with parameter markers.
OdbcDataAdapter.InsertCommand.Parameters.Add to assign values to parameter markers

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdataadapter.insertcommand.aspx

and then use the Update method to execute the SQL Insert command.

If you then wanted to populate a dataset with data from the resulting table, you'd set up a SelectCommand (with the desired SQL SELECT), and then execute Fill.

I think you're getting a misleading message because you populated the SelectCommand property with an INSERT command and then tried to do a Fill.

- Gary Patterson
0
vbcoder14Author Commented:
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!
0
Gary PattersonVP Technology / Senior Consultant Commented:
Can you please post your code?  Lot less guesswork for me.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Gary PattersonVP Technology / Senior Consultant Commented:
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
0
vbcoder14Author Commented:
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
0
Gary PattersonVP Technology / Senior Consultant Commented:
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
0
vbcoder14Author Commented:
Gary,

Thanks for the help that you provided, however, we did find the solution and it turns out that the code as we had it in the beginning was working just fine. The problem was the following:

http://www-01.ibm.com/support/docview.wss?uid=swg1IZ09579

As soon as we switched everything to 32 bit, it never gave an error again.

It's never late to learn something new :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
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
0
vbcoder14Author Commented:
There was never a problem, it turns out the exception came from a known bug in AS400.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.