C# Must declare the scalar variable "@SomeThing" in insert query

Question

I am working on .net c# , I want to insert some data into a table.

I am write query in Visual Studio and not using stored procedure(s).

This is my query

SqlCommand cmd2 = new SqlCommand("insert into Device_Events (Device_ID, Event_ID, Occurrence_Time, Recovery_Time) values (@Device_ID , @Event_ID, @Occurrence_Time, @Recovery_Time)", con);

And this is my C# code for passing values in it

// For Events
string formatString = "yyMMddHHmmss";
DateTime Occurrence_Time, Recovery_Time;
string strOccurrence = Meter_data.Substring(161, 12);
string strRecovery = Meter_data.Substring(173, 12);

cmd2.Parameters.AddWithValue("@Device_ID", device_Id);
cmd2.Parameters.AddWithValue("@Event_ID", event_Id);

if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{
    if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
    {
        cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = Occurrence_Time;
        cmd2.Parameters.AddWithValue("@Recovery_Time", SqlDbType.DateTime).Value = Recovery_Time;
    }
}

int Device_Events_rows_executed = cmd2.ExecuteNonQuery();
Console.WriteLine("Rows Executed: '{0}'", Device_Events_rows_executed);

It doesn't goes into the if part and i am getting an exception named Must declare the scalar variable "@Occurrence_Time"

Also i have read this link but unable to find any help

Updated Code

After a suggestion I have added following piece of code

            cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
            cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
            cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;
            cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;

            string formatString = "yyMMddHHmmss";
            DateTime Occurrence_Time, Recovery_Time;
            string strOccurrence = Meter_data.Substring(161, 12);
            string strRecovery = Meter_data.Substring(173, 12);

            if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
            {
                cmd2.Parameters["@Occurrence_Time"].Value = Occurrence_Time;
            }
            if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
            {
                cmd2.Parameters["@Recovery_Time"].Value = Recovery_Time;
            }

Although it's not showing me and error or exception but still it doesn't gets into the both if conditions

Any help would be highly appreciated

Answer 1

As has been stated, because of your conditional flows, there are code paths where you will try and execute cmd2 without adding the parameters to the command.

The parameters have to be added to the command, even if they don't have a value. I'd suggest setting up all the parameters at the start (using Add() rather than AddWithValue()):

cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;
cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = DBNull.Value;

Then later on, if your strings parse correctly as dates, update the value of the parameter:

if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{
    cmd2.Parameters["@Occurrence_Time"].Value = Occurrence_Time;
}
if (DateTime.TryParseExact(strRecovery, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Recovery_Time))
{
    cmd2.Parameters["@Recovery_Time"].Value = Recovery_Time;
}

EDIT

You seem very confident that the data will always be correct, so you may as well just use ParseExact instead of TryParseExact:

var occuranceDate = DateTime.ParseExact(Meter_data.Substring(161, 12), "yyMMddHHmmss", CultureInfo.InvariantCulture);
var recoveryDate = DateTime.ParseExact(Meter_data.Substring(173, 12), "yyMMddHHmmss", CultureInfo.InvariantCulture);

cmd2.Parameters.Add("@Device_ID", SqlDbType.VarChar, 50).Value = device_Id;
cmd2.Parameters.Add("@Event_ID", SqlDbType.VarChar, 50).Value = event_Id;
cmd2.Parameters.Add("@Occurrence_Time", SqlDbType.DateTime, 50).Value = occuranceDate'
cmd2.Parameters.Add("@Recovery_Time", SqlDbType.DateTime, 50).Value = recoveryDate;

int Device_Events_rows_executed = cmd2.ExecuteNonQuery();
Console.WriteLine("Rows Executed: '{0}'", Device_Events_rows_executed);

Answer 2

You always need to declare all parameters but you can pass a nullor default value in case of missing date:

if (DateTime.TryParseExact(strOccurrence, formatString, CultureInfo.InvariantCulture, DateTimeStyles.None, out Occurrence_Time))
{               
    cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = Occurrence_Time;                
}
else 
{
    cmd2.Parameters.AddWithValue("@Occurrence_Time", SqlDbType.DateTime).Value = null;
}