updating a dataset

[steve_rm]

Hello,

When l got to update my database l get this error message "Missing the datacolumn 'DepartmentName' in the DataTable 'Employee' for the sourceColumn 'DepartmentName' ".

I have a database application. 2 tables. Department and Employee.
Department -> DepartmentCode (PK)
DepartmentName

Employee -> EmployeeID (PK)
Name
Surname
DepartmentCode (FK)

I have create this relationship in code using relationships in the form load event. see below:

private void frmEmployees_Load(object sender, System.EventArgs e)
		{
			try
			{
				cnn.Open();
				//Fill the DataSet with the Employee details
				OleDbCommand cmd = cnn.CreateCommand();
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = "SELECT * FROM Employee";
				da.SelectCommand = cmd;
				//Gets all the details for the database, e.g. Primary keys
				da.FillSchema(ds,SchemaType.Source,"Employee");
				da.Fill(ds,"Employee");
				
				//Fill the DataSet wtih the Department details
				cmd.CommandText = "SELECT * FROM Department";
				da.SelectCommand = cmd;
				da.FillSchema(ds,SchemaType.Source,"Department");
				da.Fill(ds,"Department");
				
				//Assign the parent column of the Department table and child column of the
				//employee table. Remember - DepartmentCode PK in Department table, and 
				//FK in the Employee Table.
				DataColumn parentColumn = ds.Tables["Department"].Columns["DepartmentCode"];
				DataColumn childColumn = ds.Tables["Employee"].Columns["DepartmentCode"];
				
				//Clear the relation of any data. Not really nessessary as we are creating from new.
				//However, if we are to reuse then we must clear all contents.
				ds.Relations.Clear();
				drEmployees = new DataRelation("EmployeeDetails",parentColumn,childColumn);
				ds.Relations.Add(drEmployees);
				
				//Display the details of the first employee in the database.
				txtEmployeeID.Text = ds.Tables["Employee"].Rows[0]["EmployeeNumber"].ToString();
				txtFirstName.Text = ds.Tables["Employee"].Rows[0]["FirstName"].ToString();
				txtSurname.Text = ds.Tables["Employee"].Rows[0]["Surname"].ToString();
				txtDateOfBirth.Text = ds.Tables["Employee"].Rows[0]["DateOfBirth"].ToString();
				txtAddress1.Text = ds.Tables["Employee"].Rows[0]["Address1"].ToString();
				txtAddress2.Text = ds.Tables["Employee"].Rows[0]["Address2"].ToString();
				cboDepartment.Text = ds.Tables["Employee"].Rows[0]["DepartmentCode"].ToString();

				string departmentCode = ds.Tables["Employee"].Rows[0]["DepartmentCode"].ToString();
				DataRow[] departmentRow = ds.Tables["Department"].Select("DepartmentCode = '" + departmentCode + "' ");
				string departmentName = departmentRow[0]["DepartmentName"].ToString();
				
				
				this.UpdateDepartments();//Call to display combo box information
			}	
			catch ( OleDbException ex )
			{
				MessageBox.Show(ex.Message);
			}
			catch ( Exception ex )
			{
				MessageBox.Show(ex.Message);
			}
		}

When l click the update button, l run this code and get an error on the update.

ds.Tables["Employee"].Rows[0]["FirstName"] = txtFirstName.Text;
ds.Tables["Employee"].Rows[0]["Surname"] = txtSurname.Text;
ds.Tables["Employee"].Rows[0]["DepartmentCode"] = departmentCode;

da.Update(ds,"Employee"); //Error ocurrs here

I hope someone can help me with this question. very important.

Thanks in advance,

Steve

[jmcilhinney]

What's the SQL for your update statement?

[steve_rm]

Hello,

I am not using a sql string, but updating uisng the command builder and using the data adapter to update the back-end database.

I have never counted a problem like this before, and l have done this many time. I am not sure, but l think it could have something to do with the ralationship l have created in the load event of the form.

Any ideas, l would be most grateful to you.

private void btnUpdateEmployee_Click(object sender, System.EventArgs e)
        {
 
            try
            {
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
 
                
        
                ds.Tables["Employee"].Rows[0]["FirstName"] = txtFirstName.Text;
                ds.Tables["Employee"].Rows[0]["Surname"] = txtSurname.Text;
                ds.Tables["Employee"].Rows[0]["DepartmentCode"] = txtdepartmentCode.Text;
 
                da.Update(ds,"Employee");
            }
            catch ( OleDbException ex )
            {
                MessageBox.Show(ex.Message);
            }
            catch ( Exception ex )
            {
                MessageBox.Show(ex.Message);
            }
        }

Thanks in advance,

Steve

[jmcilhinney]

Are you using the same DataTable that you filled originally or have you created a new DataSet altogether? If you're using a CommandBuilder then it will make certain assumptions based on the SQL query you performed. If you selected all columns then it will try to update all columns. If your DataSet and/or DataTable is the same object that you filled in the first place, I wouldn't have thought that this would be a problem because the DepartmentName column should be present. It sounds like the update SQL is trying to update that column but it is not present in the DataTable. If for some reason you don't want to update that column then you should be writing your own update SQL and not using a command builder.

If you are unsure of how to proceed, you could use a breakpoint at the call to Update and use the Watch window to examine the columns in the DataTable and the SQL commands. The command builder has methods to access the actual commands being used. Alternatively, you could use Debug.WriteLine to write certain values of interest to the Output window.

[steve_rm]

Thanks for you reply,

I will follow up with what you have said.

I will let you know about my findings.

Thanks,

Steve

[mar_zim]

have you tried putting ds.tables["Employees"].getchanges()?

[steve_rm]

Hello,

I managed to solve the problem, but l am not sure why this would work. I have inserted code to refill the dataset with the employee information. But l can't understand, why l have do do this again when i have filled it when the form loads.

See my code below.

private void btnUpdateEmployee_Click(object sender, System.EventArgs e)
        {
            OleDbCommand cmd = cnn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Employee";
 
            da.SelectCommand = cmd;
            da.Fill(ds,"Employee");
 
            try
            {
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
 
                
        
                ds.Tables["Employee"].Rows[0]["FirstName"] = txtFirstName.Text;
                ds.Tables["Employee"].Rows[0]["Surname"] = txtSurname.Text;
                ds.Tables["Employee"].Rows[0]["DepartmentCode"] = departmentCode;
                
                da.Update(ds,"Employee");
            }
            catch ( OleDbException ex )
            {
                MessageBox.Show(ex.Message);
            }
            catch ( Exception ex )
            {
                MessageBox.Show(ex.Message);
            }
        }

If you know the answer, please let me know.

Thanks in advance,

Steve