Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 5:49am
Edited 17-Jan-13 5:54am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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  
v3
Comments
Nasser Abu Farah at 17-Jan-13 11:10am
   
Can you explain more plese
Nasser Abu Farah at 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 at 18-Jan-13 4:15am
   
Check my updated reply.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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  

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

  Print Answers RSS
0 OriginalGriff 365
1 Sergey Alexandrovich Kryukov 319
2 CPallini 275
3 DamithSL 214
4 Maciej Los 185
0 OriginalGriff 5,455
1 DamithSL 4,457
2 Maciej Los 3,885
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,115


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100