如何使用引用单个主键的外键将多个图像插入MySQL数据库表 [英] How to insert multiple images into MySQL database table with foreign key referencing a single primary key

问题描述

我有一个包含两个表的MYSQL数据库，即property和propertyImages，property具有一个主键(propertyID)，该主键与其他列一起自动递增，propertyImages具有一个外键，即propertyID.

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.

当我将数据(例如属性名称，位置等)插入属性表时，用户选择的多个图像将插入到propertyImages表中，并且外键与属性表中最后插入的主键相同.

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.

当我插入数据时，属性表行被正确填充，没有问题，并且使用正确的外键将所选倍数的第一个图像输入到propertyImages表中，但是随后崩溃，并且没有保存其余图像或图像路径.

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!";

    }

这是错误消息:MySql.Data.dll中发生了类型'MySql.Data.MySqlClient.MySqlException'的异常，但未在用户代码中处理

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

其他信息:无法添加或更新子行:外部关键约束失败( realestatedb . propertyimage ，CONSTRAINT propertyimage_ibfk_1 外键( PropertyID )参考属性( PropertyID )在更新级联上删除级联)

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)

有类似问题的人的工作示例

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();
                            }
                        }
                    }

                }
            }

推荐答案

您的问题是这一行:

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

这将在第一次插入时起作用，因为 LAST_INSERT_ID 是适当的外键值.

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

但是在第二插入中， LAST_INSERT_ID 现在已更改为刚插入的记录(第一次插入)的ID值.

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

要解决此问题，您需要将 LAST_INSERT_ID 放入C#变量，然后将其传递到每个后续的SQL语句(即@ForeignKeyID而不是 LAST_INSERT_ID ).

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).

这意味着更改您的第一:

cmd.ExecuteNonQuery();

收件人:

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

其中insertedID是您在方法顶部声明的变量(可能是 int ).

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

然后您需要更改:

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();
    }
}

收件人:

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();
    }
}

这篇关于如何使用引用单个主键的外键将多个图像插入MySQL数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！