Knowledge Base

SqlClient Does Not Check Validity of Decimal Value That Is Passed as Parameter

Article ID: 318609

Article Last Modified on 10/11/2005

APPLIES TO

Microsoft ADO.NET 1.0

This article was previously published under Q318609

SYMPTOMS

When you use the System.Data.SqlClient.SqlCommand object, if you use a SqlParameter field of type SqlDbType.Decimal to perform a parameterized insert statement, no error is reported if the inserted number is greater than the bounds of the precision or the scale of the target column.

CAUSE

Microsoft SQL Server 2000 does not check the precision or the scale of an incoming decimal value that is inserted into a table when the insert statement comes from the System.Data.SqlClient.SqlCommand class. The System.Data.SqlClient class should verify that the number does not exceed the precision or the scale of the target field before it tries a parameterized insert. The SqlCommand object does not check the validity of the data that is passed to SQL Server.

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next Microsoft Visual Studio .NET that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date        Version       Size       File Name         Platform
   ---------------------------------------------------------------
   5-Mar-2002  1.0.3705.211  1,175,552  System.data.dll   x86

WORKAROUND

To work around this problem, use one of the following methods:

Change the data type of the SqlParameter field from SqlDbType.Decimal to SqlDbType.Float.
Set up a constraint on the column so that it does not allow data that exceeds the precision or the scale of the target column.
Prevalidate the data before you assign it to the SqlParameter value.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

In SQL Server 2000, use Query Analyzer to create the following table:

USE PUBS
GO
CREATE TABLE [dbo].[myTable] (
    [c1] [int] NOT NULL ,
    [c2] [decimal](5, 2) NULL 
) ON [PRIMARY]
GO

Run the following Visual C# code, and then notice that no error is reported:

using System;
using System.Data;
using System.Data.SqlClient;

class ReproClass1
{
  const string CONNECT = 
                 "Data Source=MySQLServer;" + 
                 "Integrated Security=SSPI;" + 
                 "Initial Catalog=Pubs;"; 
  static void Main(string[] args)
  {
    SqlConnection conn;
    SqlCommand cmd;
    SqlParameter p1, p2;
    try
    {
      conn = new SqlConnection(CONNECT);
      conn.Open();        
      cmd  = new SqlCommand();
      cmd.CommandText = "insert into myTable values (@pk, @decval)";
      cmd.Connection = conn;
      
      // Set up parameter for your primary key column.
      p1 = new SqlParameter();
      p1.ParameterName = "@pk";
      p1.SqlDbType = SqlDbType.Int;
      p1.Direction= ParameterDirection.Input;
      p1.Value = 2;
      
      // Set up parameter for your decimal column.
      p2 = new SqlParameter();
      p2.ParameterName = "@decval";
      p2.SqlDbType = SqlDbType.Decimal;
      p2.Direction= ParameterDirection.Input;
      p2.Precision= 5;
      p2.Scale = 2;
      p2.Value = 9999.99;
      
      // Add parameters, and then execute the query.
      cmd.Parameters.Add(p1);
      cmd.Parameters.Add(p2);     
      cmd.ExecuteNonQuery();
      Console.WriteLine( "9999.99 inserted without error." );
    }
    catch(Exception e)
    {
      Console.WriteLine(e.ToString());
    }       
  }
}

Keywords: kbbug kbfix kbqfe kbhotfixserver KB318609

Microsoft KB Archive/318609

Contents