1

I am trying to insert product details into my product table to be stored and later on selected from to display

I have already tried

cmd.Parameters.AddWithValue("ProdPrice", Convert.ToInt32(txtProdPrice.Text));

cmd.Parameters.AddWithValue("ProdPrice", decimal.Parse(txtProdPrice.Text));

cmd.Parameters.AddWithValue("prodStock", int.Parse(txtProdStock.Text));

product table

CREATE TABLE [dbo].[Product] (
    [ProdID]    INT             IDENTITY (1, 1) NOT NULL,
    [ProdName]  NVARCHAR (50)   NOT NULL,
    [ProdPrice] DECIMAL (18, 2) NULL,
    [ProdDesc]  NVARCHAR (MAX)  NULL,
    [ImageName] NVARCHAR (255)  NULL,
    [Size]      INT             NULL,
    [ImageData] VARBINARY (MAX) NULL,
    [ProdBrand] NVARCHAR (50)   NULL,
    [ProdStock] INT             NULL,
    PRIMARY KEY CLUSTERED ([ProdID] ASC)

.ASPX.CS Codes

string cs = ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("insert into Product values (@ProdName, @ProdDesc, @ProdPrice, @ImageName, @Size, @ImageData, @ProdBrand, @ProdStock);", con);
                cmd.Parameters.AddWithValue("ProdName", txtProdName.Text);
                cmd.Parameters.AddWithValue("ProdDesc", txtProdDesc.Text);
                cmd.Parameters.AddWithValue("ProdPrice", decimal.Parse(txtProdPrice.Text));
                cmd.Parameters.AddWithValue("ImageName", fileName);
                cmd.Parameters.AddWithValue("Size", fileSize);
                cmd.Parameters.AddWithValue("ImageData", bytes);
                cmd.Parameters.AddWithValue("ProdBrand", ddlBrand.SelectedItem.Text);
                cmd.Parameters.AddWithValue("prodStock", int.Parse(txtProdStock.Text));


                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                lblImageResult.Visible = true;
                lblImageResult.Text = "Upload Successful";
                lblImageResult.ForeColor = System.Drawing.Color.Green;

All results are still the same, "error converting datatype nvarchar to numeric"

3
  • As Laurent Lequenne said, you should specify the columns names in your insert statement SqlCommand cmd = new SqlCommand("insert into Product (ProdName, ProdDesc, ProdPrice, ...) values (@ProdName, @ProdDesc, @ProdPrice, @ImageName, @Size, @ImageData, @ProdBrand, @ProdStock);", con); Here you are trying to insert the ProdName in the ProdId field Commented Jul 9, 2019 at 9:10
  • ProdID is set on auto-increment, it shouldn't be affected by my insert values. Commented Jul 9, 2019 at 9:15
  • If you don't specify the fields in your insert query INSERT INTO TABLE (Field1, Field2 ... FiledN) VALUES (Value1, Value2 ... ValueN) else sql server will populate them in the order they where defined while creating the table Commented Jul 9, 2019 at 9:21

3 Answers 3

5

If you don't specify the fields in the insert statement, the values provided should be in the column order of the table.

... insert into Product values (@ProdName, @ProdPrice, @ProdDesc, ....
Sign up to request clarification or add additional context in comments.

1 Comment

Just invert ProdPrice and ProdDesc in your code. Easy
0

As has already been mentioned, when you are not specifying the column names on your insert then the parameters are assumed to be in the same order as the table columns. In your case the ProdDesc and ProdPrice parameters are in a different order to their respective table columns, hence the error.

Either re-order those parameters, or even better specify the columns on the query:

SqlCommand cmd = new SqlCommand("insert into Product (ProdName, ProdDesc, ProdPrice, ImageName, Size, ImageData, ProdBrand, ProdStock) values (@ProdName, @ProdDesc, @ProdPrice, @ImageName, @Size, @ImageData, @ProdBrand, @ProdStock);", con);

Additionally, consider avoiding Parameters.AddWithValue

Comments

-1

ProdID is identity column & didn't specified column field name that's why it's trying to insert ProdID column but ProdID is INT value.

SqlCommand cmd = new SqlCommand("insert into Product (ProdName,ProdDesc,ProdPrice,ImageName,Size,ImageData,ProdBrand,ProdStock) values (@ProdName, @ProdDesc, @ProdPrice, @ImageName, @Size, @ImageData, @ProdBrand, @ProdStock);", con)

3 Comments

Welcome to SO. When submitting an answer, please add some explanation as to why this solves the problem. At first sight it is not obvious why your answer should deal with the nvarchar/numeric error that was stated in the question.
... and you should format code as code by adding 4 spaces before it ...
ProdID is identity column & didn't specified column field name that's why it's trying to insert ProdID column but ProdID is INT value. and thank you for your feedback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.