Based on Output Value - Stop or Continue Stored Procedure Execution

Question

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

 

 

 

 

Answer 1

 

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

 

 

Jens K. Suessmeyer

 

Answer 2

I don't understand the issue.

 

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

 

A.D.T.

 

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

 

Answer 7

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)

      )

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.