Many to Many relation

Question

0.00/5 (No votes)

See more:

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:49am

Nasser Abu Farah

Updated 17-Jan-13 4:54am

v2

Add a Solution

2 solutions

Add a Solution

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

Zafar Sultan · Accepted Answer · 2013-01-17T05:01:00

Solution 1

Create a stored procedure as:

SQL

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.

Posted 17-Jan-13 5:01am

Zafar Sultan

Updated 17-Jan-13 22:15pm

v3

Comments

Nasser Abu Farah 17-Jan-13 11:10am

Can you explain more plese

Nasser Abu Farah 17-Jan-13 12:08pm

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:15am

Check my updated reply.

Pallavi Waikar · Accepted Answer · 2013-01-18T03:12:00

check out this link....answers no. 7
http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id[^]

SQL

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

Many to Many relation

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Many to Many relation

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us