I'm a student on 3th year of IT and I've found so much help from this forum, I am stuck with my project creating program in C# so I need your help. I have 4 tables in my database but we will focus on 2 of them materijal(Material) table and Skart(Scrap) table:

materijal Table(original) which has:

idmaterijal(INT), 
naziv_materijala(VARCHAR), 
kolicina_materiala(FLOAT), 
mjerna_jedinica(VARCHAR), 
sifra_materijala(INT)

material Table(eng) :

idmaterial(INT) , 
name_material(VARCHAR) , 
quantity_material(FLOAT) , 
measuring_unit(VARCHAR) , 
code_material(INT) 

skart Table (original) which has:

idskart(INT), 
materijal_idmaterijal(INT)

scrap Table(eng) :

idscrap(INT), 
material_idmaterial(INT).

They are connected with Non-Identifying Relation 1:1. Now I have a Combo Box in my C# Form presented down below. I need to take from table Materials (name_material, quantity_material, mesuring_unit, code_material) and subtract the quantity that I insert in the textBox on my Form. For example I have 108.15 kg of HR in my Material table and I want to put 45 kg to scrap table , how do I do it in C#...

Pictures:

https://prnt.sc/hdvGuXJWNI0Q - My Diagram in MySQL (It is on my language but I translated every cell name for you guys)

https://prnt.sc/lgIRVTCOe670 - Materials Panel

https://prnt.sc/t1IGxpgJ8GHl - Choosing material via combo box in scraps panel

https://prnt.sc/6InfCPpezZga - Example on how I want to subtract

Down here I had an Idea so you would take from the picture skart panel https://prnt.sc/6InfCPpezZga and insert name_material via comboBox1 (it lists all of the materials from material table ... here I chose HR name of the material) and quantity_material via textBox7 in my case 45 with save button aka button 13 I need to update my material table by taking the quantity_material - textBox7 value and place in my dataGridView3 (witch is showing idscrap and material_idmaterial) code_material, name_material, quantity_material(but quantity I insert in textBox7) and measuring_unit , I know that placing it and showing with dataGridView I have to do the Inner Join or left join but I cant quite understand it ...

private void button13_Click(object sender, EventArgs e)
{
    konekcija.Open();
    //OleDbCommand komanda = new OleDbCommand();
    MySqlCommand komanda = new MySqlCommand();
    komanda.Connection = konekcija;
    komanda.CommandType = CommandType.Text;
    komanda.CommandText = "update materijal set naziv_materijala='"   comboBox1   "' where kolicina_materijala='"   textBox7.Text   "'";
    komanda.ExecuteNonQuery();
    konekcija.Close();
    comboBox1.Text = "";
    textBox7.Text = "";
    MessageBox.Show("Uspjesno ste unijeli izabrani Škart");
}

CodePudding user response：

The correct approach for code like this would be something like:

private void UpdateNameFromQuantityButton_Click(object sender, EventArgs e)
{
    //konekcija.Open();  //do not keep a connection hanging around; keep a connectionString instead

    var sql = "update materijal set naziv_materijala=@nm where kolicina_materijala=@km";

    using var konekcija = new MySqlConnection(connectionString);
    using var komanda = new MySqlCommand(sql, konekcija);
    konekcija.Open();
    komanda.Parameters.AddWithValue("@nm", materialNameComboBox.Text);
    komanda.Parameters.AddWithValue("@km", quantityTextBox.Text);
    var rowsAffected = komanda.ExecuteNonQuery();  //handy to capture this to know if nothing was changed (0 rows affected)
    konekcija.Close();

    materialNameComboBox.Text = "";
    quantityTextBox.Text = "";
    MessageBox.Show("Uspjesno ste unijeli izabrani Škart");
}

• use using on your command and connection; make a new connection object when you need it. Reusing the same one can cause issues with some databases. The number of MySqlConnection objects in your code is unrelated to the number of actual TCP connections to the database server because TCP connections are pooled by a mechanism elsewhere. Your use of Open and Close actually just leases and returns a pooled connection
• always use @parameters in your SQLs to put data into the command. Always*
• close your connection as soon as possible after you're done with it

^{* Unless the "data" is something like a table name or column name in which case it cannot be parameterized; try and avoid such scenarios - it's usually possible}