The variable in SqlCommand must be unique within the request package .. but is that so?

Question

I am doing a windows service to collect a bunch of information, and I am sending it to the database. I know the methodology below because I used it on other tables without errors. But I had to extend my DELETE command to use two conditions, and now I get an error message.

This is the error I get: "The variable name" @deletedrive "has already been declared. The variable names must be unique in a batch or stored procedure."

Here is my code (the ending is left because it just catches the exception we get. Also ignore the commented lines):

private static void ServerSpaceTimerAction(object source, ElapsedEventArgs e, string InstallLocation) { //StreamWriter RecordServerSpace = new StreamWriter(InstallLocation + "\\Temp\\ServerSpaceTemp.csv"); try { ServerSpace GetSpace = new ServerSpace(); GetConfigurations Configs = new GetConfigurations(); Configs.GetServers(); Configs.GetDatabase(); var connection = new SqlConnection("Data Source=" + Configs.DataSource + "; Initial Catalog=" + Configs.DatabaseCatalog + "; Integrated Security=" + Configs.IntegratedSecurityProtocol); connection.Open(); char[] delim = { ' ', ',' }; string sInsertServerSpace = "INSERT INTO ServerSpace (date_time, server, region, farm, technology, role, drive, total_space, free_space) VALUES (@dt, @ser, @reg, @farm, @tech, @role, @drive, @ts, @fs)"; string sDeleteServerSpace = "DELETE FROM ServerSpace WHERE server=@serd AND drive=@deletedrive "; foreach (string server in Configs.servers) { string temp; SqlCommand cmdInsertServerSpace = new SqlCommand(sInsertServerSpace, connection); SqlCommand cmdDeleteServerSapce = new SqlCommand(sDeleteServerSpace, connection); Configs.GetServerAttributes(server); if (Configs.technology != "SQL") { GetSpace.DriveSpaceByServer(server); } else { GetSpace.DriveSpaceByServerMount(server); } string[] driveStats = GetSpace.ServerSpaceStats.Split(delim); temp = DateTime.Now.ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@dt", temp); temp = server.ToString(); cmdDeleteServerSapce.Parameters.AddWithValue("@serd", temp); cmdInsertServerSpace.Parameters.AddWithValue("@ser", temp); temp = Configs.regiongroup.ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@reg", temp); temp = Configs.farmgroup.ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@farm", temp); temp = Configs.technology.ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@tech", temp); temp = Configs.rolegroup.ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@role", temp); for (int i = 0; i < driveStats.Length; i++) { temp = driveStats[i].ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@drive", temp); cmdDeleteServerSapce.Parameters.AddWithValue("@deletedrive", temp); i++; temp = driveStats[i].ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@ts", temp); i++; temp = driveStats[i].ToString(); cmdInsertServerSpace.Parameters.AddWithValue("@fs", temp); cmdDeleteServerSapce.ExecuteNonQuery(); cmdInsertServerSpace.ExecuteNonQuery(); } //RecordServerSpace.WriteLine(DateTime.Now + "," + server + "," + Configs.regiongroup + "," + Configs.farmgroup + "," + Configs.technology + "," + Configs.rolegroup + "," + GetSpace.ServerSpaceStats); } connection.Close(); 

Let me know if you might need anything else for reference.

Answer 1

You only need to add parameters once. Each time you add parameters through a loop. Change it so that you add parameters once, but each time set their values ​​during the cycle.

 SqlCommand cmdDeleteServerSapce = new SqlCommand(sDeleteServerSpace, connection); .... cmdInsertServerSpace.Parameters.AddWithValue("@drive", temp); cmdDeleteServerSapce.Parameters.AddWithValue("@deletedrive", temp)); for (int i = 0; i < driveStats.Length; i++) { temp = driveStats[i].ToString(); cmdInsertServerSpace.Parameters["@drive"].value = temp; cmdDeleteServerSapce.Parameters["@deletedrive"].value = temp; ..... cmdDeleteServerSapce.ExecuteNonQuery(); cmdInsertServerSpace.ExecuteNonQuery(); } 

Answer 2

It looks like you are using a command in a loop. You only need to set the parameter once (AddWithValue). You can change it in subsequent attempts, but not add it again.

You should:

 cmdDeleteServerSapce.Parameters.Add("@deletedrive", SqlDbType.VarChar); 

Out of cycle.

and

 cmdDeleteServerSapce.Parameters["@deletedrive"].Value = temp; 

Inside the loop.

Answer 3

I believe your cmdInsertServerSpace.ExecuteNonQuery() and cmdDeleteServerSapce.ExecuteNonQuery() missing from your loop. I would recommend the following:

 cmdInsertServerSpace.Parameters.Add("@drive", SqlDbType.VarChar); cmdDeleteServerSapce.Parameters.Add("@deletedrive", SqlDbType.VarChar); for (int i = 0; i < driveStats.Length; i++) { string temp = driveStats[i].ToString(); cmdInsertServerSpace.Parameters["@drive"].Value = temp; cmdDeleteServerSapce.Parameters["@deletedrive"].Value = temp; i++; ..... cmdInsertServerSpace.ExecuteNonQuery(); cmdDeleteServerSapce.ExecuteNonQuery(); } 

Answer 4

You must use the clear parameter

  **cmdInsertServerSpace.Parameters.Clear();** cmdInsertServerSpace.Parameters.AddWithValue("@dt", temp); temp = server.ToString(); cmdDeleteServerSapce.Parameters.AddWithValue("@serd", temp); cmdInsertServerSpace.Parameters.AddWithValue("@ser", temp);