sqlsql-serverdecimalssms

Save decimal values in SQL Server Management Studio


I am looking to save decimal values in SQL Server Management Studio.

They just come out as integer values rounded to nearest value.

How is this done? If any more information is needed I can get it.

Columns:

ProductCode int Unchecked
ProductName text    Unchecked
SupplierCode    nvarchar(50)    Unchecked
Cost    decimal(5, 2)   Unchecked
Quantity    int Unchecked
RetailPrice decimal(5, 2)   Unchecked

Table output

ProductCode ProductName SupplierCode    Cost    RetailPrice Quantity
--------------------------------------------------------------------
1              Milk       SW11           0         1          12

Here is the SQL code to add it to the database:

SqlCommand addProduct = new SqlCommand("INSERT INTO dbo.NCAProduct  VALUES(" + txtProductCode.Text + ", '" + txtProductName.Text + "', '" + txtSupplierCode.Text + "', " + txtCost.Text + ", " + txtRetail.Text + ", " + txtQuantity.Text + ");", sqlConnect);

Solution

  • Several issues here:

    1. You need to change the datatype of the [ProductName] field to VARCHAR(50) or some other number if 50 is too few characters for a name. But the text datatype was deprecated when SQL Server 2005 came out and even if you are still on SQL Server 2000, I highly doubt you need more than 8000 characters for a product name ;-). The replacement for TEXT is VARCHAR(MAX), for when a situation actually needs more than 8000 characters.

    2. You need to parameterize your query because in the current form, your code is susceptible to SQL Injection.

    3. You cannot save decimals in SQL Server Management Studio (SSMS). You can save queries and you can save results, but SSMS is an IDE. You are probably meaning that you cannot save the decimal values to SQL Server using .NET.

    4. While not required by an INSERT statement, specifying the column list is always a good idea because the order of the columns might not be what you expect, and if you add columns then this INSERT will fail. Meaning, instead of:

      INSERT INTO dbo.NCAProduct VALUES(...);
      

      do:

      INSERT INTO dbo.NCAProduct
                    (ProductCode, ProductName, SupplierCode, Cost, RetailPrice, Quantity)
      VALUES(...);
      

      Without seeing what values you are trying to insert (which would help narrow down the issue), I would say that this is the most likely cause of the issue, assuming that the order of columns in your initial "/// Columns" list is the actual order. If you look at the order of how the fields are inserted, txtRetail.Text and txtQuantity.Text are switched as the table appears to have Quantity and then RetailPrice. Specifying the field list in the INSERT eliminates this issue, even if it isn't the actual problem here.

    If #4 is not the problem, then you need to debug and step through to see the values of txtRetail.Text and txtQuantity.Text as they exist when they are concatenated into the SqlCommand.