MySQL generated columns and INSERT new data in Visual Studio 2017

Hello,

I have a MySQL database connected to Visual Studio 2017 Community Edition software. I created two generated columns in MySQL database: - PlanProfit = PlanSales - PlanCost and PlanPercent = PlanSales/PlanCost * 100. PlanProfit is saved as Virtual Expression and PlanPercent is saved as a Stored procedure.

I am trying to INSERT data into the Plan table using parameters, but I received the following error message that @planprofit and @profitpercent are not allowed, once i add a new record and press the save button. The Exception Unhandled error message: -

Code:

---

MySql.Data.MySqlClient.MySqlException: 'The value specified for generated column 'PlanProfit' in table 'plans' is not allowed.'

---

The vb.net code is shown below: -

Code:

---

  Private Sub BtnPlanSave_Click(sender As Object, e As EventArgs) Handles BtnPlanSave.Click
     
        InsertPlans()
    End Sub

Public Sub InsertPlans()

        Dim sql As String = "INSERT INTO plans (`PlanID`, PlanCost`,`PlanSales,’PlanProfit,’PlanPercent’,’PlanNotes’) values (@planid,@plancost,@plansales,@planprofit,@planpercent,@plannotes)"
        con = New MySqlConnection("Server=localhost;Port=3306;Database=mydatabase;userid=root;password=,mypassword;persist security info=True")
        con.Open()
        Dim cmd = New MySqlCommand(sql, con)

        cmd.Parameters.AddWithValue("@planid", Me.txtPlanID.Text)
        cmd.Parameters.AddWithValue("@plancost", Me.txtPlanCost.Text)
        cmd.Parameters.AddWithValue("@plansales", Me.txtPlanSales.Text)
        cmd.Parameters.AddWithValue("@planprofit", “PlanSales – PlanCost”)
        cmd.Parameters.AddWithValue("@planpercent", “PlanSales /PlanCost * 100”)
        cmd.Parameters.AddWithValue("@plannotes", Me.txtPlanNotes.Text)
       

        cmd.ExecuteNonQuery()
        con.Close()


    End Sub

---

I tried to create a stored procedure for the PlanProfit in Visual Studio, but I received an error message that a duplicate column name "PlanProfit" exists. The stored expression is:-

Code:

---

ALTER TABLE plans
ADD COLUMN PlanProfit DOUBLE
GENERATED ALWAYS AS (PlanSales-PlanCost) STORED;

---

Any assistance to help me with the INSERT new data in the Plan table and save the new records in Visual Studio, will be greatly appreciated.

Thanks

Of course they're not allowed. Those two columns contain data generated from values in other columns, so how could your setting their values be meaningful? Change your INSERT statement and remove those two columns from it. When you insert data to the other columns that are the source for those two, they will be populated automatically.

Thanks, JMC