multiple Update query after Filter in datagridview

AsthreA

Member
Joined
Oct 11, 2014
Messages
7
Programming Experience
Beginner
I'm new to c# and i'm using vs 2013 and access database..my i need to do multiple update records after I filtered the records in datagridview

in my database table I have these fields ID(PK/autonumber), EID,Date,Day,Daystatus..
In my form I have a button and it's fucntion is filter by date using bidingsource.fiter and this was the result

EID------Date-----------Day---Daystatus
10175--10/11/2014---sat--------regular
10176--10/11/2014---sat--------regular
10177--10/11/2014---sat--------regular
10178--10/11/2014---sat--------regular

what I want to happen is when I click another button called "Apply to all" I want to Update all the daystatus to "Legal Holiday" of the filtered records..I know how to update one by one using simple update..but I need to do the multiple update..

I tried to make a code but it was updating all the records not the filtered only here's my code hope someone can help me

try
{
OleDbCommand command = new OleDbCommand();
connection.Open();
command.Connection = connection;
string query = "SELECT EID From EmployeeTable";
command.CommandText = query;
String a = "Legal Holiday";
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{

OleDbCommand command2 = new OleDbCommand();
command2.Connection = connection;
//String counter2;
//String status = "Absent";

for (var i = 0; i < 1; i++)
{
command2.Connection = connection;
string query2 = "update EmployeeData set DayStatus='" + a + "'where EID='" + reader["EID"] + "'";
command2.CommandText = query2;
command2.ExecuteNonQuery();

}
}
MessageBox.Show("Successfully");



}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("error" + ex);
}
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,143
Location
Sydney, Australia
Programming Experience
10+
The point of using the BindingSource.Filter is to expose only specific records via the BindingSource. If you then want to process each of the exposed records, you simply loop through the items of the BindingSource, e.g.
foreach (DataRowView row in myBindingSource)
{
    row["SomeColumn"] = someValue;
}
Those changes are propagated to the underlying DataTable so you then simply use the same data adapter that filled the DataTable in the first place to update the database with the changes:
myDataAdapter.Update(myDataTable);
If you want to update existing records then you need an UPDATE statement, which can be generated automatically by a command builder or you can create yourself and store in the UpdateCommand of the data adapter.
 
Top Bottom