Difference in SqlParameterCollection.Add methods

  • Thread starter Thread starter Roy Chastain
  • Start date Start date
R

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??.
 
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/

Roy Chastain said:
I have three SQL commands that use the same set of parameters (both name
Click to expand...
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
Click to expand...
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
Click to expand...
ArgumentException that the parameter is in another parameter
 
Back
Top