C# Running 3 MYSQL insert queries with single button press - Data from textboxes and listview

I was wondering if you can help me. Firstly I wanted to say that this is my first application in C# that I am created so please accept that my code is not perfect.

What I want to achieve: My application will be a Car Repair Management App. I have a panel on which I have few texboxes and a list. With a single button click I want to make it so that data in text boxes will be stored in table called 'naprawa' and data from list to be stored in table called 'opisynapraw'. These tables are related with FK from 'naprawa' so I want that items from list when they are stored they will have FK of just created field in other table. (If that makes sense)

Please see table setup below: Naprawa

|--------------+----------------+------+-----+---------+----------------+
| Field        | Type           | Null | Key | Default | Extra          |
|--------------+----------------+------+-----+---------+----------------+
| Nr_Naprawy   | int(11)        | NO   | PRI | NULL    | auto_increment |
| data_naprawy | date           | YES  |     | NULL    |                |
| nr_rej       | varchar(45)    | YES  | MUL | NULL    |                |
| Przebieg     | int(15)        | YES  |     | NULL    |                |
|--------------+----------------+------+-----+---------+----------------+

Data in that table:

|------------+--------------+---------+-----------|
| Nr_Naprawy | data_naprawy | nr_rej  | Przebieg  |
|------------+--------------+---------+-----------|
|      1     |  2018-06-20  | na06ysa |   150000  |
|------------+--------------+---------+-----------|

Table opisynapraw:

+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| idopisynapraw | int(11)       | NO   | PRI | NULL    | auto_increment |
| Opis_Naprawy  | varchar(45)   | YES  |     | NULL    |                |
| Cena          | decimal(10,2) | YES  |     | NULL    |                |
| Nr_Naprawy    | int(11)       | YES  | MUL | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+

Example Data that I would like to see in that table:

|---------------+--------------+-------+-------------|
| idopisynapraw | Opis_Naprawy | Cena  | Nr_Naprawy  |
|---------------+--------------+-------+-------------|
|       1       |  notes abcd  |  30   |      1      |
|---------------+--------------+-------+-------------|
|       2       |  notes cdef  |   5   |      1      |
|---------------+--------------+-------+-------------|

What I want my code to do. I when I press Add button it will add the record from text boxes into 'naprawa'. Then take the ID of what was added and use it as FK for opisynapraw and add it along with data from listveiw to opisynapraw table.

Here is my code.

private void btnDodajNaprawe_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection myConn3 = new MySqlConnection(MyConnection);

                myConn3.Open();



                string querydoajnap = "INSERT INTO naprawa (data_naprawy,nr_rej,Przebieg) VALUES('" + dtaData.Value.Date.ToString("yyyy/MM/dd") + "', '" + txtNrRej.Text + "', '" + txtPrzebieg.Text + "');";
                MySqlCommand cmd = new MySqlCommand(querydoajnap, myConn3);

                MySqlConnection lastidconn = new MySqlConnection(MyConnection);
                lastidconn.Open();


                if (cmd.ExecuteNonQuery() == 1)
                {
                    MessageBox.Show("Dodane");


                    txtNrRej.Text = string.Empty;
                    txtPrzebieg.Text = string.Empty;

                }
                else
                {
                    MessageBox.Show("Blad");
                }




                String LastIDnapr = "select LAST_INSERT_ID();";
                MySqlCommand cmd1 = new MySqlCommand(LastIDnapr, lastidconn);
                MySqlDataReader IDRead = cmd1.ExecuteReader();
                int idnumber = 0;
                while (IDRead.Read())
                {
                    idnumber = IDRead.GetInt32(0);
                }

                MySqlCommand cmd2 = myConn3.CreateCommand();

                foreach (ListViewItem item in listView1.Items)
                {
                    // opisynapraw(Opis_Naprawy,Cena,Nr_Naprawy) VALUES (@val1 , @val2, '" + idnumber + "');";


                    cmd2.Parameters.AddWithValue("@val1",item.Text);
                    cmd2.Parameters.AddWithValue("@val2",item.SubItems[1].Text);
                    cmd2.CommandText = "INSERT INTO opisynapraw(Opis_Naprawy,Cena,Nr_Naprawy) VALUES (@val1 , @val2, '" + idnumber + "');";
                    cmd2.ExecuteNonQuery();
                }
                lastidconn.Close();
                myConn3.Close();


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

When I execute the above code it works for adding data to table 'Naprawa' but I think it will not take last used ID to relate to 'opisynapraw' table what brings following error.

For some reason I need some rep points to post images... I will type below what error message I'm having

Cannot add or update a child row; a foreign key contraint fails ('cars','opisynapraw', CONSTRAINT 'Nr_Naprawy'FOREIGN Key ('Nr_Naprawy') REFERENCES 'naprawa'('Nr_Naprawy') ON DELETE NO ACTION ON UPDATE NO ACTION)

I would very appreciate any help in resolving this mystery.

Thank You in advance

Rogupl

Solution

You need the INSERT into Naprawa to give you back the auto generated value in the same query. Add this into the end of your insert statement, after the ;

SELECT LAST_INSERT_ID();

Then to get it back, change the ExecuteNonQuery into ExecuteScalar - the value it returns will be the value you need - it returns it as an object, so either cast it or System.Convert it. (This requires you to remove your check that it inserted 1 row; but bear in mind it will throw an exception of it fails.)

Now remove the code relating to the LastIDnapr query, it should work fine.

Additional tips:

Your code is vulnerable to SQL injection attacks: use SQL parameters rather than string concatenation to construct a query.
MySqlConnection, MySqlCommand, and MySqlDataReader are all IDisposable so each should be in a using block.
Those connections are connecting to the same database so ensure that they don't overlap. This will be obvious once you're using using blocks.
Take a look at can we stop using AddWithValue