hi friends,
I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks
i used the below query but itz not working
sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) "
..can anyone suggest me how to retrieve data for current week , for a related item
thanks in advance
i need urgent help.
select begin_of_week = dateadd(week, datediff(week, 0, getdate()), 0),
end_of_week = dateadd(week, datediff(week, 0, getdate()) + 1, -1)|||
You can try the following:
SELECT *FROM SylWeeksWHERE sylWeeKID>=dateadd(wk,datediff(wk, 0,getdate()), -1)AND sylWeeKID<dateadd(wk,datediff(wk, 0,getdate())+1, -1)
|||
could you try following
select * from tablename where datepart(wk, datefiled) = datepart(wk,getdate())
thanks,
satish.
|||
Hi khtan,
Thanks alot for your help,
actually the query that u sent helped me with little modification...actually i wanted to select sylweeksid
condition 1: where syllabusIdfk="& sylId & ",which is a foreign key
condition 2: sylweekid should return only current week criteria...that worked...which uu suggested
but i wanted to have condition 1 also in it...it is not working in any respect ??
i did following:
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate=dateadd(week,datediff(week,0,getdate())+1,-1)) "
but its not working
can u help.
thanks
overview:
tablename1: sylweeks----fields--sylweekid(primary key),sylweekcode,syllabusidfk(foreign key) ,sortorder--startdate,enddate
tablename2: syllabus--fields syllabusid(primary key),ImeIdfk(foreign key),course id
|||
Hi limno,
Thanks alot for your help,
actually the query that u sent helped me with little modification...actually i wanted to select sylweeksid
condition 1: where syllabusIdfk="& sylId & ",which is a foreign key
condition 2: sylweekid should return only current week criteria...that worked...which uu suggested
but i wanted to have condition 1 also in it...it is not working in any respect ??
i did following:
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate=dateadd(week,datediff(week,0,getdate())+1,-1)) "
but its not working
can u help.
thanks
overview:
tablename1: sylweeks----fields--sylweekid(primary key),sylweekcode,syllabusidfk(foreign key) ,sortorder--startdate,enddate
tablename2: syllabus--fields syllabusid(primary key),ImeIdfk(foreign key),course id
|||
Hi limno,
Thanks alot for your help,
can u check my query in one of the replies, i m not able to duplicate it.
thanks
|||
hi satish
thanks for your help,
can u check my query ,i m not able to duplicate it.
thanks
amanat
|||
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate>=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate<dateadd(week,datediff(week,0,getdate())+1,-1)) "
|||
Hi limno,
Thanks alot it worked...
thank u so much
amanat
i dont know how to give points at this time...but will surely
thanks alot
|||
Hi Limno,
I wan to add another query
Sqlstring1 = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ")order by sortorder"
which state that if there is no week that matches current date then first week present in database should be executed ..
please guide me where i should fix it
Public
OverloadsSharedFunction getFirstWeekId(ByVal SylIdAs Int32)AsInteger'this function returns the ID of the first week of the sylbi'It is used to seed the sylabus detail page when the page first loadsDim connAs SqlConnectionDim cmdAs SqlCommandDim countAs Int32Dim sqlStringAsString'Dim Sqlstring1 As StringDim drAs SqlDataReader
conn =
New SqlConnection
cmd =
New SqlCommand'the query
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate>=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate<dateadd(week,datediff(week,0,getdate())+1,-1)) "
conn.ConnectionString = ConfigurationSettings.AppSettings("ConnStr")
cmd.CommandType = CommandType.Text
cmd.CommandText = sqlString
Try
cmd.Connection = conn
IfNot conn.State = ConnectionState.OpenThen
conn.Open()
EndIf
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows =TrueThen
dr.Read()
ReturnCInt(dr.Item(0))EndIf
Catch exAs ExceptionThrow exFinally
dr.Close()
EndTryEndFunction
---------------
If (Session("curWEEK"))IsNothingThen
week = -1
EndIf'The following case statement checks the value of weekSelectCase weekCase -1'if week is set to -1 that means the session variable curWEEK has not been set'hence this is possibly the initial rendering of the syllabus.' since this is the initial visit, the syllabus is rendered with' the first week of the syllabus.
week = daWeeks.getFirstWeekId(
CInt(Session("SylId")))
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))
Case 0'if week is set to 0 then the user has selected to view all weeks'so all weeks are rendered to the syllabus (this is can effect performance
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))CaseElse'if week is set to any other value, the user has selected to view a specific week'that week ID is the value of week' that specific week is rendered to the syllabus
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))EndSelect
|||
Hi Limno,
I wan to add another query
Sqlstring1 = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ")order by sortorder"
which state that if there is no week that matches current date then first week present in database should be executed ..
please guide me where i should fix it
Public
OverloadsSharedFunction getId(ByVal SylIdAs Int32)AsInteger'this function returns the ID of the first week of the sylbiDim connAs SqlConnectionDim cmdAs SqlCommandDim countAs Int32Dim sqlStringAsStringDim drAs SqlDataReader
conn =
New SqlConnection
cmd =
New SqlCommand
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate>=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate<dateadd(week,datediff(week,0,getdate())+1,-1)) "
conn.ConnectionString = ConfigurationSettings.AppSettings("ConnStr")
cmd.CommandType = CommandType.Text
cmd.CommandText = sqlString
Try
cmd.Connection = conn
IfNot conn.State = ConnectionState.OpenThen
conn.Open()
EndIf
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows =TrueThen
dr.Read()
ReturnCInt(dr.Item(0))EndIf
Catch exAs ExceptionThrow exFinally
dr.Close()
EndTryEndFunction
---------------
If (Session("curWEEK"))IsNothingThen
week = -1
EndIf'The following case statement checks the value of weekSelectCase weekCase -1'if week is set to -1 that means the session variable curWEEK has not been set
week = daWeeks.getId(
CInt(Session("SylId")))
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))
Case 0
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))CaseElse'if week is set to any other value, the user has selected to view a specific week
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))EndSelect
Thanks alot once again for your kind help
|||
Hi Limno,
I wan to add another query
Sqlstring1 = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ")order by sortorder"
which state that if there is no week that matches current date then first week present in database should be executed ..
please guide me where i should fix it
Public
OverloadsSharedFunction getId(ByVal SylIdAs Int32)AsInteger'this function returns the ID of the first week of the sylbiDim connAs SqlConnectionDim cmdAs SqlCommandDim countAs Int32Dim sqlStringAsStringDim drAs SqlDataReader
conn =
New SqlConnection
cmd =
New SqlCommand
sqlString = "Select sylWeekId FROM SylWeeks Where (syllabusIDFK=" & SylId & ") AND (startdate>=dateadd(week,datediff(week,0,getdate()),-1))AND (Enddate<dateadd(week,datediff(week,0,getdate())+1,-1)) "
conn.ConnectionString = ConfigurationSettings.AppSettings("ConnStr")
cmd.CommandType = CommandType.Text
cmd.CommandText = sqlString
Try
cmd.Connection = conn
IfNot conn.State = ConnectionState.OpenThen
conn.Open()
EndIf
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows =TrueThen
dr.Read()
ReturnCInt(dr.Item(0))EndIf
Catch exAs ExceptionThrow exFinally
dr.Close()
EndTryEndFunction
---------------
If (Session("curWEEK"))IsNothingThen
week = -1
EndIf'The following case statement checks the value of weekSelectCase weekCase -1'if week is set to -1 that means the session variable curWEEK has not been set
week = daWeeks.getId(
CInt(Session("SylId")))
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))
Case 0
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))CaseElse'if week is set to any other value, the user has selected to view a specific week
doc.LoadXml(Syllabus.CreateRoot(
CType(Session("sylID"), Int32), week))EndSelect
Thanks alot once again for your kind help
