accessing data related to current week from list of all weeks

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