SQL Command Parameters

Question

I have an asp.net MVC web app that adds data to a sql database. It has been working fine with the following code:

query = "insert into ImageNameTable(ImageName,NotifyDate,NotifyDate2) values(@ImageName,@NotifyDate,@NotifyDate2)";

SqlParameter StringParameter = new SqlParameter();
StringParameter.SqlDbType = SqlDbType.VarChar;
StringParameter.ParameterName = "@ImageName";
StringParameter.Value = imageName;


DateTime now = DateTime.Now;
now = now.AddMilliseconds(-now.Millisecond);
DateTime notifyDate = now.AddDays(1);

DateTime notify = DateTime.Now;
DateTime aNotify = DateTime.Now;

notify = notify.AddMilliseconds(-notify.Millisecond);
notify = notify.AddMinutes(1);
aNotify = notify.AddMinutes(1);

SqlParameter notifyDateParameter = new SqlParameter();
notifyDateParameter.SqlDbType = SqlDbType.DateTime;
notifyDateParameter.ParameterName = "@NotifyDate";
notifyDateParameter.Value = notify;

SqlParameter notifyDate2Parameter = new SqlParameter();
notifyDate2Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate2Parameter.ParameterName = "@NotifyDate2";
notifyDate2Parameter.Value = aNotify;

SqlCommand cmd = new SqlCommand(query, conn);


cmd.Parameters.Add(StringParameter);
cmd.Parameters.Add(notifyDateParameter);
cmd.Parameters.Add(notifyDate2Parameter);

cmd.ExecuteNonQuery();        
cmd.Dispose();
conn.Close();
conn.Dispose();

This was used for testing purposes. So now I have changed it to what I actually want it to do, and nothing is appearing in the sql database. Essentially, all I have done is added parameters to the sql command. There is no error, just that it doesn't appear in the database. Here is the change:

query = "insert into ImageNameTable(ImageName,NotifyDate,NotifyDate2,NotifyDate3,NotifyDate4,NotifyDate5,NotifyDate6,NotifyDate7,NotifyDate8,NotifyDate9,NotifyDate10,NotifyDate11,NotifyDate12,NotifyDate13,NotifyDate14) values(@ImageName,@NotifyDate,@NotifyDate2,@NotifyDate3,@NotifyDate4,@NotifyDate5,@NotifyDate6,@NotifyDate7,@NotifyDate8,@NotifyDate9,@NotifyDate10,@NotifyDate11,@NotifyDate12,@NotifyDate13,@NotifyDate14)";


SqlParameter StringParameter = new SqlParameter();
StringParameter.SqlDbType = SqlDbType.VarChar;
StringParameter.ParameterName = "@ImageName";
StringParameter.Value = imageName;

DateTime now = DateTime.Now;
now = now.AddMilliseconds(-now.Millisecond);
DateTime notifyDate = now.AddDays(1);

DateTime notify = DateTime.Now;
DateTime aNotify = DateTime.Now;


notify = notify.AddMilliseconds(-notify.Millisecond);
notify = notify.AddHours(1);
aNotify = notify.AddHours(5);
DateTime bNotify = aNotify.AddDays(1);
DateTime cNotify = bNotify.AddDays(1);
DateTime dNotify = cNotify.AddDays(2);
DateTime eNotify = dNotify.AddDays(2);
DateTime fNotify = eNotify.AddDays(3);
DateTime gNotify = fNotify.AddDays(4);
DateTime hNotify = gNotify.AddDays(7);
DateTime iNotify = hNotify.AddDays(14);
DateTime jNotify = bNotify.AddMonths(2);
DateTime kNotify = bNotify.AddMonths(3);
DateTime lNotify = bNotify.AddMonths(4);
DateTime mNotify = bNotify.AddMonths(6);


SqlParameter notifyDateParameter = new SqlParameter();
notifyDateParameter.SqlDbType = SqlDbType.DateTime;
notifyDateParameter.ParameterName = "@NotifyDate";
notifyDateParameter.Value = notify;

SqlParameter notifyDate2Parameter = new SqlParameter();
notifyDate2Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate2Parameter.ParameterName = "@NotifyDate2";
notifyDate2Parameter.Value = aNotify;

SqlParameter notifyDate3Parameter = new SqlParameter();
notifyDate3Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate3Parameter.ParameterName = "@NotifyDate3";
notifyDate3Parameter.Value = bNotify;

SqlParameter notifyDate4Parameter = new SqlParameter();
notifyDate4Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate4Parameter.ParameterName = "@NotifyDate4";
notifyDate4Parameter.Value = cNotify;

SqlParameter notifyDate5Parameter = new SqlParameter();
notifyDate5Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate5Parameter.ParameterName = "@NotifyDate5";
notifyDate5Parameter.Value = dNotify;

SqlParameter notifyDate6Parameter = new SqlParameter();
notifyDate6Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate6Parameter.ParameterName = "@NotifyDate6";
notifyDate6Parameter.Value = eNotify;

SqlParameter notifyDate7Parameter = new SqlParameter();
notifyDate7Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate7Parameter.ParameterName = "@NotifyDate7";
notifyDate7Parameter.Value = fNotify;

SqlParameter notifyDate8Parameter = new SqlParameter();
notifyDate8Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate8Parameter.ParameterName = "@NotifyDate8";
notifyDate8Parameter.Value = gNotify;

SqlParameter notifyDate9Parameter = new SqlParameter();
notifyDate9Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate9Parameter.ParameterName = "@NotifyDate9";
notifyDate9Parameter.Value = hNotify;

SqlParameter notifyDate10Parameter = new SqlParameter();
notifyDate10Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate10Parameter.ParameterName = "@NotifyDate10";
notifyDate10Parameter.Value = iNotify;

SqlParameter notifyDate11Parameter = new SqlParameter();
notifyDate11Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate11Parameter.ParameterName = "@NotifyDate11";
notifyDate11Parameter.Value = jNotify;

SqlParameter notifyDate12Parameter = new SqlParameter();
notifyDate12Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate12Parameter.ParameterName = "@NotifyDate12";
notifyDate12Parameter.Value = kNotify;

SqlParameter notifyDate13Parameter = new SqlParameter();
notifyDate13Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate3Parameter.ParameterName = "@NotifyDate13";
notifyDate13Parameter.Value = lNotify;

SqlParameter notifyDate14Parameter = new SqlParameter();
notifyDate14Parameter.SqlDbType = SqlDbType.DateTime;
notifyDate14Parameter.ParameterName = "@NotifyDate14";
notifyDate14Parameter.Value = mNotify;


SqlCommand cmd = new SqlCommand(query, conn);


cmd.Parameters.Add(StringParameter);
cmd.Parameters.Add(notifyDateParameter);
cmd.Parameters.Add(notifyDate2Parameter);
cmd.Parameters.Add(notifyDate3Parameter);
cmd.Parameters.Add(notifyDate4Parameter);
cmd.Parameters.Add(notifyDate5Parameter);
cmd.Parameters.Add(notifyDate6Parameter);
cmd.Parameters.Add(notifyDate7Parameter);
cmd.Parameters.Add(notifyDate8Parameter);
cmd.Parameters.Add(notifyDate9Parameter);
cmd.Parameters.Add(notifyDate10Parameter);
cmd.Parameters.Add(notifyDate11Parameter);
cmd.Parameters.Add(notifyDate12Parameter);
cmd.Parameters.Add(notifyDate13Parameter);
cmd.Parameters.Add(notifyDate14Parameter);


cmd.ExecuteNonQuery();       
cmd.Dispose();
conn.Close();
conn.Dispose();

I'm wondering if I have used too many parameters, but I've been told that the max amount of parameters a command can hold is way more than 14. Does anyone know what is going on here?

There is no error. The problem is that nothing appears in the database now. The only change I have made to it is adding parameters to the sql command. — Vigs
– Vigs, Commented Aug 30, 2015 at 4:35
9 times out of 10, where theres no error and no data, you're looking in the wrong database. Check your connection string. Make sure it matches where you are looking for the data to appear. — Sam Axe
– Sam Axe, Commented Aug 30, 2015 at 4:37
Take @SamAxe's advice and make sure you're looking in the right database. If you are, make sure your code isn't catching and squelching errors - that's regrettably common. Also, note that you can make your code a lot more compact - and still readable, possibly even more so - by using the Parameters.AddWithValue command: instead of the four notifyDateParameter lines followed by cmd.Parameters.Add(notifyDateParameter) you can use one line: cmd.Parameters.AddWithValue(@notifyDate, notify). — Ed Gibbs
– Ed Gibbs, Commented Aug 30, 2015 at 4:43
I'll point out the obvious but it may lead to something : the first code snippet you provided is complete, the second one is not. Please provide all the code that executes the 2nd example. You may simply be missing something basic due to copy/paste errors. — Paul Sasik
– Paul Sasik, Commented Aug 30, 2015 at 4:44
I see the full code now. Nothing obvious jumps out except that the method does not follow best practices. Break up the method into smaller constituent bits of logic and the bug may present itself during the refactoring. — Paul Sasik
– Paul Sasik, Commented Aug 30, 2015 at 4:49

Igor · Accepted Answer · 2015-09-01 15:44:19Z

1

notifyDate3Parameter.ParameterName = "@NotifyDate13"; - note 3 and 13 to the left and to the right of the assignment sign, respectively.

edited Sep 1, 2015 at 15:44

answered Aug 31, 2015 at 15:33

Igor

15.9k1 gold badge29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

@WonkotheSane - thanks :), after the last OP's comment it was only a matter of hitting Ctrl+F

Collectives™ on Stack Overflow

SQL Command Parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related