Solved

Simple Insertion of 12 Digit Number into SQL Server2000

Posted on 2004-09-27
11
296 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
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
 

Author Comment

by:sainavya1215
ID: 12166673
Does rest of the code remain the same
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now