Solved

Simple Insertion of 12 Digit Number into SQL Server2000

Posted on 2004-09-27
11
316 Views
Last Modified: 2012-05-05
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
Comment
Question by:sainavya1215
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 5

Expert Comment

by:tomasX2
ID: 12164702
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
 

Author Comment

by:sainavya1215
ID: 12166539
   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
 
LVL 8

Expert Comment

by:gregasm
ID: 12166666
try using type of double



With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .Add("@Number", System.Data.SqlDbType.Double, 8)
            End With
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:sainavya1215
ID: 12166673
Does rest of the code remain the same
0
 

Author Comment

by:sainavya1215
ID: 12166758
There is no Sqldbtype.Double  when specifiying in stored procedure
0
 
LVL 10

Accepted Solution

by:
123654789987 earned 100 total points
ID: 12167487
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
 
LVL 12

Assisted Solution

by:monosodiumg
monosodiumg earned 100 total points
ID: 12168469
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
 
LVL 8

Assisted Solution

by:gregasm
gregasm earned 100 total points
ID: 12170686
oh, its sqldbtype.float that one is a large numeric datatype
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question