Problem when I try to insert data: error in converting data type nvarchar to numeric.

Question

1.00/5 (1 vote)

See more:

VB

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]

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

Posted 15-Jun-16 13:22pm

Member 11765123

Updated 16-Jun-16 14:09pm

Add a Solution

Comments

Sergey Alexandrovich Kryukov 16-Jun-16 1:40am

Don't use nvarchar for storing numeric data. Use appropriate data types. However, yes, you may need it for big precise numbers.
What is "BigInt"? there is the type System.Numerics.BigInteger. It just works.
—SA

Member 11765123 16-Jun-16 19:13pm

thank you!! but how do I code 'System.Numerics.BigInteger' in vb.net when I'm declaring sql data type as follows:
cm.Parameters.AddWithValue("@sn", SqlDataType.Numeric).Value = tssn.Text

Sergey Alexandrovich Kryukov 16-Jun-16 20:11pm

I answered in Solution 3.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anisuzzaman Sumon · Accepted Answer · 2016-06-15T19:40:00

C#

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(

C#

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

) but in this code you've used

SQL

SqlDataType.NVarChar

instead of SqlDataType.Numeric

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

C#

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

Sergey Alexandrovich Kryukov · Accepted Answer · 2016-06-16T14:09:00

Solution 3

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

Posted 16-Jun-16 14:09pm

Sergey Alexandrovich Kryukov

Comments

Member 11765123 17-Jun-16 22:37pm

thank you so much!!

Sergey Alexandrovich Kryukov 18-Jun-16 2:39am

You are very welcome.
Good luck, call again.
—SA