Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple Insertion of 12 Digit Number into SQL Server2000

Posted on 2004-09-27
11
Medium Priority
?
333 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 400 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 400 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 400 total points
ID: 12170686
oh, its sqldbtype.float that one is a large numeric datatype
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 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