从数字数据类型转换为十进制的错误。

时间:2021-11-06 01:33:14

Well here is my problem i have a table that contains the following column:

这是我的问题,我有一个包含以下列的表格

asis_doc_minutos decimal(4,1)

If i insert the following number (1.5) with a simple insert query it inserts it! BUT my SProcedure doesn't inserts the data. Here is my Sp:

如果我用一个简单的插入查询插入以下数字(1.5),它会插入它!但是SProcedure不会插入数据。这是我的Sp:

 ALTER procedure [dbo].[insertar_pla_asis_doc]
  @fecha datetime,
  @tema varchar(500),
  @minutos decimal(2,2),
  @cod_docente int,
  @cod_materia varchar(25),
  @cod_seccion int,
  @cod_ciclo int,
  @cod_escuela int
  as
  begin
 insert into pla_asis_doc(asis_doc_fecha, asis_doc_tema,
 asis_doc_fecha_realizo, asis_doc_cod_docente, asis_doc_cod_mat, asis_doc_seccion,
     asis_doc_cod_ciclo, 
 asis_doc_cod_escuela, asis_doc_minutos)
 values (@fecha, @tema, getdate(), @cod_docente, @cod_materia, @cod_seccion,
 @cod_ciclo, @cod_escuela, @minutos);
   end

I'm calling this Sp from C# and this is the way i do it:

我将c#中的Sp命名为Sp,我就是这么做的:

 int valor_devuelto = puente.insertar(fecha, txtTema.Text,
 Convert.ToDecimal(lblHoras.Text), cod_emp, cod_materia, seccion,
 Convert.ToInt32(ciclo.Text), cod_esc);

Notice that (lblHoras.Text) is called from a TextBox and so i convert it to Decimal becasue that's what my column expects. That lines calls to a method in another class, which is the one that calls my Sp in my db and where i got the error, here is that code:

注意(lblHoras.Text)是从文本框中调用的,因此我将它转换为Decimal,因为这是我的列所期望的。这条线调用另一个类中的方法,也就是在db中调用我的Sp的方法,在这里我得到了错误,这里是代码:

public int insertar(DateTime fecha, string tema, decimal minutos, int docente, string materia_codigo, int seccion, int ciclo, int escuela_cod)
{
    int valor = 0;
    try
    {
        cn.Open();
        cmd.Connection = cn;                
        cmd.CommandText = "insertar_pla_asis_doc";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("@fecha", System.Data.SqlDbType.DateTime).Value = fecha;
        cmd.Parameters.Add("@tema", System.Data.SqlDbType.VarChar).Value = tema;
        cmd.Parameters.Add("@minutos", System.Data.SqlDbType.Decimal).Value = minutos;
        cmd.Parameters.Add("@cod_docente", System.Data.SqlDbType.Int).Value = docente;
        cmd.Parameters.Add("@cod_materia", System.Data.SqlDbType.VarChar).Value = 
        materia_codigo;
        cmd.Parameters.Add("@cod_seccion", System.Data.SqlDbType.Int).Value = seccion;
        cmd.Parameters.Add("@cod_ciclo", System.Data.SqlDbType.Int).Value = ciclo;
        cmd.Parameters.Add("@cod_escuela", System.Data.SqlDbType.Int).Value = 
        escuela_cod;
        valor = cmd.ExecuteNonQuery();
        cn.Close();
    }
    catch (Exception ex)
    {
    }
    return valor;
}

Everytime i run this code there's an error saying: Error al convertir el tipo de datos numeric a decimal. (Error converting ) How can i solve that?

每次我运行这段代码时都会出现一个错误:error al convertir el tipo de datos数值a decimal。(错误转换)我如何解决这个问题?

2 个解决方案

#1


3  

Your parameter in the procedure is declared as

过程中的参数被声明为

@minutos decimal(2,2)

It means two numbers, both right of decimal point.

它表示两个数,都是小数点右边的数。

Check this sample:

检查这个示例:

create table #t (d decimal(2,2))
insert #t values (1.5)

Increase the size of the parameter.

增加参数的大小。

#2


2  

The answer from @dean above was not very accurate.

上面@dean的回答不太准确。

It will failed as @steve mentioned.

正如@steve提到的那样,它将失败。

create table #t (d decimal(2,2)) insert #t values (1.5)

创建表#t (d decimal(2,2))插入#t值(1.5)

the result will be: "Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated."

结果将是:“Msg 8115,第16级,状态8,第1行算术溢出错误将数值转换为数据类型数值。声明已被终止。

Correction:

更正:

create table #t (d decimal(3,2)) insert #t values (1.5)

创建表#t (d decimal(3,2))插入#t值(1.5)

Please see explanation below:

请见下面的解释:


Decimal and numeric arguments:

小数和数值参数:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

十进制[(p[, s])]和数字[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal. p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18. s (scale)

固定精度和刻度。当使用最大精度,有效值是38 + 1到10 - 10 ^ ^ 38 - 1。十进制的同义词是dec和dec(p, s)。数值在功能上等同于十进制。p(精度)将被存储的十进制数的最大总数,小数点后的左边和右边。精度必须为从1到最大精度为38的值。默认的精度是18。s(规模)

The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

将存储在小数点右边的小数位数。这个数字被从p减去,以确定小数点左边的最大位数。可以存储在小数点右边的最大小数位数。标度必须是0到p的值。只有指定了精度,才可以指定标度。默认比例为0;因此,根据精度的不同,0 <= s <= p.最大存储容量也不同。

#1


3  

Your parameter in the procedure is declared as

过程中的参数被声明为

@minutos decimal(2,2)

It means two numbers, both right of decimal point.

它表示两个数,都是小数点右边的数。

Check this sample:

检查这个示例:

create table #t (d decimal(2,2))
insert #t values (1.5)

Increase the size of the parameter.

增加参数的大小。

#2


2  

The answer from @dean above was not very accurate.

上面@dean的回答不太准确。

It will failed as @steve mentioned.

正如@steve提到的那样,它将失败。

create table #t (d decimal(2,2)) insert #t values (1.5)

创建表#t (d decimal(2,2))插入#t值(1.5)

the result will be: "Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated."

结果将是:“Msg 8115,第16级,状态8,第1行算术溢出错误将数值转换为数据类型数值。声明已被终止。

Correction:

更正:

create table #t (d decimal(3,2)) insert #t values (1.5)

创建表#t (d decimal(3,2))插入#t值(1.5)

Please see explanation below:

请见下面的解释:


Decimal and numeric arguments:

小数和数值参数:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

十进制[(p[, s])]和数字[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal. p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18. s (scale)

固定精度和刻度。当使用最大精度,有效值是38 + 1到10 - 10 ^ ^ 38 - 1。十进制的同义词是dec和dec(p, s)。数值在功能上等同于十进制。p(精度)将被存储的十进制数的最大总数,小数点后的左边和右边。精度必须为从1到最大精度为38的值。默认的精度是18。s(规模)

The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

将存储在小数点右边的小数位数。这个数字被从p减去,以确定小数点左边的最大位数。可以存储在小数点右边的最大小数位数。标度必须是0到p的值。只有指定了精度,才可以指定标度。默认比例为0;因此,根据精度的不同,0 <= s <= p.最大存储容量也不同。