CODEWITHSUNDEEP
asp.netsql-server-2008-r2
Kumar Kush
Kumar Kush

Reputation: 2586

ASP.NET, SQL Server 2008 R2, System.OverflowException

I am trying to INSERT a record in an SQL Server 2008 R2 table using a Stored Procedure. I am "trying" to write a generic class for database access.

Here's the code in .VB file:

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    If PreviousPage IsNot Nothing AndAlso PreviousPage.IsCrossPagePostBack Then
        Dim params(5) As SqlParameter
        params(0) = New SqlParameter("@Name", PreviousPage.Name.Text)
        params(1) = New SqlParameter("@Qualification", PreviousPage.Qualification.Text)
        params(2) = New SqlParameter("@ContactNo", PreviousPage.ContactNo.Text)
        params(3) = New SqlParameter("@Expertise", PreviousPage.Expertise.Text)
        params(4) = New SqlParameter("@Comments", PreviousPage.Comments.Text)
        params(5) = New SqlParameter("@Affiliation", PreviousPage.Affiliation.Text)
        DAL.DoInsertSP(params)
    End If
End Sub

Here's the code for DAL.DoInsertSP():

Public Shared Function DoInsertSP(ByRef params As Array) As Byte
    Dim result As Byte = 0
    CreateConnection()
    cmd = New SqlCommand("sp_InsertExpert", conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddRange(params)
    Try
        result = CByte(cmd.ExecuteNonQuery())
    Catch ex As Exception
        conn.Dispose()
    End Try
    Return result
End Function

The code is throwing an System.OverflowException: Arithmetic operation resulted in an overflow.

Here's the script for the proc sp_InsertExpert

USE [dbherpes]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InsertExpert]
@Name nvarchar(30) = null,
@Qualification nvarchar(40) = null,
@ContactNo nvarchar(13) = null,
@Expertise nvarchar(30) = null,
@Comments nvarchar(100) = null,
@Affiliation nvarchar(50) = null AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Expert (Name, Qualification, ContactNo, Expertise, Comments, Affiliation) VALUES (@Name, @Qualification, @ContactNo, @Expertise, @Comments, @Affiliation)
END

Here's the structure of the table:

enter image description here

How can I correct this please?

UPDATE

I have set ID column's Identity Specification = 'Yes', Identity Increment = 1, Indentity Seed = 1

Upvotes: 0

Views: 387

Answers (1)

Nick Bork
Nick Bork

Reputation: 4841

Do you not have the line number in which the exception is occuring?

By looking at your code:

ExecuteNonQuery

returns an INT as per http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

I would suggest not converting the output to a byte but rather comparing cmd.ExecuteNonQuery() to a value, such as > 0

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Upvotes: 1

Related Questions