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?
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?
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Can you please post your code? Lot less guesswork for me.
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
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
ASKER
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
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
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
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
ASKER
There was never a problem, it turns out the exception came from a known bug in AS400.
Visual Basic.NET
Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,
96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
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!