June 29, 2011 at 11:35 pm
Hello Friends,
I’ve two tables ClassAttendance and StudentAttendance
ClassAttendance have log of Class Attendance. Means that if the attendance of class 12 or 10 is done then its details are saved here. So it has one record of one class with today’s date.
StudentAttendance have log of Students Attendance. Means details of present or absent of every student is saved here with ClassAttendanceId of above table. It has multiple records of all students for one single class.
Following is the table structure:
ClassAttendance
ClassAttendanceId ClassName Present Absent Date Batch
---------------------------------------------------------------------------------------------------
1 10 3 1 2011-06-29 2011
StudentAttendance
StudentAttendanceId StudentId Status ClassAttendanceId
-----------------------------------------------------------------------
14Present1
25Absent1
37Present1
49Present1
I’ve retrieved Batch “2011” from StudentBatches table using Student Id like-
Select Batch from StudentBatches Where StudentId = 4; ie Result is 2011, then
Save all details in ClassAttendance table and Get ClassAttendanceId
Save all details in StudentAttendance table with ClassAttendanceId
I’m doing all this using one single stored procedure. I’ve called this stored procedure from front end code where I used loop to save multiple records in StudentAttendance table.
My problem is when I called the procedure first time I used first record of student to extract “Batch” and One record is saved in ClassAttendance Table as well as StudentAttendance Table.
But when my loop executes second time again same procedure is fired and again Second record is saved in ClassAttendance Table which I don’t want. I want to save second record of student this time.
I can do this using .NET code easily by selecting ClassAttendanceId and checking whether the record is present or not, but at this moment its not possible. So I want to do this using same store procedure.
Is there any way to save record in both tables when loop executes first time and save remaining records in StudentAttendance Table second time onwards?
Thanks In Adv...
June 30, 2011 at 3:56 am
This was removed by the editor as SPAM
June 30, 2011 at 4:55 am
Here it is -
ALTER PROCEDURE [dbo].[SP_AttendanceStudentClass]
(
@FirstName VARCHAR(50),
@DOB DATETIME,
@TotalPresent INT,
@TotalAbsent INT,
@AttendanceDate DATETIME
)
AS
BEGIN
DECLARE
@ExeQry int,
@ClassAttendanceId Int,
@ContactId int,
@BatchId int
---- Select Batch Id here
SELECT @BatchId = ISNULL ((Select BAT.BatchId from Contacts CON
INNER JOIN Batches BAT ON CON.ContactId = BAT.BatchId
Where CON.ContactId = @ContactId), 0 )
---- Save Record in ClassAttendance Table
BEGIN
EXEC @ExeQry = ClassAttendance_Insert
@AttendanceClassId = @AttendanceClassId OUTPUT,
@Batch = @BatchId,
@TotalPresent = @TotalPresent,
@TotalAbsent = @TotalAbsent,
@ClassAttendanceDate = @AttendanceDate
END
-- Start Inserting into StudentAttendance Table Here
END
But This Can Save Record only in ClassAttendance Table. I'm lil bit confused where to start inserting
Data in StudentAttendance Table.
June 30, 2011 at 5:36 am
This was removed by the editor as SPAM
June 30, 2011 at 6:13 am
Thanks For Your Reply,
There is not much difference in SP just add one line before Selecting @BatchId.
SELECT @ContactId = ISNULL((SELECT ContactId FROM Contacts Where ContactName = @FirstName), 0)
Another thing is that students details are in Another table.
At this moment you can avoid checking Stored Procedure.
I just need a good logic. or you can say the steps to do this.
I'm following these steps :
1. Select all students details and store it in dataset in Front End.
2. Pass First Name and DOB of student using loop.
3. When loop executes first time do this -
a.Get ContactId of student using FirstName and DOB
b. Take this ContactId and Extract Class (10) and Batch (2011)
c.Execute Query to Insert in ClassAttendance Table as we've ClassName, Present, Absent, Date and Batch.
d.Now we got ClassAttendanceId
--- ClassAttendance Table Transaction Ends Here ---
e.Take this ClassAttendanceId and Save record of first student in StudentAttendance Table as we've StudentId,
I'll extract Status(no need to worry) and ClassAttendanceId.
4.Transaction Ends here.
Now when the loop executes second time where I want to store second student record only, this time also STEP 3C will
executes. But I dont want to save record in ClassAttendance.
I want to save record of second student in StudentAttendance.
June 30, 2011 at 6:21 am
This was removed by the editor as SPAM
June 30, 2011 at 6:46 am
ok, lil bit closer to it.
The only thing that I've to check whether attendance of that particular date is done or not.
If attendance is done then save record of student only
else save record of class and student both.
Thanks a Lot
June 30, 2011 at 8:23 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply