Cannot convert datatype nvarchar to numeric

Question

I import data from from an Excel file into a SQL Server database with the following query. The Excel file has all values as string types (' before every cells).

I get this error when I import it."Cannot convert datatype nvarchar to numeric"

If I remove the two columns SalePrice and Price2 from importing, then the import is successful.

The datatypes of my table is

CREATE TYPE [dbo].[InventoryType] AS TABLE
(
   [LocalSKU] [varchar](200) NOT NULL,
   [ItemName] [varchar](200) NULL,
   [QOH] [int] NULL,
   [Price] [decimal](19, 4) NULL,
   [Discontinued] [bit] NULL,
   [Barcode] [varchar](25) NULL,
   [Integer2] [int] NULL,
   [Integer3] [int] NULL,
   [SalePrice] [decimal](19, 4) NULL,
   [SaleOn] [bit] NULL,
   [Price2] [decimal](19, 4) NULL
)
GO

The query I am using is:

SqlCommand sqlcmd = new SqlCommand
   (@"MERGE Inventory AS target
      USING (SELECT
                LocalSKU, ItemName, QOH, Price, Discontinued, 
                Barcode, Integer2, Integer3, SalePrice, SaleOn, Price2 
             FROM @source) AS Source ON (Source.LocalSKU = target.LocalSKU)
      WHEN MATCHED THEN
         UPDATE 
           SET ItemName = source.ItemName,
               Price = source.Price,
               Discontinued = source.Discontinued,
               Barcode = source.Barcode,
               Integer2 = source.Integer2,
               Integer3 = source.QOH,
               SalePrice = source.SalePrice,
               SaleOn = source.SaleOn,
               Price2 = source.Price2;", sqlconn);

SqlParameter param;
param = sqlcmd.Parameters.AddWithValue("@source", dr);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.InventoryType";

sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();

That statement isn't inserting anything - it's only updating existing rows ..... also: you didn't post the actual **error** that you're getting! — marc_s, Apr 08 '14 at 20:34
@marc_s I have updated the question. Yes it is only updating. — user2525244, Apr 08 '14 at 20:53
Is the type of column `QOH` convertible into a numeric type? — David R Tribble, Apr 08 '14 at 21:10
@DavidRTribble No. I have lot of dependencies. I was working on this and I found that if I remove the two columns SalePrice and Price2 , then the import is successful. — user2525244, Apr 08 '14 at 22:08
One thing to keep in mind is that you may have some invalid data in the given column that's throwing the error(s). For example if you have a Numeric column and have letters or inproperly formatted numeric values, this error will be thrown. Check for values in your cells that aren't correct first. This includes trailing or leading spaces as well. — Techie Joe, Apr 08 '14 at 23:39
I'll put it as an answer then and then you can give it a check if you like it. — Techie Joe, Apr 08 '14 at 23:46

score 0 · Answer 1 · answered Apr 08 '14 at 23:46

One thing to keep in mind is that you may have some invalid data in the given column that's throwing the error(s). For example if you have a Numeric column and have letters or inproperly formatted numeric values, this error will be thrown. Check for values in your cells that aren't correct first. This includes trailing or leading spaces as well.

Cannot convert datatype nvarchar to numeric

1 Answers1