Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Simple Insertion of 12 Digit Number into SQL Server2000

I am trying to insert 12 digit number into  sqlserver2000 table column. Error I get is "value is too small for Int32"   Column in Table is    :  Number Numeric(9) This cannot be changed though........

Here is my code

===CLIENT SIDE ==========
Dim obj As New CNumber
        Try
            Dim x As String = txtNumber.Text.Trim
            obj.Number = x
            obj.addNumber()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
==================================DLL CODE ========================================
Imports System
Imports System.Data.SqlClient

Public Class CNumber
    Private m_number As String

    Public Property Number() As String
        Get
            Return m_number
        End Get
        Set(ByVal Value As String)
            m_number = Value
        End Set
    End Property
    Public Function addNumber()
        Try
            Dim COpenDB as new CDB
            Dim mCommand As SqlClient.SqlCommand

            mCommand = New SqlClient.SqlCommand("sp_AddNumber", COpenDB.Openconnection())
            mCommand.CommandType = CommandType.StoredProcedure
            'Add Params to the Command collection
            With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .Add("@Number", SqlDbType.Int, 4)
            End With
            'Sepcify the parameters values to be passed to SP
            With mCommand
                .Parameters("@Number").Value = Convert.ToInt64(m_number)
            End With
                   With mCommand
                .Parameters("@Return_value").Direction = ParameterDirection.ReturnValue
                .Parameters("@Number").Direction = ParameterDirection.Input
            End With
            mCommand.ExecuteNonQuery()
            Dim Retval As Integer = mCommand.Parameters("@Return_value").Value
        Catch ex As Exception
            Throw ex
        End Try

    End Function
End Class
==========================Store dprocedure ============
create procedure sp_addNumber
@Number numeric(9)

as
begin
insert into addNumber (number) values (@Number)

  if @@error!=0
        begin
              return -100
        end
               else
                     begin
                          return -101
                     end

end

0
sainavya1215
Asked:
sainavya1215
3 Solutions
 
tomasX2Commented:
if it´s defined as numeric(9) then you might try to change the datatype for the @Number....


            With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .Add("@Number", System.Data.SqlDbType.Decimal, 5)
            End With
0
 
sainavya1215Author Commented:
   With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .Add("@Number", System.Data.SqlDbType.Decimal, 5)
            End With

I changed according to code given above. I get error "Error converting numeric to numeric"
0
 
gregasmCommented:
try using type of double



With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .Add("@Number", System.Data.SqlDbType.Double, 8)
            End With
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
sainavya1215Author Commented:
Does rest of the code remain the same
0
 
sainavya1215Author Commented:
There is no Sqldbtype.Double  when specifiying in stored procedure
0
 
123654789987Commented:
When u are adding parameter

Change
  With mCommand
                .Parameters("@Number").Value = Convert.ToInt64(m_number)
            End With


to

  With mCommand
                .Parameters("@Number").Value = Convert.ToInt32(m_number)
            End With

because the maximum limit of your column in database is 9 digits and Int32 supports upto 10 digits. U should have a check to see that the value of m_number is not more than 9 digits
0
 
monosodiumgCommented:
numeric(9) iuses 5 bytes and can store up to 10 decimal digits (5 x 2).  You simply can't store a 12 digit number in such a field.
Numeric stores a number as a series of decimal digits with 2 digits per byte (4 bits per digit).
0
 
gregasmCommented:
oh, its sqldbtype.float that one is a large numeric datatype
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now