Overflow operation when compiling using AnyCPU

Question

I have a legacy solution written in VB.NET. It writes to a DB2 database using ODBC. I have a Textbox that is filled with a numeric value then I use ToString in order to cast whatever is written there and write it to the database using ExecuteNonQuery since there the field is of CHAR(10) type, the problem that it yields Arithmetic operation resulted in an overflow when compiling using AnyCPU but it does not happen when compiling in 32 bits. What is causing this since I am using it as a String?

Edit:

Public Sub ExecuteTransaction(ByVal connectionString As String)

    Using connection As New OdbcConnection(connectionString)
        Dim command As New OdbcCommand()

        command.Connection = connection
        Try
            connection.Open()
            command.Connection = connection
            command.CommandText = "Update QS36f.table set Cat= 'F' where Num= '" & Me.txt2.ToString.Trim &"'"
            command.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Using
End Sub

"Please help me fix this code that I'm not going to show you". Do I really need to explain what's wrong with that? — John, May 14 '22 at 13:43
It is a proprietary code, I could be in trouble for that. But basically, it's textbox converted to String then used in an update statement, and it works when compiled in 32 bits, so no syntax errors. — Joe, May 14 '22 at 13:47
Even if you just include the few lines doing the conversion and the insert, that would help. You can change variable / table / proprietary names and you should be fine. Have you tried switching on Option Strict and Option Explicit? — Andrew Mortimer, May 14 '22 at 14:14
Don't use string concatenation to insert values into SQL code in the first place. I'd wager that, if you were doing it properly and using parameters, it would work as expected. — user18387401, May 15 '22 at 01:29

JorisJ1 · Accepted Answer · 2022-05-18T05:49:27.037

When I enter 12345 in the TextBox and then do the following:

Dim input As String = Me.txt2.ToString.Trim

The contents of input are not the desired "12345", but instead a string representation of the TextBox and its contents, namely: "System.Windows.Forms.TextBox, Text: 12345"

To get only the contents, use .Text:

Dim input As String = Me.txt2.Text.Trim

I would also try and convert it to an integer (assuming the desired input is an integer) right away before handing the input to the query:

Dim input As Integer
If Not Integer.TryParse(Me.txt2.Text, input) Then
    MessageBox.Show("Input could not be converted to integer")
    Exit Sub
End If

If that does not solve it then (as user18387401 mentioned) using parameters instead of string concatenation might help:

command.CommandText = "Update QS36f.table set Cat= 'F' where Num= @Num"

Dim param As New OdbcParameter("Num", OdbcType.VarChar)
param.Value = Me.txt2.Text.Trim
command.Parameters.Add(param)

EDIT: Don't use Parameters.AddWithValue.

I think we're trying to avoid `AddWithValue` these days: https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Andrew Mortimer, May 15 '22 at 12:04
So what's the best way to do it since I am inserting only numbers converted to a string into a DB2 that shows char(10). — Joe, May 15 '22 at 17:23
Thanks @AndrewMortimer I fixed it. Joe, using a parameter is definitely the way to go. — JorisJ1, May 18 '22 at 05:53

Overflow operation when compiling using AnyCPU

1 Answers1