How can I insert value on my Foreign Key Column if the relationship is one-to-many

Question

I have two table the first table name is tbl_Account have a fieldName UserID, Username, Password and the second table name is tbl_Item have a fieldname ItemID, ItemName, UserID. How can I get the value of UserID in tbl_Account and put it to tbl_Item row UserID? Defends to the user who login.

    public void InsertRecord()
            {
                Connection connection = new Connection();
                try
                {
                    string sql = "INSERT INTO tbl_Item VALUES (@itemId, @itemName, @logId)";
                    MySqlConnection conn = new MySqlConnection(connection.ConnectionString);
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@itemId", GenerateID());
                    cmd.Parameters.AddWithValue("@itemName", ItemName);
                    cmd.Parameters.AddWithValue("@logId", GenerateIDs());
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    MessageBox.Show("Update Successfully", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception e)
                {
                    MessageBox.Show("An error occurred: " + e, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

//my code for btnAdd
private void button1_Click(object sender, EventArgs e)
        {
            Item item = new Item();

            item.ItemID = item.GenerateID();
            item.ItemName = textBox2.Text;
            item.Account.UserID = item.Account.GenerateID();
            item.InsertRecord();
        }

here you go https://stackoverflow.com/questions/14246744/executescalar-vs-executenonquery-when-returning-an-identity-value — PKCS12, Sep 05 '18 at 12:15

score 0 · Answer 1 · answered Sep 05 '18 at 12:25

0

You can reference UserID from tbl_Account table in tbl_Item table by writing the following code on your SQL side.

 FOREIGN KEY (UserID) REFERENCES tbl_Account(UserID).

Hope that answers your question.

answered Sep 05 '18 at 12:25

heywhydot16

15
1
6

`string sql = "INSERT INTO tbl_Item VALUES (@itemId, @itemName, @logId) FOREIGN KEY (UserID) REFERENCES tbl_Account(UserID)";` like this? – Swimming Pool Sep 05 '18 at 12:41
It said the" You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version" – Swimming Pool Sep 05 '18 at 12:44
no this won't work, you only need to define the Foreign while designing your database schema and leave SQL to do the rest while into the database. Can you post more code? – heywhydot16 Sep 07 '18 at 14:45

score 0 · Answer 2 · answered Sep 05 '18 at 13:01

public void InsertRecord(Account loggedInUser)
            {
                Connection connection = new Connection();
                try
                {
                    string sql = "INSERT INTO tbl_Item VALUES (@itemId, @itemName, @logId)";
                    MySqlConnection conn = new MySqlConnection(connection.ConnectionString);
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@itemId", ItemId)); // Why you called GenerateID() second time? It was generated in button1_Click
                    cmd.Parameters.AddWithValue("@itemName", ItemName);
                    cmd.Parameters.AddWithValue("@logId", loggedInUser.UserID);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    MessageBox.Show("Update Successfully", "Update Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception e)
                {
                    MessageBox.Show("An error occurred: " + e, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

//my code for btnAdd
private void button1_Click(object sender, EventArgs e)
        {
            Item item = new Item();

            item.ItemID = item.GenerateID();
            item.ItemName = textBox2.Text;
            //item.Account.UserID = item.Account.GenerateID(); do you really need this?
// someLoggedInUser is Account
            item.InsertRecord(someLoggedInUser);
        }

It show that "The name "someLoggedInUser" does not exist in the current context." — Swimming Pool, Sep 06 '18 at 02:11
You should know who is logged in. You should pass Account record of logged in user or if you have only Id of that user you can pass Id, change `InsertRecord` signature to accept Is not Account and change assigning `logId` parameter. — Pablo notPicasso, Sep 06 '18 at 08:59

How can I insert value on my Foreign Key Column if the relationship is one-to-many

2 Answers2