Which Data Type in C# Should I use to enter data into a Decimal Column in T-SQL?

Question

I am having a hard time trying to figure out which data type I would use in C# to enter data into my table in my database which contains a decimal(5,2). When I tried using a C# decimal data type, it said that It had an error converting numeric to decimal. When I tried string, It said that it could not convert nvarchar to decimal. When I tried float... Same thing happened, except the excuse was a "real" data type. double also failed to work.

I have a stored procedure which enters the data into my table, but before I run of and cast data types in my stored procedure to the actual decimal, is there any other way I can rather Convert a c# data type to fit in my decimal(5,2) field?

private void btnAddClientComputer_Click(object sender, EventArgs e)
{
    SQLCommands comm = new SQLCommands();
    try
    {
        comm.AddClientComputer(int.Parse(cbCustomerID.Text), cbAction.Text, decimal.Parse(tbCost.Text));
    }
    catch (FormatException)
    {
        MessageBox.Show("The cost you have entered is invalid. Please ensure the cost is above 0, and is an actual number", "Invalid Input at Cost", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
    }
}

...

public void AddClientComputer(int CustomerID, string Action, decimal Cost)
{
    try
    {
        comm = new SqlCommand("UspAddClientComputer", conn); // Stored Procedure - see sql file
        comm.Parameters.AddWithValue("@CustomerID", CustomerID);
        comm.Parameters.AddWithValue("@Action", Action);
        comm.Parameters.AddWithValue("@Cost", Cost);
        comm.CommandType = CommandType.StoredProcedure;
        comm.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
    }
}

...

CREATE TABLE ClientComputers
            (ClientComputerID int Identity(1,1) primary key clustered
            ,CustomerID int
            ,Action varchar(7) check (Action = 'Upgrade' OR Action = 'Store')
            ,Cost decimal(5,2) check (Cost > 0)
            ,Constraint FKCustomerComputer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));
Go

...

CREATE PROCEDURE uspAddClientComputer @CustomerID int, @Action varchar(7), @Cost decimal(5,2)
AS
BEGIN TRY
    BEGIN TRANSACTION TrnAddClientComputer;
     INSERT INTO [TCTdb].[dbo].[ClientComputers]
           ([CustomerID]
           ,[Action]
           ,[Cost])
     VALUES
           (@CustomerID
           ,@Action
           ,@Cost)
    COMMIT TRANSACTION TrnAddClientComputer;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TrnAddClientComputer;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState
               );
END CATCH
GO

Answer 1

I have found a way to fix my problem under the circumstances, thanks to sll bringing a few things under my attention. The following code worked for me.

private void btnAddClientComputer_Click(object sender, EventArgs e)
{
    SQLCommands comm = new SQLCommands();
    double trycost;
    if (double.TryParse(tbCost.Text,out trycost))
    {
        comm.AddClientComputer(int.Parse(cbCustomerID.Text), cbAction.Text, trycost);
    }
    else
    {
        MessageBox.Show("The cost you have entered is invalid. Please ensure the cost is above 0, and is an actual number", "Invalid Input at Cost", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
    }
}

...

public void AddClientComputer(int CustomerID, string Action, double Cost)
    {
        try
        {
            comm = new SqlCommand("UspAddClientComputer", conn); // Stored Procedure - see sql file
            comm.Parameters.AddWithValue("@CustomerID", CustomerID);
            comm.Parameters.AddWithValue("@Action", Action);
            comm.Parameters.Add(new SqlParameter("@Cost",Cost));
            comm.CommandType = CommandType.StoredProcedure;
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
        }
    }

..Here comes the solution part. I replaced the Decimal(5,2) with smallmoney.

CREATE TABLE ClientComputers
            (ClientComputerID int Identity(1,1) primary key clustered
            ,CustomerID int
            ,Action varchar(7) check (Action = 'Upgrade' OR Action = 'Store')
            ,Cost smallmoney check (Cost > 0)
            ,Constraint FKCustomerComputer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));
Go

---------STORED PROCEDURES
--ADD CLIENT COMPUTER
CREATE PROCEDURE uspAddClientComputer @CustomerID int, @Action varchar(7), @Cost smallmoney
AS
BEGIN TRY
    BEGIN TRANSACTION TrnAddClientComputer;
     INSERT INTO [TCTdb].[dbo].[ClientComputers]
           ([CustomerID]
           ,[Action]
           ,[Cost])
     VALUES
           (@CustomerID
           ,@Action
           ,@Cost)
    COMMIT TRANSACTION TrnAddClientComputer;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TrnAddClientComputer;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState
               );
END CATCH
GO

Thanks to everyone who has tried. This answered my question. Right Idea, WRONG data type.

Answer 2

I would suggest that you add the decimal parameter with the type specified. That means not just using AddWithValue, but creating a Parameter object.

I suspect that the problem is being caused because the code is not managing to convert cleanly.

ETA :

Your code was

comm.Parameters.Add("@Cost",SqlDbType.Decimal);
comm.Parameters["@Cost"].Value = Cost;

You need to to do something like ( As I said I don't have syntax checking easily available )

SqlParameter param= new SqlParameter("@Cost", SqlDbType.Decimal, Cost);//there are more parameters which I cannot remember
comm.Parameters.Add(param);

The important thing is to create the object where you can pass in all of the parameters which can define it clearly as a SQL decimal.

Answer 3

Try out

 comm.Parameters.Add(new SqlParameter("@Cost", Cost));

BTW,

you can refactor following block

 try
    {
        comm.AddClientComputer(int.Parse(cbCustomerID.Text), cbAction.Text, decimal.Parse(tbCost.Text));
    }
    catch (FormatException)
    {
        MessageBox.Show("The cost you have entered is invalid. Please ensure the cost is above 0, and is an actual number", "Invalid Input at Cost", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
    }

to

// TODO: do the same for int.Parse as well
decimal userDefinedCost;
if (decimal.TryParse(tbCost.Text, out userDefinedCost))
{
     comm.AddClientComputer(int.Parse(cbCustomerID.Text), cbAction.Text, userDefinedCOst);
}
else
{
     MessageBox.Show("The cost you have entered is invalid. Please ensure the cost is above 0, and is an actual number", "Invalid Input at Cost", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}