C# Service SQL Command

See more:

C#

SQL

Hello,

I would like to know whats wrong with my code, it is only executing the first SQL Command and it is not returing any error to the event log. I must be missing something, hope you guys can help me.

Thanks

Heres the Code:

public static void disco_total()
        {
            string string_con = System.Configuration.ConfigurationSettings.AppSettings["con_string"];
            SqlConnection myConnection = new SqlConnection(string_con);
            myConnection.Open();

         
                ManagementObjectSearcher searcher = new ManagementObjectSearcher("root\\CIMV2", "SELECT * FROM Win32_LogicalDisk Where DriveType = 3");
                foreach (ManagementObject wmi in searcher.Get())
                {
                    disco_letra = Convert.ToString(letra_disco.Append(wmi["Name"].ToString()));
                    //SqlCommand myCommand_discos = new SqlCommand("Insert into servidor_disco(id_servidor,nome_disco) Values('1','"+disco_letra+"')",myConnection);
                    SqlCommand myCommand_discos = new SqlCommand("DECLARE @id INT DECLARE @idd INT DECLARE @discos VARCHAR SET @id = (SELECT distinct id_servidor FROM servidores where nome_servidor='" + nome_computador + "') SET @idd=(SELECT distinct id_servidor from servidor_disco) SET @discos = (SELECT  distinct CAST(nome_disco as Varchar) from servidor_disco where nome_disco='" + disco_letra + "')  IF @id=@idd AND @discos+':' = '" + disco_letra + "' UPDATE servidor_disco SET id_servidor=@id,nome_disco='" + disco_letra + "' where id_servidor=@id AND nome_disco='" + disco_letra + "' ELSE INSERT INTO servidor_disco (id_servidor,nome_disco) Values (@id,'" + disco_letra + "')", myConnection);
                    myCommand_discos.ExecuteNonQuery();
                               
                              try
                                {
                    StringBuilder Drive = new StringBuilder();
                    StringBuilder Drive2 = new StringBuilder();
                            
                    disco_size = Math.Round(((((double)Convert.ToDouble(wmi["Size"]) / 1024) / 1024) / 1024), 2);
                    disco_livre = Math.Round(((((double)Convert.ToDouble(wmi["FreeSpace"]) / 1024) / 1024) / 1024), 2);
                    disco_space = Convert.ToString(Drive.Append(disco_size.ToString()));
                    disco_free = Convert.ToString(Drive2.Append(disco_livre.ToString()));
                                        
                    SqlCommand myCommand_discos_info = new SqlCommand("DECLARE @id VARCHAR DECLARE @idd VARCHAR DECLARE @iddd VARCHAR SET @id = (SELECT distinct id_servidor FROM servidores where nome_servidor='"+nome_computador+"') SET @idd=(SELECT distinct id_disco from servidor_disco where id_Servidor=@id AND nome_disco='"+disco_letra+"') INSERT INTO disco_info (id_disco,disco_queue,disco_total,disco_livre,data_recolha) Values (@idd,'"+ disco_length +"','"+ disco_space +"','"+ disco_free +"','10-20-11')", myConnection);
                    myCommand_discos.ExecuteNonQuery();

                    letra_disco.Remove(0, letra_disco.Length);
                                }
                             catch (Exception erro)
                                    {
                                        disco_error = Convert.ToString(erro);

                                    }

                }
        
           
            }