How to insert multiple images into MySQL database table with foreign key referencing a single primary key

Question

I have a MYSQL database with two tables, property and propertyImages, property has a primary key(propertyID) which is auto-increment along with other columns and propertyImages has a foreign key which is propertyID.

When I insert data into the property table such as propertyname, location etc, the multiple images selected by the user are inserted into the propertyImages table and the foreign key is be the same as the last inserted primary key in the property table.

When i insert the data the property table row is filled correctly with no problem and the first image of the multiple selected is entered into the propertyImages table with the correct foreign key but then it crashes and doesnt save the rest of the images or image paths.

        string constr = ConfigurationManager.ConnectionStrings["realestatedbAddConString"].ConnectionString;




        using (MySqlConnection con = new MySqlConnection(constr))
        {
            using (MySqlCommand cmd = new MySqlCommand("INSERT INTO property (PropertyName, PropertyNumBeds, PropertyType, PropertyPrice, PropertyFeatures, PropertyLocation, PropertyInformation, ImageName, ImageMap) VALUES (@PropertyName, @PropertyNumBeds, @PropertyType, @PropertyPrice, @PropertyFeatures, @PropertyLocation, @PropertyInformation, @ImageName, @ImageMap)"))
            {
                using (MySqlDataAdapter sda = new MySqlDataAdapter())
                {
                    cmd.Parameters.AddWithValue("@PropertyName", PropertyName);
                    cmd.Parameters.AddWithValue("@PropertyNumBeds", PropertyNumBeds);
                    cmd.Parameters.AddWithValue("@PropertyPrice", PropertyPrice);
                    cmd.Parameters.AddWithValue("@PropertyType", PropertyType);
                    cmd.Parameters.AddWithValue("@PropertyFeatures", PropertyFeatures);
                    cmd.Parameters.AddWithValue("@PropertyLocation", PropertyLocation);
                    cmd.Parameters.AddWithValue("@PropertyInformation", PropertyInformation);


                    string FileName = Path.GetFileName(MainImageUploada.FileName);
                    MainImageUploada.SaveAs(Server.MapPath("ImagesUploaded/") + FileName);


                    cmd.Parameters.AddWithValue("@ImageName", FileName);
                    cmd.Parameters.AddWithValue("@ImageMap", "ImagesUploaded/" + FileName);


                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }


        if (ImageUploada.HasFiles)
        {
            foreach(var file in ImageUploada.PostedFiles)
            {
                string FileName = Path.GetFileName(ImageUploada.FileName);
                ImageUploada.SaveAs(Server.MapPath("ImagesUploaded/") + file.FileName);

                using (MySqlConnection con = new MySqlConnection(constr))
                {
                    using (MySqlCommand cmd = new MySqlCommand("INSERT INTO propertyimage(MultipleImageName, MultipleImageMap, PropertyID) VALUES (@MultipleImageName, @MultipleImageMap, LAST_INSERT_ID()); "))
                    {


                        using (MySqlDataAdapter sda = new MySqlDataAdapter())
                        {

                            cmd.Parameters.AddWithValue("@MultipleImageName", file.FileName);
                            cmd.Parameters.AddWithValue("@MultipleImageMap", "ImagesUploaded/" + file.FileName);

                            cmd.Connection = con;
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
                    }
                }

            }
        }


        txtName.Text = "";
        txtPropFeat.Text = "";
        txtPropInfo.Text = "";
        txtPropLoc.Text = "";
        txtNumBeds.Text = "";
        txtPrice.Text = "";
        txtPropType.Text = "";

        Label1.Visible = true;
        Label1.Text = "Property Added to Database Successfully!";

    }

This is the error message: An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Additional information: Cannot add or update a child row: a foreign key constraint fails (realestatedb.propertyimage, CONSTRAINT propertyimage_ibfk_1 FOREIGN KEY (PropertyID) REFERENCES property (PropertyID) ON DELETE CASCADE ON UPDATE CASCADE)

EDIT: working example for people with similar problem

string PropertyName = txtName.Text;
            string PropertyFeatures = txtPropFeat.Text;
            string PropertyLocation = txtPropLoc.Text;
            string PropertyInformation = txtPropInfo.Text;
            string PropertyNumBeds = txtNumBeds.Text;
            string PropertyPrice = txtPrice.Text;
            string PropertyType = txtPropType.Text;
            long InsertedID;

            string constr = ConfigurationManager.ConnectionStrings["realestatedbAddConString"].ConnectionString;

            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("INSERT INTO property (PropertyName, PropertyNumBeds, PropertyType, PropertyPrice, PropertyFeatures, PropertyLocation, PropertyInformation, ImageName, ImageMap) VALUES (@PropertyName, @PropertyNumBeds, @PropertyType, @PropertyPrice, @PropertyFeatures, @PropertyLocation, @PropertyInformation, @ImageName, @ImageMap)"))
                {
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        cmd.Parameters.AddWithValue("@PropertyName", PropertyName);
                        cmd.Parameters.AddWithValue("@PropertyNumBeds", PropertyNumBeds);
                        cmd.Parameters.AddWithValue("@PropertyPrice", PropertyPrice);
                        cmd.Parameters.AddWithValue("@PropertyType", PropertyType);
                        cmd.Parameters.AddWithValue("@PropertyFeatures", PropertyFeatures);
                        cmd.Parameters.AddWithValue("@PropertyLocation", PropertyLocation);
                        cmd.Parameters.AddWithValue("@PropertyInformation", PropertyInformation);


                        string FileName = Path.GetFileName(MainImageUploada.FileName);
                        MainImageUploada.SaveAs(Server.MapPath("ImagesUploaded/") + FileName);


                        cmd.Parameters.AddWithValue("@ImageName", FileName);
                        cmd.Parameters.AddWithValue("@ImageMap", "ImagesUploaded/" + FileName);


                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        InsertedID = cmd.LastInsertedId;
                        con.Close();
                    }
                }
            }



            if (ImageUploada.HasFiles)
            {
                foreach(var file in ImageUploada.PostedFiles)
                {
                    string FileName = Path.GetFileName(ImageUploada.FileName);
                    ImageUploada.SaveAs(Server.MapPath("ImagesUploaded/") + file.FileName);


                    using (MySqlConnection con = new MySqlConnection(constr))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("INSERT INTO propertyimage(MultipleImageName, MultipleImageMap, PropertyID) VALUES (@MultipleImageName, @MultipleImageMap, @InsertedID); "))
                        {
                            using (MySqlDataAdapter sda = new MySqlDataAdapter())
                            {
                                cmd.Parameters.AddWithValue("@MultipleImageName", file.FileName);
                                cmd.Parameters.AddWithValue("@MultipleImageMap", "ImagesUploaded/" + file.FileName);
                                cmd.Parameters.AddWithValue("@InsertedID", InsertedID);

                                cmd.Connection = con;
                                con.Open(); 
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                    }

                }
            }

mjwills · Accepted Answer · 2017-07-10T13:25:55.647

Your problem is this line:

using (MySqlCommand cmd = new MySqlCommand("INSERT INTO propertyimage(MultipleImageName, MultipleImageMap, PropertyID) VALUES (@MultipleImageName, @MultipleImageMap, LAST_INSERT_ID()); "))

This will work on first insert, since LAST_INSERT_ID is the appropriate foreign key value.

But on the second insert, LAST_INSERT_ID has now changed to the ID value of the record you just inserted (the first insert).

To fix this, you need to get LAST_INSERT_ID into a C# variable, and then pass it into every subsequent SQL statement (i.e. @ForeignKeyID rather than LAST_INSERT_ID).

This will mean changing your first:

cmd.ExecuteNonQuery();

to:

cmd.ExecuteNonQuery();
insertedID = cmd.LastInsertedId;

where insertedID is a variable (likely int) that you declare at the top of your method.

You will then need to change:

using (MySqlCommand cmd = new MySqlCommand("INSERT INTO propertyimage(MultipleImageName, MultipleImageMap, PropertyID) VALUES (@MultipleImageName, @MultipleImageMap, LAST_INSERT_ID()); "))
{


    using (MySqlDataAdapter sda = new MySqlDataAdapter())
    {

        cmd.Parameters.AddWithValue("@MultipleImageName", file.FileName);
        cmd.Parameters.AddWithValue("@MultipleImageMap", "ImagesUploaded/" + file.FileName);

        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

to:

using (MySqlCommand cmd = new MySqlCommand("INSERT INTO propertyimage(MultipleImageName, MultipleImageMap, PropertyID) VALUES (@MultipleImageName, @MultipleImageMap, @InsertedID); "))
{


    using (MySqlDataAdapter sda = new MySqlDataAdapter())
    {

        cmd.Parameters.AddWithValue("@MultipleImageName", file.FileName);
        cmd.Parameters.AddWithValue("@MultipleImageMap", "ImagesUploaded/" + file.FileName);
        cmd.Parameters.AddWithValue("@MultipleImageMap", "ImagesUploaded/" + file.FileName);
        cmd.Parameters.AddWithValue("@InsertedID", InsertedID);

        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

Hi @mjwills, thanks for the quick reply. I understand what your saying but im still a bit confused on where I place the linked code in my method. — Aaron177, Jul 10 '17 at 13:21
Works perfectly thank you so much, I was close to giving up! — Aaron177, Jul 10 '17 at 14:41

How to insert multiple images into MySQL database table with foreign key referencing a single primary key

1 Answers1

Linked