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

You need to always provide values for all query parameters. Just because some data started in the wrong format does not change this. — Richard, Oct 25 '16 at 09:46
Is this actual code? You are using `AddWithValue` but passing arguments as if you are trying to use `Add` - I would suggest [not using `AddWithValue` at all](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — GarethD, Oct 25 '16 at 09:56

score 2 · Accepted Answer · answered Oct 25 '16 at 09:47

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;
}

answered Oct 25 '16 at 09:47

fubo

44,811
17
103
137

Actually i'am getting data from meter and it's sending me event(s) data, also i get accurate device ID for particular event but in all cases it doesn't go inside if part – Moeez Oct 25 '16 at 09:52
1

so obviously the sub-string within `strOccurrence` doesn't match `yyMMddHHmmss` otherwise `DateTime.TryParseExact` would return `true` – fubo Oct 25 '16 at 09:54
At first it was matching, i don't know what had happened – Moeez Oct 25 '16 at 10:02
@GarethD i have also tried with `Add` but still the result is same – Moeez Oct 25 '16 at 10:08
1

@faisal1208 debug your code and take a look at the content of `strOccurrence ` – fubo Oct 25 '16 at 10:55

GarethD · Answer 2 · 2016-10-25T11:36:54.730

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);

Ok, i'll add this piece of code but before it i want to tell you that i have total of **400** size string in which 160 is filled up and the remaining is divided into 25 25 pieces of chunks and in 25 chunk 12 is for Occurrence and 12 is for Recover and remaining 1 is for comma, so that's why i have added `string strOccurrence = Meter_data.Substring(161, 12); string strRecovery = Meter_data.Substring(173, 12);` — Moeez, Oct 25 '16 at 10:21
So do you want to throw an exception if either date is not in a valid format? Have you tried debugging your code to see what the value of the string is that is failing to be converted? — GarethD, Oct 25 '16 at 10:33
I want to insert the Occurrence Time and Recovery Time with correct format — Moeez, Oct 25 '16 at 11:17
Dates do not have a format. What I am asking is, if your meter_Data is invalid, e.g. if `Meter_data.Substring(173, 12)` gave you a result of `ThisIsNotADate`, what should happen? — GarethD, Oct 25 '16 at 11:25
It is giving me correct date time as `161025120830` but while it inserts in DB it should be like `2016-10-25 12:08:30` — Moeez, Oct 25 '16 at 11:31

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

2 Answers2