我尝试插入数据时出现问题：将数据类型nvarchar转换为数字时出错。 [英] Problem when I try to insert data: error in converting data type nvarchar to numeric.

问题描述

Dim cm As New SqlCommand
cm.Connection = con
cm.Connection.Open()
cm.CommandText = "Insert into tblsim ([sn],[dir],[alt],[plans],[rate],[addon],[cm]) values (@sn,@dir,@alt,@plans,@rate,@addon,@cm)"
If tssn.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@sn", SqlDataType.Numeric).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@sn", SqlDataType.Numeric).Value = tssn.Text
End If

If tsdir.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@dir", SqlDataType.Numeric).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@dir", SqlDataType.Numeric).Value = tsdir.Text
End If

If tsalt.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@alt", SqlDataType.Numeric).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@alt", SqlDataType.Numeric).Value = tsalt.Text
End If

If cmbplans.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@plans", SqlDataType.Numeric).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@plans", SqlDataType.Numeric).Value = cmbplans.Text
End If
If cmbrate.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@rate", SqlDataType.NVarChar).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@rate", SqlDataType.NVarChar).Value = cmbrate.Text
End If
If cmbaddon.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@addon", SqlDataType.NVarChar).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@addon", SqlDataType.NVarChar).Value = cmbaddon.Text
End If
If tscm.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@cm", SqlDataType.NVarChar).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@cm", SqlDataType.NVarChar).Value = tscm.Text
End If
cm.ExecuteNonQuery()
MsgBox("Record has been saved successfully", MsgBoxStyle.Information)
cm.Connection.Close()

CREATE TABLE [dbo].[tblsim](
	[simid] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[sn] [numeric](18, 0) NOT NULL,
	[dir] [numeric](18, 0) NOT NULL,
	[alt] [numeric](18, 0) NULL,
	[plans] [numeric](18, 0) NULL,
	[rate] [numeric](18, 0) NULL,
	[addon] [numeric](18, 0) NULL,
	[cm] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblsim_1] PRIMARY KEY CLUSTERED 
(
	[simid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

我尝试过：



我在尝试插入数据时遇到问题：将数据类型从NVarchar转换为数字时出错。



需要注意的是值是在'simid'列中插入的是20位长。例如28808399937872736628。

我也尝试通过将列的数据类型更改为'BigInt'和Nvarchar来插入，但它仍然不起作用！

问候：Shahid

What I have tried:

I'm facing problem when I try to insert data: Error in converting data type from NVarchar to Numeric.

To be noted that value to be inserted in column 'simid' is 20 digit long. eg 28808399937872736628.
I also tried to insert by changing the column's data type to 'BigInt' and Nvarchar but still it does not work!
Regards: Shahid

推荐答案

If cmbrate.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@rate", SqlDataType.NVarChar).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@rate", SqlDataType.NVarChar).Value = cmbrate.Text

这是你的问题。



您已将速率定义为数字（

here is your problem.

You've defined rate as numeric(

[rate] [numeric](18, 0) NULL,

）但是在这段代码中你使用了

) but in this code you've used

SqlDataType.NVarChar

而不是SqlDataType.Numeric



另请注意您已定义[addon] ] [数字]（18,0）NULL，

所以同样的错误发生在这里

instead of SqlDataType.Numeric

Also notice you've defined [addon] [numeric](18, 0) NULL,
so same mistake occurs here

If cmbaddon.Text.Trim.Length = 0 Then
    cm.Parameters.AddWithValue("@addon", SqlDataType.NVarChar).Value = DBNull.Value
Else
    cm.Parameters.AddWithValue("@addon", SqlDataType.NVarChar).Value = cmbaddon.Text
End If

修复它。我认为所有代码都是正确的

fix it . I think all code will go correct

您可以解析从数据库获取的字符串，将其解释为 System.Numerics.BigInteger ：

BigInteger.Parse方法（字符串） ）（System.Numerics）。

-SA

You can parse string obtained from the database to interpret it as the instance of System.Numerics.BigInteger:
BigInteger.Parse Method (String) (System.Numerics).

—SA

这篇关于我尝试插入数据时出现问题：将数据类型nvarchar转换为数字时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！