ToShop

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace StorageSystem
{
    public partial class ToShop : Form
    {
        public ToShop()
        {
            InitializeComponent();
        }

        private void ToShop_Load(object sender, EventArgs e)
        {
            String server = "sql146.main-hosting.eu";
            String database = "u687011352_tbet";
            String username = "u687011352_eruon";
            String password = "Super123";
            MySqlConnection con = new MySqlConnection("server=" + server + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";");
            MySqlDataAdapter sda = new MySqlDataAdapter("SELECT * FROM u687011352_tbet.Inventory INNER JOIN u687011352_tbet.kinds ON u687011352_tbet.Inventory.Item_kind_fk = u687011352_tbet.kinds.kind_id INNER JOIN u687011352_tbet.Certifications ON u687011352_tbet.Inventory.Item_certfication_fk = u687011352_tbet.Certifications.certification_id INNER JOIN u687011352_tbet.paints ON u687011352_tbet.Inventory.Item_paint_fk = u687011352_tbet.paints.paint_id INNER JOIN u687011352_tbet.Rarity ON u687011352_tbet.Inventory.Item_rarity_fk = u687011352_tbet.Rarity.Rarity_ID", con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            DGV_Items.DataSource = dt;

            //Layout
            DGV_Items.Columns[3].Visible = false;
            DGV_Items.Columns[4].Visible = false;
            DGV_Items.Columns[5].Visible = false;
            DGV_Items.Columns[6].Visible = false;
            DGV_Items.Columns[7].Visible = false;
            DGV_Items.Columns[8].HeaderText = "Kind";
            DGV_Items.Columns[9].Visible = false;
            DGV_Items.Columns[10].HeaderText = "Certification";
            DGV_Items.Columns[11].Visible = false;
            DGV_Items.Columns[12].HeaderText = "Paint";
            DGV_Items.Columns[13].Visible = false;
            DGV_Items.Columns[14].HeaderText = "Rarity";



        }

        private void DGV_Items_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void DGV_Items_SelectionChanged(object sender, EventArgs e)
        {
            if (DGV_Items.SelectedCells.Count > 0)
            {
                int selectedrowindex = DGV_Items.SelectedCells[0].RowIndex;

                DataGridViewRow selectedRow = DGV_Items.Rows[selectedrowindex];
                ItemID = Convert.ToString(selectedRow.Cells["Item_id"].Value);
                Name = Convert.ToString(selectedRow.Cells["Item_name"].Value);
                Quantity = Convert.ToString(selectedRow.Cells["Item_Quantity"].Value);
                Kind = Convert.ToString(selectedRow.Cells["Item_kind_fk"].Value);
                Certification = Convert.ToString(selectedRow.Cells["Item_certfication_fk"].Value);
                Paint = Convert.ToString(selectedRow.Cells["Item_paint_fk"].Value);
                Rarity = Convert.ToString(selectedRow.Cells["Item_rarity_fk"].Value);

            }
        }
        String ItemID;
        String Name;
        String Quantity;
        String Kind;
        String Certification;
        String Paint;
        String Rarity;
        private void btnMove_Click(object sender, EventArgs e)
        {
            int selectedrowindex = DGV_Items.SelectedCells[0].RowIndex;
            DataGridViewRow selectedRow = DGV_Items.Rows[selectedrowindex];
            Int32 ID = Convert.ToInt32(nudID.Value);
            Int32 newQuantity = Convert.ToInt32(Quantity) - Convert.ToInt32(nudQuantity.Value);
            String Desc = rtbDesc.Text;
            String server = "sql146.main-hosting.eu";
            String database = "u687011352_tbet";
            String username = "u687011352_eruon";
            String password = "Super123";
            MySqlConnection con = new MySqlConnection("server=" + server + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";");
            MySqlCommand cmdUpdateShopTable = new MySqlCommand("INSERT INTO u687011352_tbet.ShopInventory(ShopID, ShopItemName, ShopItemDesc, ShopItemQuantity,ShopItemPaint, ShopItemKind, ShopItemRarity, ShopItemCert) VALUES(@ID, @Name, @Desc, @Quantity, @paint, @kind, @rarity, @cert)", con);
            cmdUpdateShopTable.Parameters.AddWithValue("@ID", ID);
            cmdUpdateShopTable.Parameters.AddWithValue("@Name", Name);
            cmdUpdateShopTable.Parameters.AddWithValue("@Desc", Desc);
            cmdUpdateShopTable.Parameters.AddWithValue("@paint", Paint);
            cmdUpdateShopTable.Parameters.AddWithValue("@cert", Certification);
            cmdUpdateShopTable.Parameters.AddWithValue("@kind", Kind);
            cmdUpdateShopTable.Parameters.AddWithValue("@rarity", Rarity);
            MySqlCommand cmdUpdateInventoryQuantity = new MySqlCommand("UPDATE u687011352_tbet.Inventory SET Item_Quantity = @newQuan WHERE Item_id = @ID", con);
            cmdUpdateInventoryQuantity.Parameters.AddWithValue("@ID", ItemID);
            cmdUpdateInventoryQuantity.Parameters.AddWithValue("@newQuan", newQuantity);
            con.Open();
            MySqlDataAdapter cmdRecordExists = new MySqlDataAdapter("SELECT * FROM u687011352_tbet.ShopInventory WHERE ShopItemName = @Name AND ShopItemKind = @Kind AND ShopItemPaint = @Paint AND ShopItemRarity = @Rarity AND ShopItemCert = @Cert", con);
            cmdRecordExists.SelectCommand.Parameters.AddWithValue("@Name", Name);
            cmdRecordExists.SelectCommand.Parameters.AddWithValue("@Paint", Paint);
            cmdRecordExists.SelectCommand.Parameters.AddWithValue("@Cert", Certification);
            cmdRecordExists.SelectCommand.Parameters.AddWithValue("@Kind", Kind);
            cmdRecordExists.SelectCommand.Parameters.AddWithValue("@Rarity", Rarity);
            DataTable dt = new DataTable();
            cmdRecordExists.Fill(dt);
            MySqlCommand ItemExistAddQuan = new MySqlCommand("UPDATE u687011352_tbet.ShopInventory SET ShopItemQuantity = @Quan", con);
            MySqlDataReader reader = null;
            MySqlCommand cmdQuantity = new MySqlCommand("SELECT ShopItemQuantity FROM u687011352_tbet.ShopInventory", con);


            if (dt.Rows.Count == 1)
            {
                String result = cmdQuantity.ExecuteScalar().ToString();
                Int32 QuantitySHop = Convert.ToInt32(result) + Convert.ToInt32(nudQuantity.Value);
                ItemExistAddQuan.Parameters.AddWithValue("@Quan", QuantitySHop);
                cmdUpdateShopTable.Parameters.AddWithValue("@Quantity", QuantitySHop);
                ItemExistAddQuan.ExecuteNonQuery();
                cmdUpdateInventoryQuantity.ExecuteNonQuery();
            }
            else
            {
                Int32 QuantitySHop = Convert.ToInt32(nudQuantity.Value);
                cmdUpdateShopTable.Parameters.AddWithValue("@Quantity", QuantitySHop);
                cmdUpdateShopTable.ExecuteNonQuery();
                cmdUpdateInventoryQuantity.ExecuteNonQuery();
            }

            con.Close();
            this.Hide();
        }

        private void nudQuantity_ValueChanged(object sender, EventArgs e)
        {

        }
    }
}