none
Variable @Furn already declared RRS feed

  • Question

  • Hallo

    Hope somebody can give some input into this issue. The form works well and add's records to SQL the first time Pressing the Add button. the second time pressing the same button I get the following error:

    System.Data.SqlClient.SqlException: 'The variable name '@FurnaID' has already been declared. Variable names must be unique within a query batch or stored procedure.'

    Any recommendations and suggestions welcome, as all can see this is my first program using the 3 Tair method, which is all still very new..

    I also tried to delete as much as possible code that is not part of the Add button, but if required I can copy the whole lot..

    public class FurnaceInfoDA
        {
            Connection db = new Connection();
            SqlCommand cmd = new SqlCommand();
            FurnaceInfoBO FurnSymbols = new FurnaceInfoBO();
    
    
            public int SaveProductionRecords(FurnaceInfoBO furnSymbols)
            {
                cmd.Connection = db.conn;
                cmd.CommandText = "INSERT INTO tbCastProductionLog (FurnID,CastNrID,CastSizeID,MaterialSourceID,MaterialTypeID,Weight) " +
                                  "VALUES(@FurnaID,@CastNrID,@CastSizeID,@MaterialSourceID,@MaterialTypeID,@Weight)";
                cmd.Parameters.Add("@FurnaID", SqlDbType.Int).Value = furnSymbols.FurnID;
                cmd.Parameters.Add("@CastNrID", SqlDbType.Int).Value = furnSymbols.CastNrID;
                cmd.Parameters.Add("@CastSizeID", SqlDbType.Int).Value = furnSymbols.CastSizeID;
                cmd.Parameters.Add("@MaterialSourceID", SqlDbType.Int).Value = furnSymbols.MaterialSourceID;
                cmd.Parameters.Add("@MaterialTypeID", SqlDbType.Int).Value = furnSymbols.MaterialTypeID;
                cmd.Parameters.Add("@Weight", SqlDbType.Decimal).Value = furnSymbols.MaterialWeight;
               
    
                DataTable dt = new DataTable();
                SqlDataAdapter SqlAdapt = new SqlDataAdapter(cmd);
                try
                {
                    SqlAdapt.Fill(dt);
                    cmd.Connection.Close();                
                }
                catch 
                {
                    throw;
                }
                return 0;
            }
    
        }
    }

      public partial class frFurnaceFoundryMain : Form
        {
            DataTable dtFurnData = new DataTable();
            FurnaceInfoBA objFurnace = new FurnaceInfoBA();
            FurnaceInfoBO objFurnSymbols = new FurnaceInfoBO();
            int CastNrID;
            int FurnaceSelectedID = 1;
            int CastSelectedID;        
            int MaterialSourceID = 1;
            int MaterialTypeID = 1;
            float MaterialWeight = 0;
    
            public frFurnaceFoundryMain()
            {
                InitializeComponent();
               
            }
            private void frFurnaceFoundryMain_Load(object sender, EventArgs e)
            {            
                SetDateTime();
                UpdateForm(FurnaceSelectedID);            
            }          
    
            private void UpdateForm(int FurnID)
            {
                getFuranceByID(FurnID);
                getCastNrByFurnID(FurnID);
                PopulateCastSizeDropDown(FurnID);
                PopulateMaterialSourceDropDown();
                PopulateMaterialTypeDropDown(1);            
            }          
                 
    
          
         
    
            private void btnAddMaterial_Click(object sender, EventArgs e)
            {
                int ErrorCode = 0;
                objFurnSymbols.FurnID = FurnaceSelectedID;
                objFurnSymbols.CastNrID = CastNrID;
                objFurnSymbols.CastSizeID = CastSelectedID;
                objFurnSymbols.MaterialSourceID = MaterialSourceID;
                objFurnSymbols.MaterialTypeID = MaterialTypeID;
                objFurnSymbols.MaterialWeight = MaterialWeight;
    
               ErrorCode = objFurnace.ValidateAndSaveProductRecords(objFurnSymbols, out string message);
    
                if (ErrorCode == 0)
                {
                    MessageBox.Show("Succefully added");
                }
                else
                    MessageBox.Show("["+ErrorCode.ToString()+"] Message: "+message.ToString());
    
            }
            
    
            private void txtMaterialWeight_Validated(object sender, EventArgs e)
            {           
    
                float.TryParse(txtMaterialWeight.Text,out MaterialWeight);
            }
        }
        
    }
    
      public class FurnaceInfoBA
        {
            FurnaceInfoDA furnInfo = new FurnaceInfoDA();
            FurnaceInfoBO furnSymbols = new FurnaceInfoBO();
    
            public DataTable GetAllFurances()
            {
                DataTable dt = new DataTable();           
               
                try
                {
                    return furnInfo.SelectAllFurnace();
                }
                catch
                {
                    throw;
                }
                
            }
    
            public DataTable GetFuranceByID(int furnID)
            {
                DataTable dt = new DataTable();
    
                try
                {
                    return furnInfo.GetFurnaceByID(furnID);
                }
                catch
                {
                    throw;
                } 
            }
    
            public DataTable GetCastNrByFurnID (int FurnID)
            {
                {
                    DataTable dt = new DataTable();
    
                    try
                    {
                        return furnInfo.GetCastNrByFurnID(FurnID);
                    }
                    catch
                    {
                        throw;
                    }
                }
    
            }
    
            public DataTable GetCastSizeByFurnID(int FurnID)
            {
                DataTable dt = new DataTable();
                dt = furnInfo.SelectCastByFurnID(FurnID);
    
                return dt;
            }
    
            public DataTable GetAllMaterialSource()
            {                   
                    try
                    {
                        return furnInfo.SelectAllMaterialSources();
                    }
                    catch
                    {
                        throw;
                    }            
            }
    
            public DataTable GetMaterialTypeBySourceID(int MaterialSourceID)
            {          
                try
                {
                    return furnInfo.SelectMaterialTypeByMaterialID(MaterialSourceID);
                }
                catch
                {
                    throw;
                }
            }
    
            public int ValidateAndSaveProductRecords (FurnaceInfoBO furnSymbols, out string Message)
            {
                string message = "";
                int success = 0;
    
                success = ValidateProductRecords(furnSymbols, out message);
    
                if (success == 0)
                {
                    furnInfo.SaveProductionRecords(furnSymbols);
                }
    
    
                Message = message;
                return success;
            }
    
            public int ValidateProductRecords (FurnaceInfoBO furnSymbols, out string Message)
            {
                string message = "";
                int success = 0;
                float Weight = furnSymbols.MaterialWeight;
                decimal i = 0;
    
    
                
                if (furnSymbols.FurnID == 0)            
                {
                    success = 1;
                    message = "Please enter Select correct Cast Nr";
                 }
                if (furnSymbols.CastNrID == 0)
                {
                    success = 2;
                    message = "Please enter enter correct Cast Nr";
                }
                if (furnSymbols.CastSizeID ==0)
                {
                    success = 3;
                    message = "Please select a valid Cast Size";
                }
                if (furnSymbols.MaterialSourceID == 0)
                {
                    success = 4;
                    message = "Please select a valid Material Source";
                }
                if (furnSymbols.MaterialTypeID == 0)
                {
                    success = 5;
                    message = "Please select a valid Material Type";
                }            
                if ((! decimal.TryParse(furnSymbols.MaterialWeight.ToString(), out i)) || (furnSymbols.MaterialWeight == 0))
                {
                    success = 6;
                    message = "Please enter a valid weight";
                }            
                    Message = message;
                    return success;      
                
            }
        }
    }
    



    labjac

    Monday, October 22, 2018 2:58 PM

Answers

  • Try moving the definition of cmd:

    public class FurnaceInfoDA

    {

            Connection db = new Connection();

            SqlCommand cmd = new SqlCommand();

            FurnaceInfoBO FurnSymbols = new FurnaceInfoBO();

      

            public int SaveProductionRecords(FurnaceInfoBO furnSymbols)

            {

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = db.conn;

                . . .


    • Marked as answer by labjac Tuesday, October 23, 2018 5:17 AM
    Monday, October 22, 2018 6:00 PM

All replies

  • Try moving the definition of cmd:

    public class FurnaceInfoDA

    {

            Connection db = new Connection();

            SqlCommand cmd = new SqlCommand();

            FurnaceInfoBO FurnSymbols = new FurnaceInfoBO();

      

            public int SaveProductionRecords(FurnaceInfoBO furnSymbols)

            {

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = db.conn;

                . . .


    • Marked as answer by labjac Tuesday, October 23, 2018 5:17 AM
    Monday, October 22, 2018 6:00 PM
  • Hallo

    Thank a million, do I need to declare a new instance at every method when using the cmd command?

    It's all now works..

    Thanks again.

    Regards,


    labjac

    Tuesday, October 23, 2018 5:17 AM
  • If you need cmd in other places, then introduce similar local declarations. You can also use ‘using’ if you want to free the resources promptly:

       using( var cmd = new SqlCommand( ) )
       {
          . . .
       }

    Note that if you want to reuse the connection, then you should not close it using ‘cmd.Connection.Close()’.

    The original variant with cmd as a member of the class can be considered too, but you have to remove the parameters using cmd.Parameters.Clear() before adding new ones.




    • Edited by Viorel_MVP Tuesday, October 23, 2018 7:11 AM
    Tuesday, October 23, 2018 7:11 AM