Solved

Simple Insertion of 12 Digit Number into SQL Server2000

Posted on 2004-09-27
11
310 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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