CODEWITHSUNDEEP
c#ms-accessdatagridview
SaraniO
SaraniO

Reputation: 607

How to update Access database using a datagridview in C#

I have a datagridview that bind with a datatable and using DataPropertyName the datagridview only shows 4 columns of database.
this is how I bind database to a datagridview:

string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings
["FeedLibraryConnectionString"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(StrCon);
OleDbDataAdapter DataA = new OleDbDataAdapter("Select * from SelectedFeeds", Connection);
DataTable DTable = new DataTable();
DataA.Fill(DTable);

frm.RationFeedsdataGridView.AutoGenerateColumns = false;

frm.RationFeedsdataGridView.Columns[1].Name = "نام خوراک";
frm.RationFeedsdataGridView.Columns[1].HeaderText = "نام خوراک";
frm.RationFeedsdataGridView.Columns[1].DataPropertyName = "Feed Name / Description";

frm.RationFeedsdataGridView.Columns[2].Name = "مقدار (کیلوگرم)";
frm.RationFeedsdataGridView.Columns[2].HeaderText = "مقدار (کیلوگرم)";
frm.RationFeedsdataGridView.Columns[2].DataPropertyName = "Quantity";

frm.RationFeedsdataGridView.Columns[3].Name = "درصد (خوراک)";
frm.RationFeedsdataGridView.Columns[3].HeaderText = "درصد (خوراک)";
frm.RationFeedsdataGridView.Columns[3].DataPropertyName = "Percent";

frm.RationFeedsdataGridView.DataSource = DTable;

So far nothing wrong,

now I want when user change the value of Columns[2], it's value automatically inserted (and after that updated) into database I tryed myself using this code but seems that it's wrong, the code is:

string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings
["FeedLibraryConnectionString"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(StrCon);
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Connection;
double Quantity = Convert.ToDouble(frm.RationFeedsdataGridView.CurrentCell.Value);
string FeedName = frm.RationFeedsdataGridView.CurrentRow.Cells[1].Value.ToString();
Connection.Open();
Cmd.CommandText="Update SelectedFeeds set Quantity=" +Quantity  'Where Feed Name / Description'= FeedName;
Cmd.ExecuteNonQuery();
Connection.Close();

the first problem is that my CommandText is wrong and I don't know how to fix it. what should I do?

Upvotes: 1

Views: 1385

Answers (1)

SaraniO
SaraniO

Reputation: 607

tanks to @LarsTech the answer is:

public static void UpdateRationFeeds(Form1 frm)
{
    string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings["FeedLibraryConnectionString"].ConnectionString;
    OleDbConnection Connection = new OleDbConnection(StrCon);
    OleDbCommand Cmd = new OleDbCommand();
    Cmd.Connection = Connection;
    double Quantity = Convert.ToDouble(frm.RationFeedsdataGridView.CurrentCell.Value);
    string FeedName = frm.RationFeedsdataGridView.CurrentRow.Cells[1].Value.ToString();
    Connection.Open();
    Cmd.CommandText = "Update SelectedFeeds set Quantity=@quantity Where SelectedFeeds.[Feed Name]=@feedname";
    Cmd.Parameters.Add("@quantity", OleDbType.Double).Value = Quantity;
    Cmd.Parameters.Add("@feedname", OleDbType.LongVarChar).Value = FeedName;
    Cmd.ExecuteNonQuery();
    Connection.Close();
}

Upvotes: 4

Related Questions