Reputation: 3984
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
Upvotes: 1
Views: 3709
Reputation: 3984
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.
Upvotes: 0
Reputation: 3139
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.
Upvotes: 1
Reputation: 62564
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);
}
Upvotes: 1