ASP.NET Sql insert Error converting data type nvarchar to numeric

Question

Entity class which object I want to add to DB:

public class Item
{
    [Key]
    public long Id { get; set; }

    [MaxLength(100)]
    [Required]
    public string Name { get; set; }

    [MaxLength(1000)]
    public string Description { get; set; }

    [Required]
    public decimal Price { get; set; }

    public Currency Currency { get; set; }

    public bool Negotiable { get; set; }

    public virtual List<Photo> Photos { get; set; }
    public Category Category { get; set; }

    [Required]
    public byte CategoryId { get; set; }

    [Required]
    public string OwnerId { get; set; }

    public ApplicationUser Owner { get; set; }
}

It's local Db, generated with code-first approach. And now I want to add a row in my application using Sql Command:

  public void AddItem(Item item)
    {

        var cnnString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        var cmd = "INSERT INTO Items([Id],[Name],[Description] ,[Price] , [Currency] ,  [Negotiable], [CategoryId], [OwnerId] ) VALUES(@id,@name,@description,@price,@currency,@negotiable,@categoryId,@ownerId)";

        using (SqlConnection cnn = new SqlConnection(cnnString))
        {

            using (SqlCommand cmd1 = new SqlCommand(cmd, cnn))
            {
                cnn.Open();
                cmd1.CommandText = "SELECT MAX(Id) FROM Items";
                using (SqlDataReader reader = cmd1.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int maxId = Convert.ToInt32(cmd1.ExecuteScalar());
                        item.Id = maxId;
                    }
                }

                cmd1.Parameters.AddWithValue("@id", item.Id);
                cmd1.Parameters.AddWithValue("@name", item.Name);
                cmd1.Parameters.AddWithValue("@description", item.Description);
                cmd1.Parameters.Add("@price", SqlDbType.Decimal).Value = item.Price;
                cmd1.Parameters.AddWithValue("@currency", item.Currency);
                cmd1.Parameters.AddWithValue("@negotiable", item.Negotiable);
                cmd1.Parameters.AddWithValue("@categoryId", item.CategoryId);
                cmd1.Parameters.AddWithValue("@ownerId", item.OwnerId);



                cmd1.ExecuteNonQuery();  //*
            }
        }

    }

EDITED: This code above throws exception: here is already an open DataReader associated with this Command which must be closed first.""

DB Schema in T-SQL is defined like below:

CREATE TABLE [dbo].[Items] (
[Id]          BIGINT          IDENTITY (1, 1) NOT NULL,
[Name]        NVARCHAR (100)  NOT NULL,
[Description] NVARCHAR (1000) NULL,
[Price]       DECIMAL (18, 2) NOT NULL,
[Currency]    INT             NOT NULL,
[Negotiable]  BIT             NOT NULL,
[CategoryId]  TINYINT         NOT NULL,
[Category_Id] INT             NULL,
[OwnerId]     NVARCHAR (128)  DEFAULT ('') NOT NULL,
CONSTRAINT [PK_dbo.Items] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.Items_dbo.Categories_Category_Id] FOREIGN KEY ([Category_Id]) REFERENCES [dbo].[Categories] ([Id]),
CONSTRAINT [FK_dbo.Items_dbo.AspNetUsers_OwnerId] FOREIGN KEY ([OwnerId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

);

Hi Can you please hit debugger and see if item.Currency , item.CategoryId ,item.OwnerId values are numeric or not .Or the best solution is to check SQL server profiler . The problem is in the values while adding in Parameters. — Yashveer Singh
– Yashveer Singh, Commented Jan 15, 2017 at 15:52
Via debugger i checked of course, it was first thing that I did, they have properly values assigned. OwnerId is string, not numeric, like in my class definition (string OwnerId) — mike_pl
– mike_pl, Commented Jan 15, 2017 at 15:56
yes but this error can be for any other column which is of type int — Yashveer Singh
– Yashveer Singh, Commented Jan 15, 2017 at 16:00

Yashveer Singh · Accepted Answer · 2017-01-15 19:50:38Z

1

change you insert query to this . and try now

            public long AddItem(Item item) 
        { 

        var cnnString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; 
        var cmdInsert = "INSERT INTO Items([Name],[Description] ,[Price] , [Currency] , [Negotiable], [CategoryId], [OwnerId] ) VALUES(@name,@description,@price,@currency,@negotiable,@categoryId,@ownerId)"; 


        using (SqlConnection cnn = new SqlConnection(cnnString)) 
        { 
        cnn.Open(); 
        using (SqlCommand cmd1 = new SqlCommand(cmdInsert, cnn)) 
        { 

        cmd1.Parameters.AddWithValue("@name", item.Name); 
        cmd1.Parameters.AddWithValue("@description", item.Description); 
        cmd1.Parameters.Add("@price", SqlDbType.Decimal).Value = item.Price; 
        cmd1.Parameters.AddWithValue("@currency", item.Currency); 
        cmd1.Parameters.AddWithValue("@negotiable", item.Negotiable); 
        cmd1.Parameters.AddWithValue("@categoryId", item.CategoryId); 
        cmd1.Parameters.AddWithValue("@ownerId", item.OwnerId); 

        cmd1.ExecuteNonQuery(); 
        } 
        cnn.Close(); 
        } 

        }

edited Jan 15, 2017 at 19:50

answered Jan 15, 2017 at 15:44

Yashveer Singh

2,0072 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Yashveer Singh Over a year ago

If you can show me profiler output I can find the issue.

mike_pl Over a year ago

how to do that, can I in Visual Studio?

Yashveer Singh Over a year ago

ok you have Local SQL server installed on your system . If yes then open sql server management studio -> Tools ->SQL Server Profiler . Open it it will ask you to connect to server choose you local server and run it . After that you run the code . you will see all the query that come from your application in the log . you just need to find your insert query

mike_pl Over a year ago

sorry, for too late response, but the database that i can see in SQL Server Object Explorer is not visible via sql management studio...

Yashveer Singh Over a year ago

type the name manually or i can help if i can see your system using remote desktop . have you tried my answer i updated it

|

Mixim · Accepted Answer · 2017-01-15 15:59:29Z

0

Let's look to your code:

    var cmd = "INSERT INTO Items VALUES(@id,
@name,
@description,
@price,
@currency,    
@negotiable,    
@categoryId,    
@ownerId)";

and table definition:

CREATE TABLE [dbo].[Items] (
[Id]          BIGINT          IDENTITY (1, 1) NOT NULL,
[Name]        NVARCHAR (100)  NOT NULL,
[Description] NVARCHAR (1000) NULL,
[Price]       DECIMAL (18, 2) NOT NULL,
[Currency]    INT             NOT NULL,
[Negotiable]  BIT             NOT NULL,
[CategoryId]  TINYINT         NOT NULL,
[Category_Id] INT             NULL,
[OwnerId]     NVARCHAR (128)  DEFAULT ('') NOT NULL,
CONSTRAINT [PK_dbo.Items] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.Items_dbo.Categories_Category_Id] FOREIGN KEY ([Category_Id]) REFERENCES [dbo].[Categories] ([Id]),
CONSTRAINT [FK_dbo.Items_dbo.AspNetUsers_OwnerId] FOREIGN KEY ([OwnerId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE)

You have CategoryId, Category_Id, OwnerId in table definition and just @categoryId, @ownerId in C#. You forgot about @category_Id value, I think

answered Jan 15, 2017 at 15:59

Mixim

9722 gold badges12 silver badges38 bronze badges

2 Comments

mike_pl Over a year ago

Category_Id can be null in there, so that's doesn't matter

canon Over a year ago

@mike_pl if you're omitting columns, you should specify which columns you're targeting.

Collectives™ on Stack Overflow

ASP.NET Sql insert Error converting data type nvarchar to numeric

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related