Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
How to select previous entered values MS-SQL Server 2000Hello EveryOne, I have to write a stored procedure for selecting staff timings, the criteria will be selecting the staff timings based on current month and username(he has to be an active user). If the person did not enter the values for the current month then the values from his last entry are selected. I have succeded in selecting the values for the current month, but i am stuck at how to select the last entered values for that user. As i am new please help me out with this. See below for the procedure i have written. ALTER PROCEDURE c_sp_GetTime @UserName varchar(100), @Month int As BEGIN Select Starttime, EndTime, LunchStart, LunchStartHour, LunchStartMin, LunchDuration from t_staffworkinghours WHERE UserName=@UserName AND Month = @Month And Exists (Select * from t_StaffDetails where EmpStatus='Active' And t_StaffDetails. StaffId=t_StaffWorki nghours.StaffID) RETURN -1 END Thanks in advance, Shilpa.
Post Follow-up to this message
Re: How to select previous entered values MS-SQL Server 2000Something like this might work for you: (first look for current month values , and if none found, retrieve the last entry for the user.) ALTER PROCEDURE c_sp_GetTime ( @UserName varchar(100), @Month int ) AS BEGIN SELECT h.Starttime, h.EndTime, h.LunchStart, h.LunchStartHour, h.LunchStartMin, h.LunchDuration FROM t_staffworkinghours h JOIN t_StaffDetails d ON h.StaffID = d.StaffID WHERE ( h.UserName = @UserName AND h.[Month] = @Month AND d.EmpStatus = 'Active' ) IF @@ROWCOUNT = 0 SELECT TOP 1 h.Starttime, h.EndTime, h.LunchStart, h.LunchStartHour, h.LunchStartMin, h.LunchDuration FROM t_staffworkinghours h JOIN t_StaffDetails d ON h.StaffID = d.StaffID WHERE ( h.UserName = @UserName AND d.EmpStatus = 'Active' ) ORDER BY h.[Month] DESC RETURN -1 END As a side note, you should NOT use reserved words for table and column names , i.e., [Month]. Also, if you were to store the Starttime/Endtime as DATETIME datatypes, you could eliminate the LunchStartHour, LunchStartMin, LunchDuration, and Month columns since those values are always easily derived from the StartTime/EndT ime. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous You can't help someone get up a hill without getting a little closer to the top yourself. - H. Norman Schwarzkopf "Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164671574.212226.78480@f16g2000 cwb.googlegroups.com... > Hello EveryOne, > > I have to write a stored procedure for selecting staff timings, the > criteria will be selecting the staff timings based on current month and > username(he has to be an active user). If the person did not enter the > values for the current month then the values from his last entry are > selected. > > I have succeded in selecting the values for the current month, but i am > stuck at how to select the last entered values for that user. As i am > new please help me out with this. See below for the procedure i have > written. > > ALTER PROCEDURE c_sp_GetTime > @UserName varchar(100), > @Month int > > As > BEGIN > Select Starttime, EndTime, LunchStart, LunchStartHour, > LunchStartMin, LunchDuration > from t_staffworkinghours > WHERE UserName=@UserName > AND Month = @Month > And Exists > (Select * > from t_StaffDetails > where EmpStatus='Active' > And t_StaffDetails. StaffId=t_StaffWorki nghours.StaffID) > > RETURN -1 > END > > Thanks in advance, Shilpa. >
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Hello Arnie, Thanks for your time and i have implemented all your suggestions, But when i use this procedure in a webform I get this error message "No data found". Any suggestions on where i am going wrong. Arnie Rowland wrote: > Something like this might work for you: (first look for current month valu es, and if none found, retrieve the last entry for the user.) > > > ALTER PROCEDURE c_sp_GetTime > ( @UserName varchar(100), > @Month int > ) > AS > BEGIN > SELECT > h.Starttime, > h.EndTime, > h.LunchStart, > h.LunchStartHour, > h.LunchStartMin, > h.LunchDuration > FROM t_staffworkinghours h > JOIN t_StaffDetails d > ON h.StaffID = d.StaffID > WHERE ( h.UserName = @UserName > AND h.[Month] = @Month > AND d.EmpStatus = 'Active' > ) > > IF @@ROWCOUNT = 0 > > SELECT TOP 1 > h.Starttime, > h.EndTime, > h.LunchStart, > h.LunchStartHour, > h.LunchStartMin, > h.LunchDuration > FROM t_staffworkinghours h > JOIN t_StaffDetails d > ON h.StaffID = d.StaffID > WHERE ( h.UserName = @UserName > AND d.EmpStatus = 'Active' > ) > ORDER BY h.[Month] DESC > > RETURN -1 > END > > As a side note, you should NOT use reserved words for table and column nam es, i.e., [Month]. > > Also, if you were to store the Starttime/Endtime as DATETIME datatypes, yo u could eliminate the LunchStartHour, LunchStartMin, LunchDuration, and Mont h columns since those values are always easily derived from the StartTime/En dTime. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > You can't help someone get up a hill without getting a little closer to th e top yourself. > - H. Norman Schwarzkopf > > > "Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164671574.2122 26.78480@f16g2000cwb.googlegroups.com... > ------ =_NextPart_000_00C8_ 01C71240.92B3EAE0 > Content-Type: text/html; charset=iso-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 7586 > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> > <META content="MSHTML 6.00.5730.11" name=GENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY> > <DIV><FONT face=Arial size=2>Something like this might work for you: (firs t look > for current month values, and if none found, retrieve the last entry for t he > user.)</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><BR><FONT face="Courier New" size=2>ALTER PROCEDURE > c_sp_GetTime<BR> ( @UserName > varchar(100),<BR> @Month int<BR> > )<BR>AS<BR> BEGIN<BR> &am p;nbsp; SELECT > <BR> h.Starttime, > <BR> h.EndTime, > <BR> h.LunchStart, > <BR> > h.LunchStartHour,<BR> &am p;nbsp; > h.LunchStartMin, <BR> &am p;nbsp; > h.LunchDuration<BR> FROM t_staffworkinghours > h<BR>   ; JOIN t_StaffDetails > d<BR>   ; ON > h.StaffID = d.StaffID<BR>   ; WHERE ( > h.UserName = > @UserName<BR> & amp;nbsp; > AND h.[Month] = > @Month<BR> & ;nbsp; AND > d.EmpStatus = > 'Active'<BR> &a mp;nbsp; > )<BR>   ; > <BR> IF @@ROWCOUNT = 0 < /FONT></DIV> > <DIV><FONT face="Courier New" size=2></FONT> </DIV> > <DIV><FONT face="Courier New" > size=2> &nb sp; SELECT TOP > 1<BR>   ; > h.Starttime, > <BR> > h.EndTime, > <BR> > h.LunchStart, > <BR> > h.LunchStartHour,<BR> &am p;nbsp; > h.LunchStartMin, > <BR> > h.LunchDuration<BR> & nbsp; FROM > t_staffworkinghours > h<BR>   ; JOIN > t_StaffDetails > d<BR>   ; > ON h.StaffID = d.StaffID<BR> &n bsp; > WHERE ( h.UserName = > @UserName<BR> & amp;nbsp; &nb sp; > AND d.EmpStatus = > 'Active'<BR> &a mp;nbsp; &nbs p; > )<BR>   ; ORDER BY h.[Month] > DESC<BR> &n bsp; &a mp;nbsp; &nbs p; > <BR> RETURN -1<BR>&n bsp; END</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>As a side note, you should NOT use reserved w ords > for table and column names, i.e., [Month]. </FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Also, if you were to store the Starttime/Endt ime as > DATETIME datatypes, you could eliminate the <FONT > face="Courier New">LunchStartHour,& amp;nbsp;LunchStartM in, LunchD uration, <FONT > face=Arial>and</FONT> Month </FONT><FONT face=Arial>columns since those va lues > are always easily derived from the StartTime/EndTime.<BR></DIV></FONT></FO NT> > <DIV><FONT face=Arial size=2>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood Consu lting, > Inc</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR >Most > experience comes from bad judgment. <BR>- Anonymous</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>You can't help someone get up a hill without > getting a little closer to the top yourself.<BR>- H. Norman > Schwarzkopf</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>"Sharon" <</FONT><A > href=" mailto:sharonrao123@ yahoo.com.au"><FONT face=Arial > size=2>sharonrao123@yahoo.com.au</FONT></A><FONT face=Arial size=2>> wr ote in > message </FONT><A > href="news:1164671574.212226.78480@f16g2000cwb.googlegroups.com"><FONT > face=Arial > size=2>news:1164671574.212226.78480@f16g2000cwb.googlegroups.com</FONT></A ><FONT > face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Hello > EveryOne,<BR>> <BR>> I have to write a stored procedure for selectin g > staff timings, the<BR>> criteria will be selecting the staff timings ba sed on > current month and<BR>> username(he has to be an active user). If the pe rson > did not enter the<BR>> values for the current month then the values fro m his > last entry are<BR>> selected.<BR>> <BR>> I have succeded in selec ting > the values for the current month, but i am<BR>> stuck at how to select the > last entered values for that user. As i am<BR>> new please help me out with > this. See below for the procedure i have<BR>> written.<BR>> <BR>> > ALTER PROCEDURE c_sp_GetTime<BR>> @UserName varchar (100),<BR>> > @Month int<BR>> <BR>> As<BR>> > BEGIN<BR>> Select Starttime, EndTime, > LunchStart, LunchStartHour,<BR>> LunchStartMin, > LunchDuration<BR>> &am p;nbsp; from > t_staffworkinghours<BR>> &nb sp; WHERE </FONT><A > href=" mailto:UserName=@Use rName"><FONT face=Arial > size=2>UserName=@UserName</FONT></A><BR><FONT face=Arial > size=2>> AN D Month = > @Month<BR>> And > Exists<BR>> &n bsp; > (Select > *<BR>> & nbsp; & amp;nbsp; > from > t_StaffDetails<BR>> &a mp;nbsp; &nbs p; > where > EmpStatus='Active'<BR>> &nbs p; & ;nbsp; > And > t_StaffDetails. StaffId=t_StaffWorki nghours.StaffID)<BR>> & nbsp; & amp;nbsp; &nb sp; &am p;nbsp; > <BR>> RETURN -1<BR>> END<BR>> <BR>> Thanks in advance, > Shilpa.<BR>></FONT></BODY></HTML> > > ------ =_NextPart_000_00C8_ 01C71240.92B3EAE0--
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Hello Arnie,
Just elobarating on the issue below, Please see below for the methods i
have used in the webpage to display the values using the procedure. I
am getting the error " No Data Found" not able to figure out where i am
doing wrong
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack() Then
Call displaydata()
End If
End Sub
Private Sub GetTime()
Dim UserName As String = "Megan Sproats"
Dim Month As Integer = 11
Try
Dim sqlconnection3 As New
System.Data.SqlClient.SqlConnection("connectionstring")
Dim cmd2 As New SqlClient.SqlCommand
cmd2.CommandText = "c_sp_GetTime"
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Connection = sqlconnection3
cmd2.Parameters.Clear()
Dim myparam As New SqlClient.SqlParameter
Dim myparam2 As New SqlClient.SqlParameter
myparam.ParameterName = "@UserName"
myparam.Direction = ParameterDirection.Input
myparam.SqlDbType = SqlDbType.VarChar
myparam.Value = UserName
cmd2.Parameters.Add(myparam)
myparam2.ParameterName = "@Month"
myparam2.Direction = ParameterDirection.Input
myparam2.SqlDbType = SqlDbType.Int
myparam2.Value = Month
cmd2.Parameters.Add(myparam2)
DATime.SelectCommand = cmd2
sqlconnection3.Open()
Catch ex As Exception
Dim param As String
param = "message=" & Server.UrlEncode("GetRego Error -
" & ex.Message)
End Try
End Sub
Private Sub displaydata()
Dim dailyStartTime, dailyEndTime As DateTime
Dim lunchstarthour, lunchstartmin, dailyLunchPeriod As
Integer
Call GetTime()
Dim reader As Data.SqlClient.SqlDataReader
reader = DATime.SelectCommand.ExecuteReader
If reader.HasRows Then
While reader.Read
dailyStartTime = reader.Item("StartTime")
dailyEndTime = reader.Item("EndTime")
lunchstarthour = reader.Item("LunchStartHour")
lunchstartmin = reader.Item("LunchStartMin")
dailyLunchPeriod = reader.Item("LunchDuration")
End While
reader.NextResult()
reader.Close()
Label1.Text = dailyStartTime
Label2.Text = dailyEndTime
Else
Dim param As String
param = "message=" & Server.UrlEncode("No data found")
lblfeedback.Text = param
End If
End Sub
Sharon wrote:[color=darkred
]
> Hello Arnie,
>
> Thanks for your time and i have implemented all your suggestions, But
> when i use this procedure in a webform I get this error message "No
> data found". Any suggestions on where i am going wrong.
>
>
> Arnie Rowland wrote:
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Sharon
Can you add another column as Last_Modified or somethinmg like that and
having a trigger that updates the column with GETDATE() ?
"Sharon" <sharonrao123@yahoo.com.au> wrote in message
news:1164693449.988382.219130@80g2000cwy.googlegroups.com...
> Hello Arnie,
>
> Just elobarating on the issue below, Please see below for the methods i
> have used in the webpage to display the values using the procedure. I
> am getting the error " No Data Found" not able to figure out where i am
> doing wrong
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> 'Put user code to initialize the page here
> If Not IsPostBack() Then
> Call displaydata()
> End If
> End Sub
>
> Private Sub GetTime()
> Dim UserName As String = "Megan Sproats"
> Dim Month As Integer = 11
>
> Try
> Dim sqlconnection3 As New
> System.Data.SqlClient.SqlConnection("connectionstring")
> Dim cmd2 As New SqlClient.SqlCommand
> cmd2.CommandText = "c_sp_GetTime"
> cmd2.CommandType = CommandType.StoredProcedure
> cmd2.Connection = sqlconnection3
> cmd2.Parameters.Clear()
> Dim myparam As New SqlClient.SqlParameter
> Dim myparam2 As New SqlClient.SqlParameter
> myparam.ParameterName = "@UserName"
> myparam.Direction = ParameterDirection.Input
> myparam.SqlDbType = SqlDbType.VarChar
> myparam.Value = UserName
> cmd2.Parameters.Add(myparam)
> myparam2.ParameterName = "@Month"
> myparam2.Direction = ParameterDirection.Input
> myparam2.SqlDbType = SqlDbType.Int
> myparam2.Value = Month
> cmd2.Parameters.Add(myparam2)
> DATime.SelectCommand = cmd2
> sqlconnection3.Open()
> Catch ex As Exception
> Dim param As String
> param = "message=" & Server.UrlEncode("GetRego Error -
> " & ex.Message)
> End Try
> End Sub
>
> Private Sub displaydata()
> Dim dailyStartTime, dailyEndTime As DateTime
> Dim lunchstarthour, lunchstartmin, dailyLunchPeriod As
> Integer
> Call GetTime()
> Dim reader As Data.SqlClient.SqlDataReader
> reader = DATime.SelectCommand.ExecuteReader
> If reader.HasRows Then
>
> While reader.Read
> dailyStartTime = reader.Item("StartTime")
> dailyEndTime = reader.Item("EndTime")
> lunchstarthour = reader.Item("LunchStartHour")
> lunchstartmin = reader.Item("LunchStartMin")
> dailyLunchPeriod = reader.Item("LunchDuration")
> End While
> reader.NextResult()
> reader.Close()
> Label1.Text = dailyStartTime
> Label2.Text = dailyEndTime
> Else
> Dim param As String
> param = "message=" & Server.UrlEncode("No data found")
> lblfeedback.Text = param
> End If
> End Sub
>
> Sharon wrote:
>
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Check the output of the stored procedure in query analyzer. It should return rows. >From the code on the webpage, it is likely that the data reader for executing the stored procedure has a different scope. The datareader being used for reading the values is in a different function. Let me know if this helps. Manisha Gandhi Uri Dimant wrote:[color=darkred ] > Sharon > Can you add another column as Last_Modified or somethinmg like that and > having a trigger that updates the column with GETDATE() ? > > > > > > > > "Sharon" <sharonrao123@yahoo.com.au> wrote in message > news:1164693449.988382.219130@80g2000cwy.googlegroups.com...
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000i checked in the query analyser the procedure returns an empty row for the first select statement and one row for the second select statement, Now how do i use this stored procedure in a webform to display the data. The data reader seems to read the first select statement and as it does not have any rows it returns false thats what i think so. Please let me know any suggestions . Thanks in advance, Shilpa. mdGandhi wrote:[color=darkred ] > Check the output of the stored procedure in query analyzer. It should > return rows. > executing the stored procedure has a different scope. > The datareader being used for reading the values is in a different > function. > > Let me know if this helps. > > Manisha Gandhi > > Uri Dimant wrote:
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000The procedure code that I provided you will return ONLY one row. Only one of the SELECT statements will execute -not both. It will return an empty row only if there is no history for the UserName. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous You can't help someone get up a hill without getting a little closer to the top yourself. - H. Norman Schwarzkopf "Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164753102.911214.9760@l39g2000cwd.googlegroups.com... >i checked in the query analyser the procedure returns an empty row for > the first select statement and one row for the second select statement, > Now how do i use this stored procedure in a webform to display the > data. The data reader seems to read the first select statement and as > it does not have any rows it returns false thats what i think so. > > Please let me know any suggestions . > > Thanks in advance, Shilpa. > mdGandhi wrote: >
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Hello Arnie, Please ignore me if i am wrong, you have said in your reply that only one select statement is executed not both. I have executed the procedure in the query analyser with two senarios First Senario: The user has entered the staff timings for the current month The first Select statement is executed and the message i get is (1 row(s) affected) Second Senario: The user has entered the staff timings for the previous month, no entry for current month. The messages i get in query analyser (0 row(s) affected) (1 row(s) affected) what does the message 0 rows affected mean, I am confused here if only one select statement is executed why i am i getting two messages. Please advice. Thank you, Shilpa. Arnie Rowland wrote:[color=darkred ] > The procedure code that I provided you will return ONLY one row. Only one of > the SELECT statements will execute -not both. > > It will return an empty row only if there is no history for the UserName. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > You can't help someone get up a hill without getting a little closer to th e > top yourself. > - H. Norman Schwarzkopf > > > "Sharon" <sharonrao123@yahoo.com.au> wrote in message > news:1164753102.911214.9760@l39g2000cwd.googlegroups.com...
Post Follow-up to this message Re: How to select previous entered values MS-SQL Server 2000Let me try to correct the misunderstanding. I see that my hasty response was very confusing since I did not reread my original post and was not properly responding to your last question. You are correct, in the second scenario, an empty resultset is returned for the first select, and a potentially empty resultset is returned for the second select. Now how to handle that in the application. (Here is a simplified VB.NET example...) Do While dr.Read 'Assign the dr.fields Loop dr.NextResult() Do While dr.Read 'Assign the dr.fields Loop Obviously, if the first resultset is empty, the code skips to the second resultset. And if the second resultset is also empty, then nothing is displayed. I hope this helps. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous You can't help someone get up a hill without getting a little closer to the top yourself. - H. Norman Schwarzkopf "Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164851774.595746.50840@l39g2000cwd.googlegroups.com... > Hello Arnie, > > Please ignore me if i am wrong, you have said in your reply that only > one select statement is executed not both. I have executed the > procedure in the query analyser with two senarios > > First Senario: The user has entered the staff timings for the current > month > > The first Select statement is executed and the message i get is > > (1 row(s) affected) > > Second Senario: The user has entered the staff timings for the previous > month, no entry for current month. The messages i get in query analyser > > (0 row(s) affected) > (1 row(s) affected) > > what does the message 0 rows affected mean, I am confused here if only > one select statement is executed why i am i getting two messages. > Please advice. > > Thank you, Shilpa. > > > Arnie Rowland wrote: >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
