Results 1 to 3 of 3

Thread: MySQL generated columns and INSERT new data in Visual Studio 2017

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: MySQL generated columns and INSERT new data in Visual Studio 2017

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: MySQL generated columns and INSERT new data in Visual Studio 2017

    Thanks, JMC

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width