Update database from 2 tables with c#

Question

Worked with phpMyAdmin and c#.

I wanted to update database in the phpMyAdmin with c#. But, I had an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from bucket inner join color on bucket.color_idcolor = color.idcolor' at line 1

In the phpMyAdmin, I had 2 tables in the same database: bucket and color. In the bucket table, I had column: idbucket, volume, and color_idcolor. While in the color table, I had column: idcolor and name.

In c#, I made an windows form, where the users can update the database. They can update volume (textbox) and color (combobox), and press Save button to store the update to the database. This is a few parts of my code:

private string idBucket;
private string volume;
private string color;

public void TransferData(string idBucket, string volume, string color)
{
    this.idBucket = idBucket;
    this.volume = volume;
    this.color = color;
}

MySqlConnection conector = new MySqlConnection();

public frmChangeBucket()
{
    InitializeComponent();
    conector.ConnectionString = "server=localhost; database=bucket; uid=root";
}

void ContentCboColor()
{
    conector.Open();

    MySqlCommand comand = new MySqlCommand();
    comand.Connection = conector;
    comand.CommandType = CommandType.Text;
    comand.CommandText = "select idcolor, name from color";

    MySqlDataAdapter da = new MySqlDataAdapter();
    DataTable dt = new DataTable();

    da.SelectCommand = comand;
    da.Fill(dt);
    cboColor.DisplayMember = "name";
    cboColor.ValueMember = "idcolor";
    cboColor.DataSource = dt;

    conector.Close();
}

private void btnSave_Click(object sender, EventArgs e)
{
    conector.Open();   

    MySqlCommand comand = new MySqlCommand();
    comand.Connection = conector;    
    comand.CommandType = CommandType.Text;  

    int result = 0;

    volume = txtVolume.Text;
    color = cboColor.SelectedValue.ToString();

    comand.CommandText = "update bucket set volume=@volume, [email protected], from bucket "
    + "inner join color on bucket.color_idcolor = color.idcolor "
    + "where idBucket=@idbucket";

    komen.Parameters.AddWithValue("@idbucket", idBucket);
    komen.Parameters.AddWithValue("@volume", volume);
    komen.Parameters.AddWithValue("@color.name", color);
    result += comand.ExecuteNonQuery();
    comand.Parameters.Clear();

    if (result > 0)
    {
        MessageBox.Show("You've changed " + result + " data");
    }
    else
    {
        MessageBox.Show("You haven't changed any data");
    }

    conector.Close();   
    this.Close();  
}  

In c#, I just displayed color name in combobox. First, users can display a few data from database. I used inner join to display color name. To store the update to database, do I have to inner join again? Please answer with the right code. Thanks for your help.

Answer 1

Instead trying to save text in the int field([email protected]) you should use:

"color_idcolor = color.idcolor"

basically:

    comand.CommandText = "update bucket set volume=@volume, [email protected] "
        + "where idBucket=@idbucket";
    comand.Parameters.AddWithValue("@idbucket", idBucket);
    comand.Parameters.AddWithValue("@volume", volume);
    comand.Parameters.AddWithValue("@color.idcolor", color);
    result += comand.ExecuteNonQuery();
    comand.Parameters.Clear();