Based on Output Value - Stop or Continue Stored Procedure Execution
I have a web page that captures time when the user clicks a series of buttons. There are seven buttons, the user clicks button 1, logs back into the app, then clicks button 2. When the user log back into the app to click button 2, the information specific to button 1 need to be written to the browser; this process continues daily. This is working in classic ASP, now I need to convert it to ASP.NET.
How do I get one record written for each user as I update information for that record throughout the day?
Thank You.
MsMe.
Goal:
1. When the user initially logs onto the page their ID, WINT and Time is inserted into the db.
2. The info in the db is written to the browser each time the user accesses the web page.
3. The user, throughout the day, clicks each button to capture various times for their activity.
4. After the initial info is inserted, then all subsequent time stamps needs to be updated based on the record in step one. Such there is only one record of time stamps for each day
Problem:
My stored proc is entering a new record each time the user logs onto the application.
My code looks like the following:
ALTER PROCEDURE dbo.usp_InsertLogin(
@UserID char(8),
@Today datetime,
@SSNLogon char(15)
)
as
BEGIN
select UserID, Today, SSNLogon FROM Time
WHERE (UserID=@UserID and Today=@Today and SSNLogon = @SSNLogon)
GROUP BY UserID, Today, SSNLogon
BEGIN
INSERT INTO ATTTblAttendance
(UserID, Today, SSNLogon)
VALUES (@UserID,@Today,@SSNLogon)
END
END
RETURN
_________________________________
ALTER PROCEDURE dbo.usp_GetLogin@UserID Char(8),
@Today DateTime
AS
SET NOCOUNT ON;
SELECT UserID, Today, Login, AMBreakOut, AMBreakIn, LunchOut, LunchIn,
PMBreakOut, PMBreakIn, Logout, Comments, LoginLogon, AMBreakOutLogon,
AMBreakInLogon, LunchOutLogon, LunchInLogon, PMBreakOutLogon, PMBreakInLogon, LogoutLogon, SSNLogon
FROM Time
WHERE (UserID = @UserID) AND (Today = @Today)
_______________________________
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim dtToday As DateTime
dtToday = DateTime.Now.ToLongDateString
lblDate.Text = dtToday
Dim strLogin As String
strLogin = Replace(HttpContext.Current.User.Identity.Name.ToString, "TIMEDOMAIN\", "")
'Establish connection to the database connection
Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TimeConnectionString").ToString)
'Retrives employee's record based on current date
Dim ataSelectEmployee As SqlCommand = New SqlCommand
Dim ataDataReader As SqlDataReader
With ataSelectEmployee
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_GetLogin"
.Parameters.AddWithValue("@UserID", Session("EmployeeID"))
.Parameters.AddWithValue("@Today", DateTime.Now.ToShortDateString)
.Connection = sqlcon
.Connection.Open()
.ExecuteNonQuery()
End With
ataDataReader = ataSelectEmployee.ExecuteReader()
Do While ataDataReader.Read()
lblLogin.Text = ataDataReader("Login").ToString
lblAmBreakOut.Text = ataDataReader("AMBreakOut").ToString
lblAMBreakIn.Text = ataDataReader("AMBreakIn").ToString
lblLunchOut.Text = ataDataReader("LunchOut").ToString
lblLunchIn.Text = ataDataReader("LunchIn").ToString
lblPMBreakOut.Text = ataDataReader("PMBreakOut").ToString
lblPMBreakIn.Text = ataDataReader("PMBreakIn").ToString
lblLogout.Text = ataDataReader("Logout").ToString
Loop
ataDataReader.Close()
'Insert New record
Dim cmdAddRecord As SqlCommand = New SqlCommand
With cmdAddRecord
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_InsertLogin"
.Parameters.AddWithValue("@UserID", Session("EmployeeID"))
.Parameters.AddWithValue("@Today", DateTime.Now.ToShortDateString)
.Parameters.AddWithValue("@SSNLogon", strLogin)
.Connection = sqlcon
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
sqlcon.Close()
End If
End Sub
Odpovědi
You 'could' add logic to the stored procedure to check to see if a record already exists but you'd have to decide on a constraint. Do you only want 1 record per day?
If NOT EXISTS(SELECT * FROM myTable WHERE myTimeStamp > DATEADD(day, -1, GETDATE()))
--Do your insert here
A.D.T.
Všechny reakce
Is this not working or do you just need a review for that ?
Jens K. Suessmeyer
I don't understand the issue.
What is the expected result? What is the real result?
A.D.T.
Problem:
My stored proc is entering a new record each time the user logs onto the application; instead of only one record. I have not been able to not have the Insert Stored Proc not to insert an additonal records when the user logs onto the page subsequent times.
MsMe.
That is not a problem of the stored procedure. Your application logic will have to determine if is has to log the user in the database again. A common approach for this is to write an information in the Session Context and to check this upon request, if not filled log the user if filled go ahead and skip the logging.
Jens K. Suessmeyer
You 'could' add logic to the stored procedure to check to see if a record already exists but you'd have to decide on a constraint. Do you only want 1 record per day?
If NOT EXISTS(SELECT * FROM myTable WHERE myTimeStamp > DATEADD(day, -1, GETDATE()))
--Do your insert here
A.D.T.
Hello A.D.T.,
Thank you for your direction I modified my stored procedure to reflect the values that I knew would be passed into it and after days and days of going around in circles, it W O R K E D!! *If Not Exists* did it, only one record per day per person, my updates which are associated with various buttons work.
Thank you very much.
I am walking on
.
MsMe.
Hi MsMe,
I thought that's what you were trying to do with the initial SELECT but wasn't sure.
However, Jens makes a good point. While the stored procedure check does work, it not very efficient to keep calling a stored procedure in your page only to disqualify every execution after the first. You should later on revisit this design and create a front-end validation to only call the stored procedure on the first login attempt.
Edit: ...or more accurately, I would add an UPDATE to the Else of your IF statement above to update the Time to reflect a last login attempt. This is pretty standard.
So you would have:
Code SnippetALTER PROCEDURE dbo.usp_InsertLogin
(
@UserID char(8),
@Today datetime,
@SSNLogon char(15)
)
as
If NOT EXISTS(select UserID, Today, SSNLogon FROM Time
WHERE (UserID=@UserID and SSNLogon = @SSNLogon
AND Today > DATEADD(day, -1, GETDATE()))
BEGIN
INSERT INTO ATTTblAttendance
(UserID, Today, SSNLogon)
VALUES (@UserID,@Today,@SSNLogon)
END
ELSE
UPDATE Time
SET Today = GETDATE()
WHERE UserID=@UserID and SSNLogon = @SSNLogon
RETURN
Good luck,
A.D.T.
