How to update Access database using a datagridview in C#

Question

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?

Always use parameters. If a field name has a space or is a keyword, you have to put brackets around it, so [Feed Name / Description]. BTW, that's a horrible column name. — LarsTech
– LarsTech, Commented May 11, 2016 at 17:22
As larstexh said you should use paramatera as your quotatuons are wrong — Shon
– Shon, Commented May 11, 2016 at 17:47
@LarsTech tnx, can you explain it to me how I use parameters? — SaraniO
– SaraniO, Commented May 11, 2016 at 17:57

SaraniO · Accepted Answer · 2016-05-12 16:17:52Z

4

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

edited May 12, 2016 at 16:17

answered May 11, 2016 at 18:57

SaraniO

6071 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LarsTech Over a year ago

set Quantity = @quantity where... then Cmd.Parameters.Add("@quantity", ...

Collectives™ on Stack Overflow

How to update Access database using a datagridview in C#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related