user1675407 user1675407 - 4 months ago 33
SQL Question

ExecuteNonQuery() Exception with date insertion

I have a stored procedure to insert record and returns the ID of record inserted.

Here is my SQL implementation:

ALTER PROCEDURE InsertViolation
@FactoryName nvarchar(50) ,
@Type nvarchar(100),
@Location nvarchar(30) ,
@Desc nvarchar(300),
@Severity nvarchar(300),
@DateObserved datetime,
@ViolationNumber bigint output
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Violation
(Violation_Factory_Name
,Violation_Type
,Violation_Location
,Violation_Desc
,Violation_Severity
,Violation_Date_Observed)
VALUES
(@FactoryName
,@Type
,@Location
,@Desc
,@Severity
,@DateObserved)
SET @ViolationNumber=SCOPE_IDENTITY()
RETURN @ViolationNumber
END


and i have this C# code to invoke this procedure:

public static long Insert_Violation(Model.Violation violation)
{
string strStoredProcedureName = "InsertViolation";
SqlCommand cmd = new SqlCommand(strStoredProcedureName);
string strConnString = ConfigurationManager.ConnectionStrings["ViolationConnection"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FactoryName", violation.FactoryName);
cmd.Parameters.AddWithValue("@Type", violation.ViolationType);
cmd.Parameters.AddWithValue("@Location", violation.Location);
cmd.Parameters.AddWithValue("@Desc", violation.ViolationDesc);
cmd.Parameters.AddWithValue("@Severity", violation.Serverity);
cmd.Parameters.AddWithValue("@DateObserved", violation.DateObserved);
cmd.Parameters.AddWithValue("@ViolationNumber", -1).Direction = ParameterDirection.Output;

try
{
con.Open();
cmd.ExecuteNonQuery();

return (long) cmd.Parameters["@ViolationNumber"].Value;

}
catch(Exception ex)
{
throw new Exception(ex.Message + " Date Parameter value is " + cmd.Parameters["@DateObserved"].Value + " \n DateTime Object converted to string value is " + violation.DateObserved.ToString());
return -1;
}
finally
{
con.Dispose();
cmd.Dispose();
}
}


I found the row has been inserted in the database but an exception is thrown at
cmd.ExecuteNonQuery();
the problem is that I used the
violation.DateObserved
which is C#
DateTime
object and the corresponding parameter and field in DB is of type
datetime


Here is the exception message that caught and I used throw exception to print it :


System.Exception: Specified cast is not valid. Date Parameter value is 15/12/2012 12:00:00
DateTime Object converted to string value is 15/12/2012 12:00:00


So I could not get the record number instead my function returns -1 as the catch is visited.

Answer

The return statement implicitly casts the result as an integer; you should therefore unbox as int, not long:

return (int) cmd.Parameters["@ViolationNumber"].Value;

If the value you have won't fit it an int, then either use an out parameter, or select the value and use ExecuteScalar.