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