Varchar to numeric error

[caolong]

Hi,

I have a textbox where I can enter a number. If the number exists in my sql database then it would change the label accordingly. It seems to work with any input number except for numbers starting with zero. If I input a number starting with zero(012345678) I get the error: Arithmetic Overflow error converting varchar to data type numeric. Please help. I need it to accept these kinds of numbers.

Imports System.Data.SqlClient
Public Class ValidateForm

    Private Sub MineralCloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MineralCloseButton.Click
        Me.Close()
    End Sub
    Private Sub CheckButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckButton.Click
        Dim ParcelNo As String
        ParcelNo = ParcelNumberTextBox.Text
        Try
            cnn = SQLDBConnect()
            cnn.Open()
        Catch ex As Exception
            MessageBox.Show(Me, "SQL Connection Failed: " + ex.Message, "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            errorlog(ex.ToString)
            Exit Sub
        End Try
        Try
            Dim sqlresult As Integer
            Dim command0 As SqlClient.SqlCommand = New SqlClient.SqlCommand("Select count(*) from nw_sum where parcel_number =" & ParcelNo, cnn)
            command0.CommandType = CommandType.Text
            command0.CommandTimeout = 600
            command0.ExecuteScalar()
            sqlresult = command0.ExecuteScalar
            If sqlresult = 0 Then
                Label1.Text = "Yes"
            Else
                Label1.Text = "No"
            End If
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        cnn.Close()
    End Sub
    'Error Log Subroutine to write errors into log files
    Public Sub errorlog(ByVal er)
        Dim FILE_NAME As String = "C:\Program Files\Noxious Weeds Report\ErrorLogs\NOXErrorlog.txt"
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)
            objWriter.Write(vbNewLine & System.DateTime.Now & er)
            objWriter.Close()
        Else
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME, False)
            objWriter.Write(vbNewLine & System.DateTime.Now & er)
            objWriter.Close()
        End If
    End Sub
End Class

[baja_yu]

Wrong section. This is VB Classic (VB6 and older), and your code is .Net

[caolong]

Hi,

I have a textbox where I can enter a number. If the number exists in my sql database then it would change the label accordingly. It seems to work with any input number except for numbers starting with zero. If I input a number starting with zero(012345678) I get the error: Arithmetic Overflow error converting varchar to data type numeric. Please help. I need it to accept these kinds of numbers.

Imports System.Data.SqlClient
Public Class ValidateForm

    Private Sub MineralCloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MineralCloseButton.Click
        Me.Close()
    End Sub
    Private Sub CheckButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckButton.Click
        Dim ParcelNo As String
        ParcelNo = ParcelNumberTextBox.Text
        Try
            cnn = SQLDBConnect()
            cnn.Open()
        Catch ex As Exception
            MessageBox.Show(Me, "SQL Connection Failed: " + ex.Message, "Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            errorlog(ex.ToString)
            Exit Sub
        End Try
        Try
            Dim sqlresult As Integer
            Dim command0 As SqlClient.SqlCommand = New SqlClient.SqlCommand("Select count(*) from nw_sum where parcel_number =" & ParcelNo, cnn)
            command0.CommandType = CommandType.Text
            command0.CommandTimeout = 600
            command0.ExecuteScalar()
            sqlresult = command0.ExecuteScalar
            If sqlresult = 0 Then
                Label1.Text = "Yes"
            Else
                Label1.Text = "No"
            End If
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        cnn.Close()
    End Sub
    'Error Log Subroutine to write errors into log files
    Public Sub errorlog(ByVal er)
        Dim FILE_NAME As String = "C:\Program Files\Noxious Weeds Report\ErrorLogs\NOXErrorlog.txt"
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)
            objWriter.Write(vbNewLine & System.DateTime.Now & er)
            objWriter.Close()
        Else
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME, False)
            objWriter.Write(vbNewLine & System.DateTime.Now & er)
            objWriter.Close()
        End If
    End Sub
End Class 
 Rate this post

[motil]

I can't see where you're trying to convert it to int. did you tried this:

sqlresult = Convert.ToInt32(command0.ExecuteScalar())

[Hack]

Moved To VB.NET

[caolong]

Hi,

Thanks for the reply. I am still getting that same error after trying your code suggestion.

[motil]

its looks like the number is overflow try this:

Dim sqlresult  As Long

sqlresult = Long.Parse(command0.ExecuteScalar())

[caolong]

Hi,

Thanks, but I am still getting the same error message.

[motil]

are you sure this "012345678" is the number that gives you the error?, did you set break point?

[motil]

ok i think i see where the problem, it's not within the VB code it's with your database column setting.

change the varchar column to bigger size.

i think this is the column you need to change "parcel_number" my guess is that its size is less the 9

btw, why is it defined as varchar? you want to keep the leading zeros? if it's not important change it to INT

[akhileshbc]

Maybe it's because of this line:

      Dim ParcelNo As String

What's the field type that you are using in your db for parcel_number ?

[caolong]

The column is varchar.

[caolong]

Hi,

I set my varchar column to 30, and I am still getting that same error, I did find out something strange. A number beginning with a zero only works if the number is 12 characters or more.

For example:

089470211000 works with no errors

08947021100 does not work

[techgnome]

Ummm.....
why are you executing the command TWICE?

            command0.ExecuteScalar()
            sqlresult = command0.ExecuteScalar

You should only be doing it once... and capturing the scalar result that one time.

            'command0.ExecuteScalar() 'This line is useless anyways since you're not capturing the result
            sqlresult = command0.ExecuteScalar()

Now... for your other problem... if the field is a string(text, varchar, what ever) then you need to treat your value as a string and not a number. The problem is that it thinks you want a num,ber so it's implicitly converting parcel_number fomr a varchar to a number and some entry in there somewhere is too big of an integer.

1) If it's text, treat it like text:
.SqlCommand("Select count(*) from nw_sum where parcel_number =" & ParcelNo, cnn)
should be
.SqlCommand("Select count(*) from nw_sum where parcel_number ='" & ParcelNo & "' ", cnn)
(note the extra tick marks
2) Use a parameter and not worry about it (this is the preferred and safe method
.SqlCommand("Select count(*) from nw_sum where parcel_number = @parecelNo", cnn)
.SqlCommand.Parameter.AddWithValue("@parecelNo", ParcelNo)
-- NOW execute the command

-tg

[ckennedy79]

        Try
            Dim sqlresult As Object
            Dim command0 As SqlClient.SqlCommand = New SqlClient.SqlCommand("Select count(*) from nw_sum where parcel_number =" & ParcelNo, cnn)
            command0.CommandType = CommandType.Text
            command0.CommandTimeout = 600
            sqlresult = command0.ExecuteScalar

            If (sqlresult IsNot Nothing) Then
                    If (IsNumeric(sqlresult) = True) Then
                           If CLng(sqlresult) = 0 Then
                               Label1.Text = "Yes"
                           Else
                               Label1.Text = "No"
                           End If
                    End If
             Else
                     Label1.Text = "No"
             End If
        Catch ex As Exception
            MsgBox(ex.ToString)

        End Try

What about something like this? I think you are having issues due to it trying to assume a conversion from your executescalar call. Also you only need to call executescalar once for this, not twice.

[ckennedy79]

http://www.vbforums.com/showthread.php?t=628163

[techgnome]

ckennedy - next time, instead of linking to a thread with no explanation like that... use the Report This Thread off the first post to report it as a duplicate... and give the link there... (I've gone ahead and taken care of that) ... all you did was confuse me for a moment as I thought you had simply linked back to this thread.

-tg

[si_the_geek]

Duplicate threads merged - please post each question (or variation of it) only once. If you think you have posted in the wrong forum, let a moderator know and we will move it.

(thanks for reporting it to us tg )