Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure

When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem.

Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem.

Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem

The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred procedure "A" only needs to executed once, that is when the user initially signs on, after that, Stored Procedure "c" takes over with each successive button clicks (there are eight buttons), Stored Procedue "B", needs to display all information as it is updated each time the user logs onto the page.

Thank you.

A.  stored procedure - Insert
ALTER PROCEDURE dbo.usp_InsertTodaysLogin
(
 @UserID char(8),
 @Today datetime,
 @SSNLogon char(15)
)
AS
 SET NOCOUNT OFF;
INSERT INTO lAttendance
           (UserID, Today, SSNLogon)
VALUES (@UserID,@Today,@SSNLogon)

______

B.  INSERT INTO ReadPreviousDataEntries
           (UserID, Today, SSNLogon)
VALUES (@UserID,@Today,@SSNLogon)

ALTER PROCEDURE dbo.usp_GetEmployeesTodaysLoginInfo
 @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   Attendance
WHERE (UserID = @UserID) AND (Today = @Today)

 __________________________

C.  ALTER PROCEDURE dbo.usp_UpdateLogin
(
 @Login datetime,
 @LoginLogon char(15),
 @UserID char(8),
 @Today datetime
)
AS
 SET NOCOUNT OFF;
UPDATE Attendance
SET      Login = @Login, LoginLogon = @LoginLogon
WHERE (UserID = @UserID) AND (Today = @Today)

_______________________

My aspx.vb code:

    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.ToShortDateString

            lblDate.Text = dtToday

            Dim strLogin As String
         
            strLogin = Replace(HttpContext.Current.User.Identity.Name.ToString, "DOMAIN\", "")

            'Establish connection to the database connection
            Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)

            'Insert New record
            Dim cmdAddRecord As SqlCommand = New SqlCommand
            With cmdAddRecord
                .CommandType = CommandType.StoredProcedure
                .CommandText = "usp_InsertTodaysLogin"
                .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


            '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_ReadPreviousEntries"
                .Parameters.AddWithValue("@UserID", Session("EmployeeID"))
                .Parameters.AddWithValue("@Today", DateTime.Now.ToShortDateString)
                .Connection = sqlcon
                .Connection.Open()
            End With
            ataDataReader = ataSelectEmployee.ExecuteReader()
            ataDataReader.Read()
            lblLogin.Text = ataDataReader.Item(2).ToString
            lblAmBreakOut.Text = ataDataReader.Item(3).ToString
            lblAMBreakIn.Text = ataDataReader.Item(4).ToString
            lblLunchOut.Text = ataDataReader.Item(5).ToString
            lblLunchIn.Text = ataDataReader.Item(6).ToString
            lblPMBreakOut.Text = ataDataReader.Item(7).ToString
            lblPMBreakIn.Text = ataDataReader.Item(8).ToString
            lblLogout.Text = ataDataReader.Item(9).ToString
            ataDataReader.Close()
            sqlcon.Close()


        End If
    End Sub

    Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
 
        Dim dtToday As DateTime
        Dim strLogin As String


        dtToday = DateTime.Now()

    
        strLogin = Replace(HttpContext.Current.User.Identity.Name.ToString, "DOMAIN\", "")

        'Establish connection to the database connection
        Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)

        'Updates employee record based on current date
        Dim cmdAddRecord As SqlCommand = New SqlCommand
        With cmdAddRecord
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_UpdateLogin"
            .Parameters.AddWithValue("@Login", dtToday)
            .Parameters.AddWithValue("@LoginLogon", strLogin)
            .Parameters.AddWithValue("@UserID", Session("EmployeeID"))
            .Parameters.AddWithValue("@Today", DateTime.Now.ToShortDateString)
            .Connection = sqlcon
            .Connection.Open()
            .ExecuteNonQuery()
            .Connection.Close()
            .Dispose()
        End With
     

    End Sub
End Class

0
Ayomide
11/10/2008 10:11:44 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
863 Views

Similar Articles

[PageSpeed] 55

 

I was able, with assistance, to solve the problem using, "IF NOT EXISTS(select * ...) BEGIN (INSERT ....l)

Everything else then flows.

Ayomide

 

0
Ayomide
11/20/2008 8:20:47 PM
Reply:

Similar Artilces:

How to Retrive the store procedure return value in Ado.Net by using Dot Net?
Hi i am thiru. i try to create the small search engine. so that i have wrote the store procedure for it. thats given below.Create procedure FindtestThree @Name varchar(25), @Location varchar(25), @EmpId varchar(25) as select * from TbInfo where Name like + '%' + @Name + '%' select * from TbInfo where Location like + '%' + @Location + '%' select * from hi where ComName like + '%' + @EmpId + '%' Exec FindtestThree 'aa','a','d' ----------------------------------------- The above procedure return t...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

ADO.net dataset with 2 datatable store it into 1 stored procedure
Hi, i have 1 dataset which have 2 dataTable contain; dataTable 1: FieldName : firstname lastname jobtitle company mobiletelephone contact_Id Value: Barns Happy Executive Unigi 32123 0 dataTable2: Fieldname :address street city zipcode country telephone fax contact_Id 44Dover Dover Sg 12345 Singapore 2331 12313 0 I m trying to put the whole dataset into 1 stored procedure here's my code Public Function update(ByVal ds As DataSet) As B...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

Stored procedure takes longer to execute with ADO.NET
Hello, I have a stored procedure with a simple select statement that queries a complex view. It returns about 10000 rows. This takes about 2 seconds in query analyzer but when I call this in my application using ADO.NET it takes around 3 minutes. Does anyone know why?  Thanks for your time I think you can case the dataset/DataTable and on that you can filter the row which is fater then query to the database Are you trying to display all this data? The stored proc probably runs just as fast, but rendering the display of all that data is what is taking so long. How about paging?[C# ...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

saving .net DateTime to sql database using Stored Procedure...
Hello, I've read many posts on this, but not found any real answers. I have a web method that accepts a DateTime parameter, then writes this value back to a SQL database table.  Sql data type is "datetime" and the column accepts nulls. When the web service is called I get an error, "SqlTypeException: SqlDateTime overflow" or another error of similiar results.When I removed the database call and just write to a label or variable on the page, the web method works fine, but when it saves to the database, the error is thrown. Any ideas on how to handle...

PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES
PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES - ADO.NET TO ORACLE FUNCTION   I have a very simple oracle function I'm trying to call from ado.net. The code works fine for a stored procedure but I get the error  PLS-00221: is not a procedure or is undefined  when I try to call the function. here's my code:  I've manually tested both the function and stored procedure and they are both working as expected.  here's my  vb.net code Protected Overrides Sub OnLoad(ByVal e As EventArgs) Dim cnn As OracleConnection Dim sConnSt...

Execute Stored Procedure Y asynchronously from Stored Proc X using SQL Server 2000
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.Thanks & Regards,Padam Kumar Tripathipadam_india@yahoo.com,padam_india@hotmail.comhttp://padam.8k.com When you say that you want to return from the SQL server execution.Really all of the Execution has to be completed before that Can ...

Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...

Web resources about - Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure - asp.net.sql-datasource

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Resources last updated: 1/10/2016 2:07:58 AM