are you sure you have a column named "Surname"? the following works for meusing System;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;class TestForm2 : Form
{
ComboBox cboEmployeeList; public TestForm2()
{
SqlDataAdapter da = new SqlDataAdapter("select * from employees",
"server=localhost;database=northwind;uid=sa;pwd=;"); DataSet ds = new DataSet();
da.Fill(ds,"Employees");
ds.Tables["Employees"].Columns.Add("FullName",System.Type.GetType("System.String"),"FirstName+' '+Lastname"); cboEmployeeList = new ComboBox();
cboEmployeeList.Location = new Point(100,100);
cboEmployeeList.DataSource=ds.Tables["Employees"];
cboEmployeeList.ValueMember="EmployeeID";
cboEmployeeList.DisplayMember="FullName";
Controls.Add(cboEmployeeList);
} public static void Main()
{
Application.Run(new TestForm2());
}
}
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;class TestForm2 : Form
{
ComboBox cboEmployeeList; public TestForm2()
{
SqlDataAdapter da = new SqlDataAdapter("select * from employees",
"server=localhost;database=northwind;uid=sa;pwd=;"); DataSet ds = new DataSet();
da.Fill(ds,"Employees");
ds.Tables["Employees"].Columns.Add("FullName",System.Type.GetType("System.String"),"FirstName+' '+Lastname"); cboEmployeeList = new ComboBox();
cboEmployeeList.Location = new Point(100,100);
cboEmployeeList.DataSource=ds.Tables["Employees"];
cboEmployeeList.ValueMember="EmployeeID";
cboEmployeeList.DisplayMember="FullName";
Controls.Add(cboEmployeeList);
} public static void Main()
{
Application.Run(new TestForm2());
}
}
{
string strConn="server=hunter;database=HumanResources;uid=sa;pwd=1";
SqlConnection sqlConn=new SqlConnection(strConn);
sqlConn.Open();
//daEmployees
this.daEmployees.SelectCommand=new SqlCommand();
this.daEmployees.SelectCommand.Connection=sqlConn;
this.daEmployees.SelectCommand.CommandText="Select EmployeeId,FirstName,SurName,DateOfBirth,DateOfHire,LastReview,"+
"Department from dbo.employees";
//.InsertCommand
this.daEmployees.InsertCommand=new SqlCommand();
this.daEmployees.InsertCommand.Connection=sqlConn;
this.daEmployees.InsertCommand.CommandText="Insert dbo.employees(FirstName,Surname,.DataOfBirth,DateOfHire,LastReview"+
",Department)"+ "values(@FirstName,@SurName,@DateOfBirht,@DateOfHire,@LastReview,@Department)";
this.daEmployees.InsertCommand.Parameters.Add("@FirstName",SqlDbType.VarChar,30,"FirstName");
this.daEmployees .InsertCommand.Parameters.Add("@SurName",SqlDbType.VarChar,30,"SurName");
this.daEmployees.InsertCommand.Parameters.Add("@DateOfBirth",SqlDbType.SmallDateTime,8,"DateOfBirth");
this.daEmployees.InsertCommand.Parameters.Add("@DateOfHire",SqlDbType.SmallDateTime,8,"DateOfHire");
this.daEmployees.InsertCommand.Parameters.Add("@LastReview",SqlDbType.SmallDateTime,8,"LastReview");
this.daEmployees.InsertCommand.Parameters.Add("@Department",SqlDbType.Int,4,"Department");
//.UpdateCommand
this.daEmployees.UpdateCommand=new SqlCommand();
this.daEmployees.UpdateCommand.Connection=sqlConn;
this.daEmployees.UpdateCommand.CommandText="update dbo.employees set FirstName=@FirstName"+
"SurName=@SurName,DateOfBirth=@DateOfBirth,DateOfHire=@DateOfHire,LastReview=@LastReview,Department=@Department"
+"where EmployeeID=@EmployeeId";
this.daEmployees.UpdateCommand.Parameters.Add("@FirstName",SqlDbType.VarChar,30,"FirstName");
this.daEmployees.UpdateCommand.Parameters.Add("@SurName",SqlDbType.VarChar,30,"SurName");
this.daEmployees.UpdateCommand.Parameters.Add("@DateOfBirth",SqlDbType.SmallDateTime,8,"DateOfBirth");
this.daEmployees.UpdateCommand.Parameters.Add("@DateOfHire",SqlDbType.SmallDateTime,8,"DateOfHire");
this.daEmployees.UpdateCommand.Parameters.Add("@LastReview",SqlDbType.SmallDateTime,8,"LastReview");
this.daEmployees.UpdateCommand.Parameters.Add("@Department",SqlDbType.Int,4,"Department");
//.DeleteCommand
this.daEmployees.DeleteCommand=new SqlCommand();
this.daEmployees.DeleteCommand.Connection=sqlConn;
this.daEmployees.DeleteCommand.CommandText="Delete dbo.Salaries where EmployeeID=@EmployeeID"+
"Delete dbo.Employees where EmployeeID=@EmployeeID";
this.daEmployees.DeleteCommand.Parameters.Add("@EmployeeID",SqlDbType.Int,4,"EmployeeID");
//three properties
this.daEmployees.AcceptChangesDuringFill=true;
this.daEmployees.TableMappings.Add("Table","Employees");
this.daEmployees.MissingSchemaAction=MissingSchemaAction.AddWithKey;
//daSalary
this.daSalary.SelectCommand=new SqlCommand();
this.daSalary.SelectCommand.Connection=sqlConn;
this.daSalary.SelectCommand.CommandText="Select EmployeeID ,salaryAppliedFrom,SalaryLevel From dbo.salaries";
this.daSalary.InsertCommand=new SqlCommand();
this.daSalary.InsertCommand.Connection=sqlConn;
this.daSalary.InsertCommand.CommandText="Insert dbo.salaries(EmployeeID,SalaryAppliedFrom,SalaryLevel)"
+"Values(@EmployeeID,@SalaryAppliedFrom,@SalaryLevel)";
this.daSalary.InsertCommand.Parameters.Add ("@EmployeeID",SqlDbType.Int,4,"EmployeeID");
this.daSalary.InsertCommand.Parameters.Add("@SalaryAppliedFrom",SqlDbType.SmallDateTime,8,"SalaryAppliedFrom");
this.daSalary.InsertCommand.Parameters.Add("@SalaryLevel",SqlDbType.Money,8,"SalaryLevel"); this.daSalary.UpdateCommand=new SqlCommand();
this.daSalary.UpdateCommand.Connection=sqlConn;
this.daSalary.UpdateCommand.CommandText="Update Salaries Set SalaryAppliedFrom=@NewSalaryAppliedFrom,"
+"SalaryLevel=@SalaryLevel where EmployeeId=@EmployeeID and SalaryAppliedFrom=@OldSalryAppliedFrom";
this.daSalary.UpdateCommand.Parameters.Add("@NewSalaryAppliedFrom",SqlDbType.SmallDateTime,8,"SalaryAppliedFrom");
this.daSalary.UpdateCommand.Parameters.Add("@SalaryLevel",SqlDbType.Money,8,"SalaryLevel");
this.daSalary.UpdateCommand.Parameters.Add("@EmployeeID",SqlDbType.Int,4,"EmployeeID");
this.daSalary.UpdateCommand.Parameters.Add("@OldSalaryAppliedFrom",SqlDbType.SmallDateTime,8,"SalaryAppliedFrom");
this.daSalary.UpdateCommand.Parameters["@OldSalaryAppliedFrom"].SourceVersion=DataRowVersion.Original;
this.daSalary.UpdateCommand.Parameters["@NewSalaryAppliedFrom"].SourceVersion=DataRowVersion.Current; //.DeleteCommand
this.daSalary.DeleteCommand=new SqlCommand();
this.daSalary.DeleteCommand.Connection=sqlConn;
this.daSalary.DeleteCommand.CommandText="Delete Salaries Where EmployeeID=@EmployeeID And SalaryAppliedFrom=@SalaryAppliedFrom";
this.daSalary.DeleteCommand.Parameters.Add("@EmployeeID",SqlDbType.Int,4,"EmployeeID");
this.daSalary.DeleteCommand.Parameters.Add("@SalaryAppliedFrom",SqlDbType.SmallDateTime,8,"SalaryAppliedFrom");
//three properties
this.daSalary.AcceptChangesDuringFill=true;
this.daSalary.TableMappings.Add("Table","Salaries");
this.daSalary.MissingSchemaAction=MissingSchemaAction.AddWithKey; //Departments
this.daDepartments.SelectCommand=new SqlCommand();
this.daDepartments.SelectCommand.Connection=sqlConn;
this.daDepartments.SelectCommand.CommandText="Select DepartmentID,Description From dbo.Departments";
this.daDepartments.AcceptChangesDuringFill=true;
this.daDepartments.TableMappings.Add("Table","Departments");
this.daDepartments.MissingSchemaAction=MissingSchemaAction.AddWithKey; try
{
this.daEmployees.Fill(dsDateSet);
this.daSalary.Fill(dsDateSet);
this.daDepartments.Fill(dsDateSet);
}
catch(SqlException oSqlException)
{
MessageBox.Show("The following Sql Server error occured:"+oSqlException.Message);
}
finally
{}
//this.dsDateSet.Tables["Employees"].Columns.Add("FullName",System.Type.GetType("System.String"),"FirstName+' '+Surname");
this.dsDateSet.Relations.Add("FK_Salaries_Employees",dsDateSet.Tables["Employees"].Columns["EmployeeID"],
dsDateSet.Tables["Salaries"].Columns["EmployeeID"],true);
this.dsDateSet.Relations.Add("FK_Employees_Departments",dsDateSet.Tables["Departments"].Columns["DepartmentID"],
dsDateSet.Tables["Employees"].Columns["Department"],true); this.dvEmployees.Table=dsDateSet.Tables["Employees"]; this.cboEmployeeList.DataSource=dsDateSet.Tables["Employees"];
this.cboEmployeeList.ValueMember="EmployeeID";
//this.cboEmployeeList.DisplayMember="FullName";
this.cboDepartment.DataSource=dsDateSet.Tables["Departments"];
this.cboDepartment.ValueMember="DepartmentID";
this.cboDepartment.DisplayMember="Description"; this.dvSalaries.Table=dsDateSet.Tables["Salaries"];
this.dvSalaries.RowFilter="EmployeeID=-1"; //dataGridTableStyle
DataGridTableStyle dgSalariesTableStyle=new DataGridTableStyle();
dgSalariesTableStyle.MappingName="Salaries";
this.dgSalaries.DataSource=dvSalaries;
this.dgSalaries.TableStyles.Add(dgSalariesTableStyle);
this.dgSalaries.TableStyles["Salaries"].GridColumnStyles["EmployeeID"].Width=0;
this.grpEmployeeDetails.Enabled=false;