Updating Multiple Tables in Access Database

[BlackOps]

Hello guys,
i am implementing database of the Employees and their PassCards.

i am writing a code using C# from scratch (dont use AddDatabase file wizards..etc.) dont know is it nice idea or no? i am doing it because i wanted to also to understand how it works...

ok, now a problem is, i have placed some text boxes on the form, some buttons (next,previous,last...etc)

my database file has 7 tables. i use SQL statement to select from tables:


String sql2 = "SELECT e_employees.FirstName, e_employees.LastName, d_divisions.DivisionName, d_departments.DepName, t_titles.TitleName" +

" FROM t_titles INNER JOIN ((d_divisions INNER JOIN d_departments ON d_divisions.DivisionID = d_departments.DivisionID) INNER JOIN e_employees ON " +

"d_departments.DepartmentID = e_employees.DepartmentID) ON t_titles.TitleID = e_employees.TitleID;";



This gives me FirstName,Last name from e_employees table. DepName from d_departments table. DivisionName from d_divisions table. and Titlename from t_titles table.

problem is, i cannot SAVE data to multiple tables...

here is my Save Button Code:
//============SAVING DATA======================================

private void btnSave_Click(object sender, EventArgs e)

{

// If there is existing data, update it.

if (mEmployees.Rows.Count != 0)

{

mEmployees.Rows[m_rowPosition]["FirstName"] = txtFirstName.Text;

mEmployees.Rows[m_rowPosition]["LastName"] = txtLastName.Text;

//mEmployees.Rows[m_rowPosition]["DivisionName"] = txtLastName.Text;

mDataAdapter.Update(mEmployees);

}

}



i have uncommented line of code saving to DivisionName, because when it is on, code compiles successfully, but it gives SQL error(Dynamic SQL generation is not supported against multiple base tables.), that one cannot update multiple table.

could u say me another method of updating multiple tables?

thanks

ps.i have looked at many codes, but didnt find any nice sample code with such issue

attached file is architecture of my database

[saktya]

I Think you must learn about design pattern. Learn about Business Object and Data Access Object that will help you to manage your code nicely...

May be you must consider to use Dataset, one dataset can contains more than one table so it will help when you have relation between table.

[jmcilhinney]

There's no reason not to use the Data Source wizard. It doesn't limit you from using the "old" style of data access and it does give you extra functionality that is very useful. That said, if this is just a learning exercise then using just the "old" style of data access will certainly not hurt.

When your query involves a join the system cannot automatically generate the DELETE and UPDATE statements for you. It can only generate one SQL statement and it can't know which table to affect so it doesn't do anything. You have to write the SQL statements yourself to delete and update the appropriate records.

With some databases, like SQL Server, the DeleteCommand and UpdateCommand can contain more than one SQL statement, so you can write the SQL code with a single DataAdapter. Jet/Access does not support that feature so you would have to use one DataAdapter for each table.