CODEWITHSUNDEEP
c#mysqlasp.netsql-updatemariadb
Phantastic
Phantastic

Reputation: 7

Why I encountered error on MySQL 'UPDATE' command?

currently my Class Diagram looks like this:

enter image description here

and I would like to Update ALL my product details (prodName, prodDesc, prodCategory, prodDesc,manufacturer, supplier, stockLevel, reorderLevel, unitPrice) except prodID.

so I using this SQL query:

UPDATE Product
    SET prodName = @prodName,
        prodCategory = @prodCategory,
        prodDesc = @prodDesc,
        manufacturer = @manufacturer,
        supplier = @supplier,
        stockLevel = @stockLevel,
        reorderLevel = @reorderLevel,
        unitPrice = @unitPrice
FROM product a, supply_item b, supply c
WHERE a.prodID = @prodID AND
      @prodID = b.prodID AND
      b.supplyID = c.supplyID;

but it unable to works, is my query correct?

This is the section of my code

protected void editSubmit_Click(object sender, EventArgs e)
    {
        MySqlConnection conn = new MySqlConnection(sqlConn);
        string sql = "UPDATE Product SET prodName = @prodName, prodCategory = @prodCategory, prodDesc = @prodDesc, manufacturer = @manufacturer, supplier = @supplier, stockLevel = @stockLevel, reorderLevel = @reorderLevel, unitPrice = @unitPrice FROM product a, supply_item b, supply c WHERE a.prodID = @prodID AND @prodID = b.prodID AND b.supplyID = c.supplyID;";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        conn.Open();
        cmd.Parameters.AddWithValue("@prodName", txtProductName.Text);
        cmd.Parameters.AddWithValue("@prodCategory", txtCategory.Text);
        cmd.Parameters.AddWithValue("@prodDesc", txtProductDesc.Text);
        cmd.Parameters.AddWithValue("@manufacturer", txtManufacturer.Text);
        cmd.Parameters.AddWithValue("@supplier", txtSupplier.Text);
        cmd.Parameters.AddWithValue("@stockLevel", txtStockLvl.Text);
        cmd.Parameters.AddWithValue("@reorderLevel", txtReorderLvl.Text);
        cmd.Parameters.AddWithValue("@unitPrice", txtPrice.Text);
        cmd.Parameters.AddWithValue("@prodID", txtProductID.Text); 
        cmd.ExecuteNonQuery();
        conn.Close();
    }

Originally, I used this query,

"UPDATE Product SET prodName = @prodName, prodCategory = @prodCategory, prodDesc = @prodDesc, manufacturer = @manufacturer, supplier = @supplier, stockLevel = @stockLevel, reorderLevel = @reorderLevel, unitPrice = @unitPrice WHERE prodID = @prodID"

but it doesn't show any error, and I unable to update any values on my product Any idea how to update my product? Thanks 🙏

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

The equivalent in MySQL would seem to be:

UPDATE product p JOIN
       supply_item si 
       ON p.prodId = si.prodId JOIN 
       supply s
       ON s.supplyID = si.supplyID;
    SET p.prodName = @prodName,
        p.prodCategory = @prodCategory,
        p.prodDesc = @prodDesc,
        p.manufacturer = @manufacturer,
        p.supplier = @supplier,
        p.stockLevel = @stockLevel,
        p.reorderLevel = @reorderLevel,
        p.unitPrice = @unitPrice
WHERE p.prodID = @prodID;

Notes:

  • Never use commas in the FROM clause.
  • Always use proper, explicit, standard, readable JOIN syntax.
  • Use meaningful table aliases (such as abbreviations for table names), not arbitrary letters.

Upvotes: 1

Related Questions