0

I'm working in asp.net with vb.net and in the backend I'm trying to select something from the database.

But whenever I ask to execute the query it gives an error which says 'OverflowException Ocuured'. The query that is made works perfectly in my SQL Manager tool. Any ideas what can be the problem.

(it gives the problem on the line under 'try' so the 'returnedId = com.ExecuteScalar' line)

 Function selectEIDCardnumber(ByVal name As String) As Integer
        Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("testdatabase").ConnectionString)
        Dim selecter As String = "SELECT EIDCardNumber FROM [dbo].[_User] where DisplayName = @name"
        Dim com As New SqlCommand(selecter, con)
        com.Parameters.AddWithValue("@name", name)
        con.Open()
        Dim returnedId As Integer = 0
        Try
            returnedId = com.ExecuteScalar
        Catch ex As Exception
            Response.Redirect("oops.aspx")
        End Try

        con.Close()

        Return returnedId
    End Function
Benno
  • 2,534
  • 1
  • 17
  • 27
Manuel van den Notelaer
  • 55
  • 9
  • 1
    Is your return column EIDCardNumber is INT ? – Ismail Adar Nov 29 '17 at 13:33
  • And in Sql Server Management Studio what is the value returned for the same @name parameter that fails in code? Is this value convertible to an Int32? – Steve Nov 29 '17 at 13:39
  • I changed returnId to String instead of int and also changed in in the database to datatype varchar. And now the error doesn't happen on the execute line but on the retun line. Exactly the same error. Any ideas? – Manuel van den Notelaer Nov 29 '17 at 15:39
  • Your function is still returning integer. VB will do a implicit conversion from string to integer when returning the value. – Robert Nov 29 '17 at 22:31
  • @Robert so how can I fix this the easiest way? Do a ParseInt before I return it? – Manuel van den Notelaer Nov 30 '17 at 08:21
  • Try to change the return type of your function from integer to string. I'm assuming your EIDCardNumber is used as ID. e.g. you do not need to perform any arithmetic operation on it. – Robert Nov 30 '17 at 20:18

3 Answers3

0

The result of com.ExecuteScalar is larger than the max int value.

Int32.TryParse(com.ExecuteScalar, returnedId)
Simon De Boeck
  • 3
  • 2
0

By defining your returnedId and Function As type Integer you are telling VB that the value will be a whole number between -2,147,483,648 and 2,147,483,647. Assuming the data in your database is correct, the solution is to change the returnedId and Function types to Int64, which holds numbers up to 9,223,372,036,854,775,807.

GMan80013
  • 536
  • 4
  • 13
0

If you're sure that the EIDCardNumber column is an integer type column then try changing Dim returnedId As Integer = 0 into Dim returnedId As Long = 0 and if the problem persists try changing the value of selecter to "SELECT top 1 EIDCardNumber FROM [dbo].[_User] where DisplayName = @name"

Emilio Lucas Ceroleni
  • 1,559
  • 2
  • 9
  • 13