Click here to Skip to main content
Search within:



Next

Many to Many relation
Sign Up to vote bad
good
See more: ASP.NETRelationshipqueries
hi
I have a three tables representing a many to many relation as follows"
 
-------------------------------
1:visitors
-------------------------------
visitorID (primary key,AutoNumber)
visitorName
 
-------------------------------
2:Subjects
-------------------------------
subjectID (primary key,AutoNumber)
subjectDescription
 
-------------------------------
3:visitorSubjects
-------------------------------
visitorID(primary key)
subjectID(primary key)
datekey(primary key)
time(primary key)
-------------------------------
 
What I really need to do is to execute three queries for each table within the same button>
The problem occurs in the third query which is to insert visitorSubjects since I don't know the visitorID and the SubjectID.
 
I tried to run a code to calculate the max number of records and one to it and consider it as the new visitorID. But I feel that this is not the right way of doing it.
 
Note:
All the tree queries will insert new data, which mean I want to insert a new visitor and a new subject not an existing user or subject.
 

please advice me
Thank you
Posted 17 Jan '13 - 4:49
Nasser Abu Farah403
Edited 17 Jan '13 - 4:54
v2
Add a Solution


2 solutions

Create a stored procedure as:
Create Procedure AddDetails
(
@visitorname varchar(50),
@subjectdescription varchar(500),
@date datetime,
@time  datetime
)
AS
BEGIN
declare @visitorid int, @subjecid int
BEGIN TRAN
insert into visitors
values(@visitorname)
set @visitorid = scope_identity()
insert into Subjects
values(@subjectdescription)
set @subjecid = scope_identity()
insert into visitorSubjects
values(@visitorid,@subjecid,@date,@time)
COMMIT
END
 
Now pass parameters from front end to insert data in all the three tables.
  Permalink  
Posted 17 Jan '13 - 5:01
Zafar Sultan3.8K
Edited 17 Jan '13 - 22:15
v3
Comments
Nasser Abu Farah - 17 Jan '13 - 11:10
Can you explain more plese
Nasser Abu Farah - 17 Jan '13 - 12:08
I tried this code SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings[2].ToString(); string sql; sql = "INSERT INTO TestDcoope (testName) VALUES (@Val1);Select @@IDENTITY as newId;"; conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Val1", testDes.Text); int insertedID = cmd.ExecuteNonQuery(); result.Text = insertedID.ToString(); conn.Close(); But it always return 1 instead of the last record ID
Zafar Sultan - 18 Jan '13 - 4:15
Check my updated reply.
check out this link....answers no. 7
http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id[^]
 
INSERT INTO aspnet_GameProfiles(UserId,GameId)
VALUES(@UserId, @GameId);
SELECT SCOPE_IDENTITY()
And then
 
 Int32 newId = (Int32) myCommand.ExecuteScalar();
 
try this it for both visitor and subject table then that newId value use to insert in last table visitorSubject
  Permalink  
Posted 18 Jan '13 - 3:12
Pallavi Waikar4.6K
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 286
1 Mohammed Hameed 208
2 Sergey Alexandrovich Kryukov 188
3 Santhosh G_ 108
4 Mayur_Panchal 98
0 Sergey Alexandrovich Kryukov 8,216
1 OriginalGriff 6,271
2 CPallini 3,528
3 Rohan Leuva 2,703
4 Maciej Los 2,234


Advertise | Privacy | Mobile
Web01 | 2.6.130516.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid