Problem with using SqlCommand when inserting multiple rows

[doglin]

Hey All:
I have got a quick Question.
I was trying to do db insertion in C# using SQL
here is what I did


StringBuilder bld = new StringBuilder();
bld.Append("INSERT INTO rss_table ");
bld.Append(" ( type, subType, category )")
bld.Append("VALUES ( @Type, @SubType, @Category)");


SqlCommand myCmd = new SqlCommand(bld.ToString(),conn);

//set the connection

myCmd.Connection.Open();

IEnumerator it = list.GetEnumerator();

while (it.MoveNext())
{
Item item = (Item)it.Current;

myCmd.Parameters.Add("@Type", SqlDbType.VarChar);
myCmd.Parameters["@Type"].Value = item.Type;

myCmd.Parameters.Add("@SubType", SqlDbType.VarChar);
myCmd.Parameters["@SubType"].Value = item.SubType;

myCmd.Parameters.Add("@Category", SqlDbType.VarChar);
myCmd.Parameters["@Category"].Value = item.Category;


myCmd.ExecuteNonQuery();
}

myCmd.Connection.Open();

myCmd.Connection.Close();


now the error i am running into is that when I ran , this, it gave me the following error
System.Data.SqlClient.SqlException: The variable name '@Type' has already been declared. Variable names must be unique within a query batch or stored procedure.

I know it is probably because I am doing a while loop and @Type has been used repeatedly

now, how do I insert multiple rows without running into any problems? please let me know.

Many thanks..

[klintan]

Add the parameters outside the loop (directly after you have created the command) and set their values inside the loop.