5 Replies - 7312 Views - Last Post: 01 September 2011 - 10:31 AM Rate Topic: -----

#1 junharefa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 30-August 11

How to insert data into MySql with two Tables in C#

Posted 31 August 2011 - 04:21 PM

I want to insert new data to the MySql database. I have two tables: tb_bahan_baku and tb_kategori.

tb_bahan_baku contains: kode_barang , nama, kode_kategori, satuan, harga, keterangan

tb_kategori contains: kode_kategori and is a foreign-key in the tb_bahan_baku table.

My problem is, I want to add new data to the tb_bahan_baku table and then choose one data in ComboBox of the kode_kategori which contains: KTG-01, KTG-02, KTG-03. The data that I choose of the kode_kategori only an option as a category.

What should I do to add new data to the tb_bahan_baku and also set the insert the data into tb_kategori table

Here the code that I have created, but still error:
private void btnSave_Click(object sender, EventArgs e)
    {
      string connectionSQL = "server=localhost;user id=root;password=;database=db_junisman_kulit;";
      MySqlConnection conn = new MySqlConnection(connectionSQL);


      try
      {
        MySqlCommand cmd = new MySqlCommand("INSERT INTO tb_bahan_baku(kode_barang,nama,kode_kategori,satuan,harga,keterangan)
VALUES ('" + textBox1.Text + "', '" + textBox2.Text + "', '" + comboBox2.SelectedValue + "', '" + comboBox2.SelectedValue + "', '" + textBox3.Text + "', '" + richTextBox1.Text + "')", conn);
        conn.Open();
        cmd.ExecuteNonQuery();
        MessageBox.Show("Data Telah disimpan!!");
      }

      catch (MySqlException ex)
      {

        MessageBox.Show("Can't connect to database\n" + ex.ToString());
      }
      finally
      {
        conn.Close();
      }
    }



Thank you for your help...

Is This A Good Question/Topic? 0
  • +

Replies To: How to insert data into MySql with two Tables in C#

#2 Gavisann  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 103
  • View blog
  • Posts: 382
  • Joined: 01-July 11

Re: How to insert data into MySql with two Tables in C#

Posted 31 August 2011 - 04:31 PM

What is the error you are getting? I assume it has something to do with ComboBox2.
Was This Post Helpful? 0
  • +
  • -

#3 junharefa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 30-August 11

Re: How to insert data into MySql with two Tables in C#

Posted 31 August 2011 - 07:20 PM

View PostGavisann, on 31 August 2011 - 04:31 PM, said:

What is the error you are getting? I assume it has something to do with ComboBox2.

This is the error I getting:

Cant Connect to database
MySql.Data.MySqlCLient.MySqlException: Cannot add or update a child row: a foreign key constraint fails ('db_junisman_kulit'.'tb_bahan_baku", CONSTRAINT 'tb_bahan_baku_ibfk_1' FOREIGN KEY ('kode_kategori') REFERENCES 'tb_kategori' ('kode_kateogri'))
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
.......

Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4490
  • View blog
  • Posts: 7,822
  • Joined: 08-June 10

Re: How to insert data into MySql with two Tables in C#

Posted 31 August 2011 - 08:32 PM

It's because you're trying to insert something that conflicts with your foreign key relationship. This isn't a C# problem, this is an issue with your SQL. I don't know your table definitions and relationships, so I can't say, and I don't understand the language you used naming the tables, so I can't guess.
Was This Post Helpful? 0
  • +
  • -

#5 junharefa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 30-August 11

Re: How to insert data into MySql with two Tables in C#

Posted 31 August 2011 - 10:54 PM

View PostCurtis Rutland, on 31 August 2011 - 08:32 PM, said:

It's because you're trying to insert something that conflicts with your foreign key relationship. This isn't a C# problem, this is an issue with your SQL. I don't know your table definitions and relationships, so I can't say, and I don't understand the language you used naming the tables, so I can't guess.

I'm using MySql Database
I Have a database "db_junisman_kulit" and contains two tables: tb_bahan_baku and tb_kategori

I create tb_bahan_baku table like this:
kode_barang (varchar 50) primary Key,
kode_kategori (varchar 50) Foreign Key,
nama (varchar 50),
satuan (varchar 50),
harga (double),
keterangan (text)


I create tb_kategori like this:
kode_kategori (varchar 50) Primary Key,
nama_kategori (varchar 50)


Then I set tb_bahan_baku and tb_kategori relarionship like this:
ALTER TABLE 'tb_bahan_baku' ADD FOREIGN KEY('kode_kategori')REFERENCES 'db_junisman_kulit'.'tb_kategori' (
'kode_kategori'
)ON DELETE CASCADE;



Pls tell me if the table relation that I've created is correct or not, so if I insert the new data does not conflict on the table.

for example.. I want to set kode_kategori in ComboBox that contains: KTG-01, KTG-02, KTG-03. If I want to insert new data then I can choose one of the kode_kategori data and insert to the MySql database.

Thanks in advance..
Was This Post Helpful? 0
  • +
  • -

#6 Da-Coder  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 12-August 11

Re: How to insert data into MySql with two Tables in C#

Posted 01 September 2011 - 10:31 AM

The insert statement looks to be fine. Not quite sure what the issue is. Only thing I can think of is with the database. Make sure you have the primary key linked properly with the child table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1