inserting with referance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inserting with referance

Author

Topic

acyilmaz
Starting Member

4 Posts

Posted - 2010-11-21 : 06:53:45

Hi everyone;

I wantto insert data to a table but there is columns that foreigned with another table how can I insert data to my table?

For example "Students" and "Departments" are my tables. Students table keeping the information of students like student id name surname department faculty. Departments table is keeping department names and their id. students table's department column has integer id number and when I use select query I call it like "select a.name , a.surname, b.departmentName from Students a, Departments b where a.department=b.DepartmentID". Now, my user is entering a student record he filled students name, surname,faculty and department(department name from dropdownlist). when he complete and click add button program sends query but table doesnt accept the department columns because it must be integer.How can i solve this in 1 query?is there any method like "select" query (from student a , department b where a.stdep=b.deptID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 07:24:48

you need to create a procedure for insert like below and call it from page on cicking add button


CREATE PROC AddStudentDetail
@name varchar(100), 
@surname varchar(100),
@faculty varchar(100),
@department varchar(100)
AS
DECLARE @DepartmentID int
IF EXISTS(SELECT 1 FROM Department WHERE DepartmentName = @department)
BEGIN
  SELECT @DepartmentID= ID
  FROM Department
  WHERE DepartmentName = @department
END
ELSE
BEGIN
  INSERT INTO Department(DepartmentName)
  VALUES (@department)
  SET @DepartmentID = SCOPE_IDENTITY() 
END

INSERT INTO Student (name,surname,faculty,departmentid)
VALUES(@name,@surname,@faculty,@DepartmentID)
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

acyilmaz
Starting Member

4 Posts

Posted - 2010-11-21 : 07:41:19

how can I call it from my web page?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 07:43:56

whats the front end you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

acyilmaz
Starting Member

4 Posts

Posted - 2010-11-21 : 07:54:42

im developing a web page with c#.net framework 2

and whats is the mean of 1 in this command?

IF EXISTS(SELECT 1 FROM Department WHERE DepartmentName = @department)

If I have more foreign key in this table can I add them in this procedure too? For example i want to add faculty ID ,too. Then I add a DECLARE for faculty too in this procedure, dont I?

actually i didt want to enter the tsql, procedures etc.. However, it is the way of learn :) thank you very much for your helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 08:14:25

1 is just to check for presence of records returned by query.
Yup. in case of multiple fks you need an IF EXISTS() check for each.

for calling sp from webpage see

http://www.aspfree.com/c/a/ASP.NET/Using-TSQL-Stored-Procedures-with-ASPNET-20/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

acyilmaz
Starting Member

4 Posts

Posted - 2010-11-21 : 08:18:41

thank you very very much bro im trying to apply it to my table on the notebook after that rewrite on sql server and try thank you very much for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-21 : 08:20:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources