从datagridview选择行更新mysql数据库 [英] Update mysql database from datagridview selected row

问题描述

我有一个datagridview加载表单加载事件的mysql数据库表t_pi_clients，我有另一个选项卡，其中包含t_pi_client相应列的文本框，它能够将数据从fullrowselect模式获取到这些文本框中。现在我想在这些文本框值更改时更新数据库。到目前为止，我已经尝试了一些进程，并获得我的条目保存messageBox.show但没有任何事情发生在数据库，所以我希望有人可以帮助我，也许是缺少一些谢谢

  public partial class frmMain：Form 
 {
 
 MySqlConnection连接; 
 MySqlDataAdapter mySqlDataAdapter; 
 DataSet dt = new DataSet（）; 
 DataSet DS = new DataSet（）; 
 DataSet dg = new DataSet（）; 
 
 public frmMain（）
 {
 InitializeComponent（）; 
} 
 
 
 #region主要负载
 private void frmMain_Load（object sender，EventArgs e）
 {
 
 var connectionString = ConfigurationManager.ConnectionStrings [Pigen]。ConnectionString; 
 
 connection = new MySqlConnection（connectionString）; 
 
 if（this.OpenConnection（）== true）
 {
 mySqlDataAdapter = new MySqlDataAdapter（select * from t_pi_Clients，connection）; 
 DataSet DS = new DataSet（）; 
 mySqlDataAdapter.Fill（DS）; 
 kryptonDataGridView1.DataSource = DS.Tables [0]; 
 kryptonDataGridView1.Columns [0] .Visible = false; 
 
 
 mySqlDataAdapter = new MySqlDataAdapter（select * from t_pi_msg_charge_Rate，connection）; 
 DataSet dt = new DataSet（）; 
 mySqlDataAdapter.Fill（dt）; 
 kryptonDataGridView2.DataSource = dt.Tables [0]; 
 
 
 mySqlDataAdapter = new MySqlDataAdapter（select * from t_pi_client_deposits，connection）; 
 DataSet dg = new DataSet（）; 
 mySqlDataAdapter.Fill（dg）; 
 kryptonDataGridView3.DataSource = dg.Tables [0]; 
 
} 
 
 
} 
 //将所选行数据加载到文本框
 private void kryptonDataGridView1_DoubleClick（object sender，EventArgs e）
 {
 textboxClientCode.Text = kryptonDataGridView1.SelectedRows [0] .Cells [ClientCode]。Value.ToString（）; 
 txtboxClientName.Text = kryptonDataGridView1.SelectedRows [0] .Cells [ClientName]。Value.ToString（）; 
 txtboxPostalAddress.Text = kryptonDataGridView1.SelectedRows [0] .Cells [PostalAdd]。Value.ToString（）; 
 txtboxTelephone.Text = kryptonDataGridView1.SelectedRows [0] .Cells [Telephone]。Value.ToString（）; 
 txtboxFax.Text = kryptonDataGridView1.SelectedRows [0] .Cells [Fax]。Value.ToString（）; 
 txtboxEmailAddress1.Text = kryptonDataGridView1.SelectedRows [0] .Cells [EmailAdd1]。Value.ToString（）; 
 txtboxEmailAddress2.Text = kryptonDataGridView1.SelectedRows [0] .Cells [EmailAdd2]。Value.ToString（）; 
 txtboxEmailAddress3.Text = kryptonDataGridView1.SelectedRows [0] .Cells [EmailAdd3] Value.ToString（）; 
 txtboxWebsite.Text = kryptonDataGridView1.SelectedRows [0] .Cells [Website]。Value.ToString（）; 
 txtboxChargeRate.Text = kryptonDataGridView1.SelectedRows [0] .Cells [ChargeRate]。Value.ToString（）; 
 txtboxTotalDepo.Text = kryptonDataGridView1.SelectedRows [0] .Cells [TotalDeposit]。Value.ToString（）; 
 txtboxAccountBal.Text = kryptonDataGridView1.SelectedRows [0] .Cells [AccountBal]。Value.ToString（）; 
 txtboxEntrydate.Text = kryptonDataGridView1.SelectedRows [0] .Cells [EntryDate]。Value.ToString（）; 
 
 
} 
  

现在我试过这个方法来更新，不更新数据库

  private void kryptonbtnUpdate_Click（object sender，EventArgs e）
 {
 var connectionString = ConfigurationManager.ConnectionStrings [Pigen]。ConnectionString; 
 
使用（MySqlConnection Conn = new MySqlConnection（connectionString））
 
 
 if（Conn.State.ToString（）！=打开）
 {
 
} 
 else 
 {
 connection.Open（）; 
 
} 
 
 try 
 {
 DataTable changes =（（DataTable）kryptonDataGridView1.DataSource）.GetChanges（）; 
 if（changes！= null）
 {
 MySqlCommandBuilder mcb = new MySqlCommandBuilder（mySqlDataAdapter）; 
 mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand（）; 
 mySqlDataAdapter.Update（changes）; 
（（DataTable）kryptonDataGridView1.DataSource）.AcceptChanges（）; 
 mySqlDataAdapter.Update（DS）; 
} 
 
 // adapter.Update（rowsToUpdate）; 
 
 // mySqlDataAdapter.Update（DS）; 
 
 
 
 MessageBox.Show（Entry Saved）; 
} 
 
 catch（Exception ex）
 {
 MessageBox.Show（ex.Message）; 
} 
} 
  

解决方案

只是你需要做的一个伪代码

  string cmdText = @UPDATE t_pi_Clients 
 SET ClientName = @ClientName ，
 PostalAdd = @PostalAdd，
电话= @Telephone，
传真= @Fax，
 ....等.... 
 WHERE ClientCode = @ClientCode ; 
 using（MySqlConnection cn = new MySqlConnection（.....））
 using（MySqlCommand cmd = new MySqlCommand（cmdText，cn））
 {
 cn.Open（） ; 
 cmd.Parameters.AddWithValue（@ ClientName，txtboxClientName.Text）; 
 cmd.Parameters.AddWithValue（@ PostalAdd，txtboxPostalAddress.Text）; 
 .... etc etc ... 
 cmd.Parameters.AddWithValue（@ ClientCode，textboxClientCode.Text）; 
 int rowsUpdated = cmd.ExecuteNonQuery（）; 
 if（rowsUpdated> 0）
 {
 //从Form_Load 
 //中提取加载DataGridView1的代码，并创建一个可以从这里调用的可重用的方法
} 
} 
  

首先，您使用更新子句。我假设您的主键（唯一标识您的记录的字段）是 ClientCode 字段。

然后创建连接和命令。填写命令参数集合，使用文本所需的参数，从TextBox中获取值。

调用 ExecuteNonQuery 来存储值。

如果您成功，则需要更新或重新加载datagridview。最好的方法是使用文本框中的新值逐个设置当前行的gridview单元格，或者您可以简单地提取form_load中使用的代码来填充网格，并创建一个可以从按钮调用的新方法点击事件。 （但如果你有很多记录，可能会更慢）

i have a datagridview which loads mysql database table t_pi_clients on form load event,and i have another tab which contains textboxes of the respective columns of t_pi_client, which am able to get data from fullrowselect mode into those textboxes. now i want to update the database upon changes in the those textbox values. so far i've tried some process and gets my "entry saved" messageBox.show but nothing happens to database, so am hoping someone could help me out maybe am missing something thanks

public partial class frmMain : Form
{

    MySqlConnection connection;
    MySqlDataAdapter mySqlDataAdapter;
    DataSet dt = new DataSet();
    DataSet DS = new DataSet();
    DataSet dg = new DataSet();

    public frmMain()
    {
        InitializeComponent();
    }


    #region Main load
    private void frmMain_Load(object sender, EventArgs e)
    {

        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        connection = new MySqlConnection(connectionString);

        if (this.OpenConnection() == true)
        {
            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_Clients", connection);
            DataSet DS = new DataSet();
            mySqlDataAdapter.Fill(DS);
            kryptonDataGridView1.DataSource = DS.Tables[0];
            kryptonDataGridView1.Columns[0].Visible = false;


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_msg_charge_Rate", connection);
            DataSet dt = new DataSet();
            mySqlDataAdapter.Fill(dt);
            kryptonDataGridView2.DataSource = dt.Tables[0];


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_client_deposits", connection);
            DataSet dg = new DataSet();
            mySqlDataAdapter.Fill(dg);
            kryptonDataGridView3.DataSource = dg.Tables[0];

        }


    } 
          //loads selected row data into textboxes
      private void kryptonDataGridView1_DoubleClick(object sender, EventArgs e)
    {
        textboxClientCode.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientCode"].Value.ToString();
        txtboxClientName.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientName"].Value.ToString();
        txtboxPostalAddress.Text = kryptonDataGridView1.SelectedRows[0].Cells["PostalAdd"].Value.ToString();
        txtboxTelephone.Text = kryptonDataGridView1.SelectedRows[0].Cells["Telephone"].Value.ToString();
        txtboxFax.Text = kryptonDataGridView1.SelectedRows[0].Cells["Fax"].Value.ToString();
        txtboxEmailAddress1.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd1"].Value.ToString();
        txtboxEmailAddress2.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd2"].Value.ToString();
        txtboxEmailAddress3.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd3"].Value.ToString();
        txtboxWebsite.Text = kryptonDataGridView1.SelectedRows[0].Cells["Website"].Value.ToString();
        txtboxChargeRate.Text = kryptonDataGridView1.SelectedRows[0].Cells["ChargeRate"].Value.ToString();
        txtboxTotalDepo.Text = kryptonDataGridView1.SelectedRows[0].Cells["TotalDeposit"].Value.ToString();
        txtboxAccountBal.Text = kryptonDataGridView1.SelectedRows[0].Cells["AccountBal"].Value.ToString();
        txtboxEntrydate.Text = kryptonDataGridView1.SelectedRows[0].Cells["EntryDate"].Value.ToString();


    }

now i tried this method to update but doesn't update database

private void kryptonbtnUpdate_Click(object sender, EventArgs e)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        using (MySqlConnection Conn = new MySqlConnection(connectionString))


            if (Conn.State.ToString() != "Open")
            {

            }
            else
            {
                connection.Open();

            }

        try
        {
            DataTable changes = ((DataTable)kryptonDataGridView1.DataSource).GetChanges();
            if (changes != null)
            {
                MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
                mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
                mySqlDataAdapter.Update(changes);
                ((DataTable)kryptonDataGridView1.DataSource).AcceptChanges();
                mySqlDataAdapter.Update(DS);
            }

            //        adapter.Update(rowsToUpdate);

            //   mySqlDataAdapter.Update(DS);



            MessageBox.Show("Entry Saved");
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

解决方案

This is just a pseudocode of what you need to do

string cmdText = @"UPDATE t_pi_Clients
                 SET ClientName = @ClientName,
                     PostalAdd = @PostalAdd,
                     Telephone = @Telephone,
                     Fax = @Fax,
                     .... etc ....
                 WHERE ClientCode = @ClientCode";
using(MySqlConnection cn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cn))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@ClientName", txtboxClientName.Text);
    cmd.Parameters.AddWithValue("@PostalAdd", txtboxPostalAddress.Text);
    ....etc etc...
    cmd.Parameters.AddWithValue("@ClientCode", textboxClientCode.Text);
    int rowsUpdated = cmd.ExecuteNonQuery();
    if(rowsUpdated > 0) 
    {
        // extract the code that loads DataGridView1 from the Form_Load
        // and create a reusable method that you could call from here
    }
}

First you build an sql command text with the UPDATE clause. I assume that your primary key (the field that uniquely identifies your records) is the ClientCode field.

Then create the connection and the command. Fill the command parameters collection with the parameters required by your text taking the values from the TextBoxes.
Call the ExecuteNonQuery to store the values.

If you succeed then you need to update or reload your datagridview. The best approach would be setting one by one the gridview cells of the current row with the new values from the textboxes, or you could simply extract the code used in form_load to fill the grid and make a new method that you could call from the button click event. (But this could be slower if you have many records)

这篇关于从datagridview选择行更新mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！