Difference in SqlParameterCollection.Add methods

[Roy Chastain]

I have three SQL commands that use the same set of parameters (both name and value). I originally had code for each command that
created the parameters by doing
command1.Parameters.Add("parametername",SqlType);
command1.Parameters[0].Value = the_value;
command2.Parameters.Add("sameparametername",SqlType);
command2.Parameters[0].Value = the_value;
......

I said to my self this is a lot of extra work. Why don't create the SqlParameters and use them for all commands as below.

SqlParameter parameter1 = new SqlParameter("ParameterName",SqlType);
parameter1.Value = the_value;

command1.Parameters.Add(parameter1);
command2.Parameters.Add(parameter2); // <------------ Throws the exception
......

Well that code does not work. It throws the (admittedly) documented ArgumentException that the parameter is in another parameter
collection.

Question is - Why does it care??

I then changed to code to

SqlParameter parameter1ForCommand1 = new SqlParameter("ParameterName",SqlType);
parameter1ForCommand1.Value = the_value;
command1.Parameters.Add(parameter1ForCommand1);
SqlParameter parameter1ForCommand2 = new SqlParameter("SameParameterName",SqlType);
parameter1ForCommand2.Value = the_value;
command1.Parameters.Add(parameter1ForCommand2); // <----------- Throws the exception

This throws the same exception.

Question is - Should not this version work??.

 

[Steve Willcock]

Roy, the second example here should work ok - are you sure this code is
exactly as it appears in your application?

--
Steve Willcock (MCSD for Microsoft.NET)
http://www.willcockconsulting.com/

I have three SQL commands that use the same set of parameters (both name

and value). I originally had code for each command that

created the parameters by doing
command1.Parameters.Add("parametername",SqlType);
command1.Parameters[0].Value = the_value;
command2.Parameters.Add("sameparametername",SqlType);
command2.Parameters[0].Value = the_value;
.....

I said to my self this is a lot of extra work. Why don't create the

SqlParameters and use them for all commands as below.

SqlParameter parameter1 = new SqlParameter("ParameterName",SqlType);
parameter1.Value = the_value;

command1.Parameters.Add(parameter1);
command2.Parameters.Add(parameter2); // <------------ Throws the exception
.....

Well that code does not work. It throws the (admittedly) documented

ArgumentException that the parameter is in another parameter