CODEWITHSUNDEEP
c#mysqlsql.net
Grace Michelle
Grace Michelle

Reputation: 223

Update database from 2 tables with c#

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.

Upvotes: 3

Views: 916

Answers (4)

Kaja Mydeen
Kaja Mydeen

Reputation: 585

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

In update query from keyword will not come and update query you can't use join queries.

Upvotes: 0

Matt
Matt

Reputation: 27001

One issue is the from bucket in your update statement - it needs to be removed. Another issue is the join - try to replace the join by a subquery:

update bucket 
    set volume=@volume, 
        [email protected]    
where idBucket=@idbucket and 
bucket.color_idcolor in (select color.idcolor from color)

Put this into the command text, and it should look like this:

comand.CommandText = 
    "update bucket " +
    "set volume=@volume, " +
    "    [email protected] " +   
    "where idBucket=@idbucket " +
    "and bucket.color_idcolor in (select color.idcolor from color)";

Then replace

komen.Parameters.AddWithValue("@color.name", color);

by:

komen.Parameters.AddWithValue("@color.idcolor", colorId);

Finally, since you have the color name rather than the color id, the proper color id can be looked up earlier by a separate command select color.idcolor from color where color.name = @color.name.

The code for this could be like:

string SelectItem(MySqlConnection conn, string Name)
{   
 string result="";  
 using (MySqlCommand cmd = new MySqlCommand()) 
 {
  cmd.Connection = conn;
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "select top 1 color.idcolor from color where color.name = @color.name";
  cmd.Parameters.AddWithValue("@color.name", Name);
  result=cmd.ExecuteScalar().ToString();
 }
 return result;     
}

With this function you can obtain the color Id

var colorId = SelectItem(conector, color);

N.B.: This function assumes that the database connection is already open.

Upvotes: 0

Irshad
Irshad

Reputation: 3131

Your command should be;

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

Remove the , from bucket after [email protected] and the INNER JOIN should come after UPDATE and before SET

Upvotes: 0

virusivv
virusivv

Reputation: 347

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

Upvotes: 2

Related Questions