Based on Output Value - Stop or Continue Stored Procedure Execution

Tue, 18 Nov 2008 19:34:46 Z

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)

)

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

RETURN
_________________________________
ALTER PROCEDURE dbo.usp_GetLogin

@UserID Char(8),

@Today DateTime

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

Based on Output Value - Stop or Continue Stored Procedure Execution

Tue, 18 Nov 2008 20:17:56 Z

Is this not working or do you just need a review for that ?

Jens K. Suessmeyer

Based on Output Value - Stop or Continue Stored Procedure Execution

Tue, 18 Nov 2008 20:35:03 Z

I don't understand the issue.

What is the expected result? What is the real result?

A.D.T.

Based on Output Value - Stop or Continue Stored Procedure Execution

Wed, 19 Nov 2008 01:30:21 Z

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.

Based on Output Value - Stop or Continue Stored Procedure Execution

Wed, 19 Nov 2008 09:12:17 Z

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

Based on Output Value - Stop or Continue Stored Procedure Execution

Wed, 19 Nov 2008 13:26:00 Z

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.

Based on Output Value - Stop or Continue Stored Procedure Execution

Wed, 19 Nov 2008 13:53:19 Z

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.

Based on Output Value - Stop or Continue Stored Procedure Execution

Wed, 19 Nov 2008 16:43:59 Z

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 Snippet

ALTER PROCEDURE dbo.usp_InsertLogin

(

@UserID char(8),

@Today datetime,

@SSNLogon char(15)

)

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.