Inserting values into foreign Key MySQLCommand C#

Question

I been trying different things to get my code to work.

I'm pretty new with both c# and with DB. But the thing is i am trying to fix a db for a Music event where i add in band names and there members.

I have 2 tables. One with Band and one with Band Members. I am using MySQLCommand in visual studio C#

My button code for the insertion of code to a MySQL Class

    private void bt_LäggTill_Band_Medlem_Click(object sender, EventArgs e)
    {
        MySql mySQL = new MySql();
        MySqlConnection myConn = mySQL.GetServer();
       // string insert = @"Insert Into BandMedlemar(BNamn , MedlemsNamn)Values ('" + cb_Band_Medlemar.Text.ToString() + "',(Select BNamn from Band Where BNamn = '" + cb_Band_Namn.Text.ToString() + "'))";
    //    string insert = "Insert Into BandMedlemar(MedlemsNamn , BNamn) Select val.MedlemsNamn , f.BNamn from (Values ('" + cb_Band_Medlemar.Text.ToString() + "','" + cb_Band_Namn.Text.ToString() + "')) val(MedlemsNamn,BNamn) Left Join Band f Useing (BNamn)";
        string insert = 
            "Insert Into BandMedlemar(MedlemsNamn , BNamn) " +
        "Select '" + cb_Band_Medlemar.Text.ToString() + "' , BNamn " +
        "From Band Where BNamn='" + cb_Band_Namn.Text.ToString() + "'";
        int count = 1;
        string para = cb_Band_Medlemar.Text.ToString().Replace(" ", "");
        string para2 =cb_Band_Medlemar.Text.ToString();
        mySQL.Insert(myConn, insert, count, para, para2);
        Update();
    }

I tried a couple things i let them be there.

and then there is my SQL code that takes the data end sends in to the database

    public void Insert(MySqlConnection myConn , string text , int count , string para , string para2)
    {
        try
        {
            myConn.Open();
            MySqlCommand cmd = new MySqlCommand(text, myConn);
            //cmd.Connection = myConn;
            //cmd.CommandText = text;
            cmd.Prepare();
            string[] splited = para.Split('@');
            string[] splited2 = para2.Split('@');
            int number = 0;
            while (count != 0)
            {
                cmd.Parameters.AddWithValue("@" + splited[number], splited2[number]);
                count--;
                number++;
            }
            cmd.ExecuteNonQuery();
            myConn.Close();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            myConn.Close();
        }
    }

the code works with normal insert with multiple parameters as well.

for more info i post my Create table for the band members

CREATE TABLE BandMedlemar 
( BNamn varchar(20) NOT NULL default '',MedlemsNamn varchar(30) NOT NULL, 
  Info varchar(50) default NULL, PRIMARY KEY (BNamn), 
  CONSTRAINT BandMedlemar_ibfk_1 FOREIGN KEY (BNamn) REFERENCES Band (BNamn) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

As Primary key foreign key relationship is there, please help me how i could be able to add more then one member of a band

Please give me the proper solution as i am not getting it.

Send your error description. Uniqueness is usually a matter of Primary Key, not Foreign key — fly_ua, Dec 17 '14 at 12:01
With the current code i'm getting MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'Iron Maiden' for key 1 — Tarsakh, Dec 17 '14 at 12:02
The other error i'm getting in my tryes are MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`ad4855/BandMedlemar`, CONSTRAINT `BandMedlemar_ibfk_1` FOREIGN KEY (`BNamn`) REFERENCES `Band` (`BNamn`)) — Tarsakh, Dec 17 '14 at 12:04
Remove primary key from BNamn - (PRIMARY KEY (BNamn)) , Anyway you it is defaulted to '' and you will get PK violation in you will insert empty names — fly_ua, Dec 17 '14 at 12:05
Offtopic: There is no need to add `ToString()` to `Text` (`Text.ToString()`). Text member is already a string. — i486, Dec 17 '14 at 12:07
are we talking about the BNamn in BandMedlemar or in the Band Becouse BandMedlemar.BNamn is foreign key to Band.BNamn. — Tarsakh, Dec 17 '14 at 12:08
HEre's an example with parameters and the MySqlConnection http://stackoverflow.com/questions/16167924/c-sharp-with-mysql-insert-parameters — idstam, Dec 17 '14 at 12:37

score 0 · Accepted Answer · answered Dec 17 '14 at 12:09

0

BNamn is the primary key in the BandMedlemmar-table. Change the primary key to PRIMARY KEY (BNamn, MedlemsNamn) and try again.

And please do not concatenate strings to build SQL, you will get hacked. Use the parameters instead.

answered Dec 17 '14 at 12:09

idstam

2,848
1
21
30

i know that thats why im useing the Parameters.AddWithValue. But are you saying its not good enough? – Tarsakh Dec 17 '14 at 12:13
No you put placeholders in the sql that gets replaced with the parameters automatically. (I don't know the exact syntax for you MySql library.) – idstam Dec 17 '14 at 12:15
Oki if i understand this correct i put the code in and use that and the i get hackt with that and after that the parameters get in but that is to late right? becouse i thought the code didnt get sent to the db untill the cmd.ExecuteNonQuery(); – Tarsakh Dec 17 '14 at 12:19
Gp with what you have now and see if a new primary key solves your first problem. – idstam Dec 17 '14 at 12:30
yup works like a charm thank you for that. But im still a bit worryed with the other you said tho – Tarsakh Dec 17 '14 at 12:35

Inserting values into foreign Key MySQLCommand C#

1 Answers1