loop that need attention

I not sure how to create this in Classic ASP might need some help

i have 2 dates that i need to display every day in between..
this is for a staff member to select which days they work/not work

eg...


28/09/13 chkbox=yes
29/09/13 chkbox=yes
...
30/10/13 chkbox=no
31/10/13 chkbox=yes

so i need every date displayed between the selected dates (in an access db)

also with these dates...

How could i get the loop code to work for these and then INSERT/UPDATE the data base with all the data?

Cheers in advance
Graemewebber4technologiesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
So what do you have so far? We are not here to do all the work for you
As you are new here you should understand how the question points work
You can assign up to 500 points.
You assigned 150 which comes across to experts as meaning the question isn't really important and the solution is as easy as something like correcting a syntax problem
Your question is not.
0
GawaiCommented:
post your code we can check and correct
0
Graemewebber4technologiesAuthor Commented:
Hi , sorry i am very new to this site and I am very stumped to where to start... I dont have much to go on unfortunately

Statement:
Set CurrMonth=oConn.Execute("SELECT * FROM shop_figs,cons_figs WHERE store_pseudo=user_pseudo AND target_month=shop_month AND shop_first_date>="&Date())

this can give me the Shop start of month date and last dat of month date (both can sometimes not be the 1st or 31st, etc)

this part is not a problem and i have tested it, it pulls the correct data

here is what I have played with and no success at the moment:

<%
      MonthDate=CurrMonth("first_shop_date")
      Do while (Not CurrMonth.eof)
%>
      <tr>
            <td><%=MonthDate%></td>
      </tr>
<%
      MonthDate=MonthDate+1
      CurrMonth.MoveNext
            LOOP
      CurrMonth.Close
%>

thanks guys
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

GawaiCommented:
MonthDate=CurrMonth("first_shop_date") but in the statement it is  shop_first_date ???
0
Graemewebber4technologiesAuthor Commented:
yeah, sorry i didnt cut and paste, these are the same
0
GaryCommented:
Is your sql returning multiple dates or just the start date?
Some sample data that is returned would be helpful.
0
Graemewebber4technologiesAuthor Commented:
just the start date at the moment
0
GaryCommented:
What is the format of that start date?
0
Graemewebber4technologiesAuthor Commented:
28/9/2013
0
GaryCommented:
Try this, but I am rusty with classic ASP
<%
      MonthDate=CurrMonth("first_shop_date")
      tempdate=MonthDate
      currMonth=Month(tempdate)
      Do while (currMonth=Month(tempdate))
%>
      <tr>
            <td><%=tempdate%></td>
      </tr>
<%
      tempdate=DateAdd("d",1,tempdate)
      loop

      CurrMonth.Close
%>

Open in new window

0
Graemewebber4technologiesAuthor Commented:
Microsoft VBScript runtime error '800a01a8'

Object required: 'CurrMonth'

/setup.asp, line 170  >>>        CurrMonth.Close

28/09/2013
29/09/2013
30/09/2013

it pulled 3 dates in a row, instead of 34 that i need
0
GaryCommented:
Nowhere have you said what the last date is called.
0
Graemewebber4technologiesAuthor Commented:
sorry Iforgot to mention the field, it is called CurrMonth("shop_tfer_date")

I did mention it in my 2nd post i needed all dates in between 2 set ones

which for the current record we are pulling is 31/10/2013
0
GaryCommented:
Cannot remember how you compare dates in ASP but try this

<%
      MonthDate=CurrMonth("first_shop_date")
      tempdate=MonthDate

      Do while (tempDate<=CurrMonth("shop_tfer_date"))
%>
      <tr>
            <td><%=tempdate%></td>
      </tr>
<%
      tempdate=DateAdd("d",1,tempdate)
      loop

      CurrMonth.Close
%> 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Graemewebber4technologiesAuthor Commented:
thanks that works extremely well!!

however...

if there is multiple months for a user, it chooses the first month (eg June 2012), not the current (Oct 2013)

i have 2 users and 1 has just 1 month setup, the other has multiple.

all users will have multiple next month, so i need the lastest month.

I really thought this statement would select that:
Set CurrMonth=oConn.Execute("SELECT * FROM shop_figs,cons_figs WHERE store_pseudo=user_pseudo AND target_month=shop_month AND shop_first_date>="&Date())

particularly the bold part
0
Graemewebber4technologiesAuthor Commented:
Actually looking at what I need... the Bold part of the previous Statement

I need where it can define that today's date is in between the shop_first_date and shop_tfer_date

then I will have the correct month, required :)
0
GawaiCommented:
the date should be within ' in your case

shop_first_date>='"& Date() & "'" )
0
GawaiCommented:
If my solution was also helpful then you should have split the points
0
Graemewebber4technologiesAuthor Commented:
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.
0
Graemewebber4technologiesAuthor Commented:
Hi all, after doing some more research, I think what I need is BETWEEN for my statement since i need to be defined between 2 dates

Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&"  AND "&Date()&" BETWEEN shop_first_date AND shop_tfer_date")

Like that, but... i have an error :S

error '80020009'
0
Graemewebber4technologiesAuthor Commented:
upgraded points
0
Graemewebber4technologiesAuthor Commented:
Thank you very much SouthMod
0
Ray PaseurCommented:
Dates will be easier to work with if you follow the ISO-8601 standard.  You may also want to review this article from one of our EE colleagues:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_11210-Beware-of-Between.html
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Your dates in classic asp need to be m/d/yyyy.  Please run this code and see if it helps answer what you are trying to do.

The code will display 30 days of dates from a given starting point. In this case, I am using 10/1/2013.  

Never trust your data. I have bad data sample as the last input.
If you select only one date or more then 2 you will get an error
DateDiff is what you can use to calculate the amount of days between 2 dates in VBScript


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
dim minDate,MaxDate, select_date, strDates,goodData,testDate,Message,totDates
minDate="1/1/2100"
maxDate="1/1/2000"
strDates=""
select_date="10/1/2013"
goodData="" ' 0 for bad, 1 for good
Message=""
totDates=0


' **** check for form input and  process data
if request.form("selected_date")<>"" then ' we have data and can proceed

	' checkbox data wlll look like 1/1/2013,1/3/2013,1/15/2013
	goodData="1" ' start with good  and mark bad if error found
	strDates=request.form("selected_date")
	arrDates=split(strDates,",")
	if ubound(arrDates)=1 then' 2 dates selected
		' get min date and max date and check for bad data
		for each testDate in arrDates
	
			'check for good date
			if isdate(testDate) then 
				if cdate(testDate) < cdate(minDate) then 'reset minDate
					minDate=testDate
				
				end if
				if cdate(testDate)>cdate(maxDate) then 'reset maxDate
					maxDate=testDate
				end if
				totDates=datediff("d",minDate,maxDate)	
			else
			goodData="0"
			Message="Bad dates found, start over"
			end if
		next
	else
	goodData="0"
	Message="Please select only 2 dates"
	end if
	' now update our database only if we have good data
	if goodData="1" then
		' ***** Perform your sql insert here
		'for x=0 to totDates
			'sql to insert
		'next
	    
	
		Message="<p>Your data has been udpated and can be viewed below</p>"
	end if
	
end if

%>


<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>padas</title>
</head>

<body>
<%=request.form%>
<form action="" method="post">
<h2>Select Your Days</h2>
<%
' **** Simulate running through a list of dates starting oct 1, 2013 ******* '
for days=0 to 30
'weekday and weekedayname 
' http://www.w3schools.com/vbscript/func_weekday.asp 
' http://www.w3schools.com/vbscript/func_weekdayname.asp

%>
<input name="selected_date" type="checkbox" value="<%=dateadd("d",days,select_date)%>"><%=WeekDayName(Weekday(dateadd("d",days,select_date)),True)&" "&dateadd("d",days,select_date)%><br>
<%
next
%>
<input name="selected_date" type="checkbox" value="xyzabc">I am bad data<br>
<button type="submit">Go!</button>

</form>
<hr>
<%

response.write "<div>"&Message&"</div>"
if goodData="1" then
	response.write "<p>You selected a total of "&totDates+1&" dates that range from "&minDate&" through "&maxDate
	response.write "<ul>"
	for displayDate=0 to totDates
		response.write "<li>"&dateadd("d",displayDate,minDate)&"</li>"
	next
	response.write "</ul>"
	response.write "<hr><div><p>Your raw data</p><p>"&request.form&"</p><p>"&strDates&"</p>"
end if
%>

</body>

</body>
</html>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This sql
Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&"  AND "&Date()&" BETWEEN shop_first_date AND shop_tfer_date")

Open in new window


should probably be changed to something like

Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&"  AND  BETWEEN shop_first_date >= '"&minDate&"' AND shop_tfer_date<= '"&maxDate&"'")

Open in new window

0
Graemewebber4technologiesAuthor Commented:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'target_month=shop_month AND user_ID=29 AND BETWEEN shop_first_date>='28/10/2013' AND shop_tfer_date<='28/10/2013''.

Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&" AND BETWEEN shop_first_date>='"&Date()&"' AND shop_tfer_date<='"&Date()&"'")

This is looking promising! :)
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
get rid of between

Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&" AND (shop_first_date>='"&Date()&"' AND shop_tfer_date<='"&Date()&"'"))
0
Graemewebber4technologiesAuthor Commented:
ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/setup.asp, line 145

tempdate=CurrMonth("shop_first_date")   <<<<   line 145
Do while (tempDate<=CurrMonth("shop_tfer_date"))
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Can we stay on the point of your question.  You asked,

"i have 2 dates that i need to display every day in between..
this is for a staff member to select which days they work/not work"
"so i need every date displayed between the selected dates (in an access db)"
How could i get the loop code to work for these and then INSERT/UPDATE the data base with all the data?

This has been answered several different ways.  Can you now see how start with any 2 dates, then loop through and write out all dates between?

This http:Q_28264858.html#a39607593 is getting on to a new issue.  Please stay on point and if you have new issues, then you should start a new thread.

Your error  means there was no data in your query so it could not display the field, CurrMonth("shop_first_date").  You should have a method in  your code to account for no data.   Again, this is something for a new question.
0
Graemewebber4technologiesAuthor Commented:
this error is within this thread....

its to do with displaying all the dates in between the specified dates selected
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This is an error in your SQL.  

Let's say you have a database of users with one row of data.  The field name contains, G-Money79


This will work
sql="select * from myTable where name='G-Money79'

This will give the EOF BOF error when you call rs("name")
sql="select * from myTable where name='G Money79'

I don't see where you are giving proper feedback on what you have chosen to use in your code.

Gary has given you an option http:Q_28264858.html#a39568298

Ray has given you a great clue about dates and the BETWEEN clause http:Q_28264858.html#a39603757

I have given you a full example to go by http:Q_28264858.html#a39603960

After all of that, by simply saying this line does not work, "tempdate=CurrMonth("shop_first_date")   <<<<   line 145"  is really meaningless.  Where is your feedback on what you are using?  At this point, you should post a sample page that only uses the required information as I have done http:Q_28264858.html#a39603960.  Notice I have a full working example that allows you to input a start date and then list all dates between, select 2 dates and then provide a spot to place your update code.  Now it it is your turn to post your example page of what you are using so experts can get the big picture.
0
Graemewebber4technologiesAuthor Commented:
Im sorry but I am new to this and i didnt realise that i HAD to give feed back on everything

alot of the stuff in here I dont understand and therefore I do no use or try



This the example that you have just given, the 2 SQL statements and outcomes, i understand however, i need those dates to start and finish with


what I want to display:
all the dates between 2 set dates given by the store (in another table) inclusive

there is only ever 1 shop_first_date and ever 1 shop_tfer_date for each shop, this will never conflict.
however i think the code i currently have is not defining each shop somehow, even thou in the code we define each shop...

hopefully this makes sense
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
We are not mind readers.  If you don't give your feedback but instead keep posting your errors, we have no idea what you are doing.

If you don't want to post your code, there is no way to help you further.

Good luck on your project.
0
Graemewebber4technologiesAuthor Commented:
			<%
				ElseIf Len(Request.QueryString("cons"))>0 AND Len(Request.QueryString("targetmonth"))>0 THEN
				Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&" AND shop_first_date>="&Date()&" AND shop_tfer_date<="&Date())
				Set CurrMonth2=oConn.Execute("SELECT * FROM shop_figs,cons_figs WHERE user_ID="&Session("ID")&" AND target_month=shop_month AND shop_first_date>="&Date())
			%>
				<td width=20% align=center colspan=2 height=20><b>SETUP</b></td>
				<td width=60% colspan=6 align=center>&nbsp;</td>
				<td width=20% colspan=2 align=center><a href=bugs.asp?report=new>NEW</a></td>
			</tr>
			<tr>
				<td colspan=10 align=center height=60><font size=5><b>Consultant Current Month</b></font></td>
			</tr>
			<tr>
				<td colspan=10 align=center>&nbsp;</td>
			</tr>			<tr>
				<td width=50% colspan=5 align=center rowspan=6>
					<table>
					<%
						tempdate=CurrMonth2("shop_first_date")
						Do while (tempDate<=CurrMonth2("shop_tfer_date"))
					%>
						<tr>
							<td><input style='FONT-SIZE: 8pt; BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; FONT-FAMILY: verdana; COLOR: #666666; BACKGROUND-COLOR: #cccccc' type=checkbox name=expo checked>&nbsp;<%=tempdate%></td>
						</tr>
					<%
						tempdate=DateAdd("d",1,tempdate)
							Loop
						CurrMonth.Close
					%>
					</table>
				</td>
				<td width=20% colspan=2 align=right height=60>Target Month / Year</td>
				<td width=30% colspan=3>&nbsp;<b><%=MonthName(Month(CurrMonth2("shop_month")))%>&nbsp;<%=Year(CurrMonth2("shop_month"))%></b></td>
			</tr>
			<tr>
				<td colspan=2 align=right height=60>Transfer Target</td>
				<td colspan=3><input style='FONT-SIZE: 8pt; BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; FONT-FAMILY: verdana; COLOR: #666666; BACKGROUND-COLOR: #cccccc' type=text name=tfertarget value=<%=CurrMonth2("user_target")%>></td>
			</tr>
			<tr>
				<td colspan=2 align=right height=60>Stretch Target</td>
				<td colspan=3><input style='FONT-SIZE: 8pt; BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; FONT-FAMILY: verdana; COLOR: #666666; BACKGROUND-COLOR: #cccccc' type=text name=stretchtarget value=<%=CurrMonth2("user_stretch")%>></td>
			</tr>
			<tr>
				<td colspan=5 align=center height=60>&nbsp;</td>
			</tr>
			<tr>
				<td colspan=5 align=center height=60><input style='FONT-SIZE: 8pt; BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; FONT-FAMILY: verdana; COLOR: #666666; BACKGROUND-COLOR: #cccccc' type=submit name=updateconstargets value="Update Consultant Targets"></td>
			</tr>
			<tr>
				<td colspan=5 align=center height=60>&nbsp;</td>
			</tr>

Open in new window


this code displays a month from last Year (JUNE 2012), obviously I would like the current month OCT or NOV (when it spills over)

the data has been entered manually since JUNE 2012
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I still don't really understand this but I can see in your sql statement some possible errors.

Set CurrMonth=oConn.Execute("SELECT * FROM cons_figs,shop_figs WHERE target_month=shop_month AND user_ID="&Session("ID")&" AND shop_first_date>="&Date()&" AND shop_tfer_date<="&Date())

Open in new window


I have not used access in a long time and when I set up my recordsets, I use parametrized queries.  I am not used to seeing two tables at once.  Is this line correct?

FROM cons_figs,shop_figs

Open in new window


On this line
target_month=shop_month

Open in new window

You are looking for a row of data where one field is the equals the value of another field in the same row of data?  If that is the case, part of your problem may be how you have your data set up.  http://en.wikipedia.org/wiki/Database_normalization

LIke I said, how to loop through data and how you have your db set up and your sql statement  are two completely different things.

On the code you provided at line 19 you are using the recordset CurrMonth2, tempdate=CurrMonth2("shop_first_date")

Then on line 28, you are closing your OTHER recordset CurrMonth.  I don't even see where you are using the recordset CurrMonth in the code you just posted.  Perhaps that is a clue.

There are multiple possible errors from the database set up, to declaring the recordset and possibly logic.

What you need to isolate first is if your recordset has data.  Try and recreate your query using your ms access and see what query will generate the data you expect. Once you get that fixed, come back here and we can work on your looping.
0
Graemewebber4technologiesAuthor Commented:
With querys, if you put certain requests in a certain order, can that affect the outcome??

eg. user_ID, target_month

vs

eg target_month, user_ID

If so, i may see a problem, where there is many target_month's from different shops, but only 1 user_ID

with my query:

FROM cons_figs,shop_figs

yes I have pulled data from both tables to cross reference these fields:

target_month=shop_month

if these dont match, then no data should display

I will try and figure out in Access, I have never used this function before
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I think it will be a great help for you to use your ms access and make your query that way.  When you get your expected results, switch to sql view and you can see what the sql statment looks like.  

If you are using multiple tables, your join would look something like this http://www.w3schools.com/sql/sql_join.asp
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Open in new window


This is why I am suggesting you start a new question and just work on your sql/creating a recordset as something separate.  It's hard to work on looping without any data.
0
Graemewebber4technologiesAuthor Commented:
Figured it out.....

this issue was with the database, a couple of fields it was trying to match had different formatting, changed them to the same, all good and works!

Thanks everyone for the massive amount of help!!!
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I'm glad you finally figured it out.  When troubleshooting, it is a good idea to start at the start... and in this case it was your database.  You have to have data, if you have data, you have to be able to extract it.  This is where you problem was.  The looping didn't work because of the issues in the db.

Your problem did expose other issues.  For one, you should account for no data and allow your page to error gracefully.    I also see that you are not using parameterized querieshttp://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/A_4203-Dynamically-Parameterized-Queries-for-Classic-ASP.html  For security of your data this should be done.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.