Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
my 1st table

SQL
CREATE TABLE [dbo].[Department] (
    [Dept_ID]   INT          IDENTITY (10, 10) NOT NULL,
    [Dept_Name] VARCHAR (50) NOT NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Dept_ID] ASC),
    CONSTRAINT [FK_Department_Employee] FOREIGN KEY ([Dept_ID]) REFERENCES [dbo].[Department] ([Dept_ID])
);



2nd table

SQL
CREATE TABLE [dbo].[Employee] (
    [Dept_ID]   INT          NOT NULL,
    [Dept_Name] VARCHAR (50) NOT NULL,
    [Emp_ID]    INT          IDENTITY (1, 1) NOT NULL,
    [Emp_Name]  VARCHAR (50) NOT NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Emp_ID] ASC)
);



Data of Department table-
Dept_ID-----Dept_Name
10----------IT
20----------CS
30----------EE

I want to enter data in table Employee such that if I select the Dept_Name from the dropdownlist and enter the Emp_Name, it could be save like Emp_Name, Emp_ID, Dept_Name and Dept_ID; and Dept_ID will be to the corresponding Dept_Name from dropdownlist items.
Posted
Comments
Thanks7872 25-Aug-15 2:22am    
And? What is the issue? What have you tried?

Run an insert query on the employee table -
VB
String query = "INSERT INTO dbo.employee (dept_name,emp_name) VALUES (@deptname, @employee_name)";

command.Parameters.AddWithValue("@deptname",dropdown1.value)
command.Parameters.AddWithValue("@employee_name",txtEmpName.value)

command.ExecuteNonQuery();
 
Share this answer
 
First of all I would advice not to save the department name in the employee table. You already have a relationship between the tables (Dept_ID) so you can always fetch the referenced department name by using a join. If you would save the department name to the employee, when the department name changes, you would need also to change the name in all corresponding employee rows.

Conserning the saving, to add to Abhinav S's[^] answer, the thing is that when you fetch the data into your user interface, you need to fetch both department name and id. This information could be stored inside a datatable, collection etc but the important thing is that when you bind the data to the drop down list, you need to have access to the id based on the text value the user selected.

Depending on the technology you use, some controls provide ability to bind display member (department name) and data member (department id) separately or you can bind the whole object (i.e. datarow etc) for a specific drop down item.

When saving you fetch the selected key value and use it in the statement, like in the example in solution 1, but without department name, so something like

C#
string query = "INSERT INTO dbo.employee (dept_id,emp_name) VALUES (@deptid, @empname)";
 
command.Parameters.AddWithValue("@deptid",deptid_value)
command.Parameters.AddWithValue("@empname",empname_value)
 
command.ExecuteNonQuery();
 
Share this answer
 
C#
String query = "INSERT INTO dbo.employee (Emp_Name, Emp_ID, Dept_Name,Dept_ID) VALUES (@Emp_Name,Dept_Name,Dept_ID)";

command.Parameters.AddWithValue("@Emp_Name",txtEmpName.Text)
command.Parameters.AddWithValue("@Dept_Id",ddldept_Id.SelectedItem.Value) or
//command.Parameters.AddWithValue("@Dept_Id",ddldept.SelectedValue)
command.Parameters.AddWithValue("@Dept_Name",ddldept_Name.SelectedItem.Text)
// emp_id is identity so not require to pass
command.ExecuteNonQuery();
// You have to store , only dept_id is enough to store im emp table .by means of inner join u can get the dept name..:-)
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900