Variable in SqlCommand must be unique within a query batch.. but it is?

Question

I am making a windows service to gather a bunch of information and I'm sending it to the DB. I know methodology below works because I have used it on other tables with no errors. But I had to expand my DELETE command to use to have two conditions, and now I'm receiving an error.

This is the error I am receiving: "The variable name '@deletedrive' has already been declared. Variable names must be unique within a query batch or stored procedure."

Here is my code (The ending left out because it simply is catches the exception we're getting. Also ignore 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 may need anything else to reference.

Answer 1

You only need to add the parameters once. You are adding parameters each time through the loop. Change it so that you add the parameters once, but set their values each time toruhg the loop.

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(); 
}