write cus name if between dates 2

[mthompo]

have a loop that lists days of week
and writes cutomers name if the day equals
booking start date or booking end date
this is done using case statement

however i want the days between the start and end date to
be filled with the customers name but am stuck

courtdate  = dd/mm/yyyy
books/end  = dd/mm/yyyy

[code]
<% 
i = 1
k = Weekday(dDate, 1)
Do Until i = 8 
If i = k Then
courtdate= DateAdd("w", i-k, dDate) 
Else
courtdate = DateAdd("w", i-k, dDate)
End If
		
%>
<td class="mfitsml" align="center">
<%Select Case courtdate
Case x_bookst 
response.Write x_cuslname

Case x_bookend 
response.Write x_cuslname

'this is my case for between dates
Case courtdate > x_bookst AND courtdate < x_bookend
response.Write x_cuslname

Case else%>
<a href=<%Response.Write "custaddcourtcars.asp?carid=" & Server.URLEncode(x_carid) & "&courtdate=" & courtdate %> target="_blank" onClick="window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"><span>Free</span></a>
<%end select%>

</td>
<%i = i + 1
Loop
rscourtcars.MoveNext
loop	
%>

 

[damber]

Any joy yet ?

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

ok - had some help over weekend and am now using this code
[thanks bw] got to get this finished
has limitations but does the job for now

<table border=0 cellspacing=1 cellpadding=2 bgcolor=#ffffff>  
<tr>   
<TD colspan=3 bgcolor=#c0c0c0 class="courtcar">Vehicle</TD>   
<% For curDate = whenStart To whenEnd 
dayshort = formatDate ("%D",(curDate))
%> 

<td class="mfitsml"><%=dayshort & " " & Day(curDate)%></Td> 
<% Next %> 
</tr> 

<% 
priorID = "NONE" 

Do Until RS.EOF 
carID = RS("carID") 
courtcarbookid = RS("courtcarbookID")

If carID <> priorID Then          
If priorID <> "NONE" Then Response.Write Join(week,vbNewLine) & vbNewLine & "</TR>" & vbNewLine else
priorID = carID  
%> 
<tr>   
<td class="mfitsml" width="20"><%=RS("reg")%></td>   
<td class="mfitsml" width="50"><%=RS("model")%></td>   
<td class="mfitsml" width="20"><%=RS("trandesc")%></td>   
<% 
week = Array("<td class = mfitsml width=50><a href=custaddcourtcars.asp?carid= & Server.URLEncode(caridlink) target=""_blank"" onClick=""window.open(this.href,this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"">Free</a></td>", _
"<td class=mfitsml width=80>Free</td>", _ 
"<td class=mfitsml width=80>Free</td>", _ 
"<td class=mfitsml width=80>Free</td>", _ 
"<td class=mfitsml width=80>Free</td>", _ 
"<td class=mfitsml width=80>Free</td>", _ 
"<td class=mfitsml width=80>Free</td>" ) 
End If 

If (courtcarbookid <> "" Or Not IsNull(courtcarbookid)) Then 
	
bookst = DATEVALUE( RS("bookst") ) - whenStart 
bookend = DATEVALUE( RS("bookend") ) - whenStart 
lname = RS("lname") 
caridlink = rs("carid")
For dayIndex = bookst To bookend 
        
week(dayIndex) = "<td class=mfitsml width=80>" & lname & "</td>" 
Next
end if 
RS.MoveNext 
Loop 
 
Response.Write Join(week,vbNewLine) & vbNewLine & "</TR>" & vbNewLine 
%> 
</TABLE>

am now having trouble putting my link in the FREE array

original link in html with embedded asp

<td class = mfitsml><a href=<%Response.Write "custaddcourtcars.asp?carid=" & Server.URLEncode(x_carid) & "&courtdate=" & courtdate %> target="_blank" onClick="window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"><span>Free</span></a></td>

 

[damber]

ok, here's a working dynamic example:

<%@LANGUAGE=VBScript%>
<% Option Explicit %>
<%
response.ContentType = "text/plain" 

dim aBookings(7,4)

dim dStartDate, dEndDate, i,dCurrDate, sCurrentReg,j

dStartDate = DateSerial(2006,08,01)
dEndDate = DateSerial(2006,08,07)

'///// This emulates a sorted recordset e.g.:  ORDER BY CarReg, BookStartDate
'///// Please note that it is assumed that the bookings dont overlap for the same reg
aBookings(0,0) = "Cust 1"
aBookings(0,1) = "REG1"
aBookings(0,2) = DateSerial(2006, 08,01)
aBookings(0,3) = DateSerial(2006, 08,03)
aBookings(1,0) = "Cust 2"
aBookings(1,1) = "REG1"
aBookings(1,2) = DateSerial(2006, 08,06)
aBookings(1,3) = DateSerial(2006, 08,17)
aBookings(2,0) = "Cust 3"
aBookings(2,1) = "REG2"
aBookings(2,2) = DateSerial(2006, 08,05)
aBookings(2,3) = DateSerial(2006, 08,06)
aBookings(3,0) = "Cust 4"
aBookings(3,1) = "REG3"
aBookings(3,2) = DateSerial(2006, 08,03)
aBookings(3,3) = DateSerial(2006, 08,04)
aBookings(4,0) = "Cust 5"
aBookings(4,1) = "REG3"
aBookings(4,2) = DateSerial(2006, 08,05)
aBookings(4,3) = DateSerial(2006, 08,06)
aBookings(5,0) = "Cust 6"
aBookings(5,1) = "REG4"
aBookings(5,2) = DateSerial(2006, 08,01)
aBookings(5,3) = DateSerial(2006, 08,06)
aBookings(6,0) = ""
aBookings(6,1) = "REG5"
aBookings(6,2) = null
aBookings(6,3) = null


'///// Create Column Headers with the date range
Response.Write("       |  ")
for i=0 to DateDiff("d",dStartDate,dEndDate)
	Response.Write(dateadd("d",i,dStartDate) & "  |  ")
next
Response.Write(vbcrlf)

sCurrentReg = ""  'Used to keep track of the Registration for row separation

for j=0 to ubound(abookings,1)-1  'iterates through each row in the recordset/array

	'///// Check Current Reg against current row - 
	'///// if new create a new line and output the value of the registration
	if sCurrentReg <> "" then response.Write(vbcrlf) end if
	sCurrentReg = aBookings(j,1)
	Response.Write(sCurrentReg & ":  |  ")

	'///// Now create a loop for the date range ( so it always populates that many days)
    for i=0 to DateDiff("d",dStartDate,dEndDate)
        dCurrDate = DateAdd("d", i, dStartDate)

		'///// Check if this car has no bookings - if not, set as free 
		if isnull(aBookings(j,2)) or isnull(aBookings(j,3)) then
			response.Write(padText("Free") & "  |  ")
		else
			'//// Now check to see if the Current date is inside the dates booked for the customer in this record
			if datediff("d",dCurrDate,Cdate(aBookings(j,2))) <= 0 and _
				datediff("d", dCurrDate, cdate(aBookings(j,3))) >= 0 then
				response.Write(padText(aBookings(j,0)) & "  |  ")  
			else
				'//// If not in the range then check the next record to see if the Reg is the same 
				if aBookings(j+1,1) = sCurrentReg then 
					if datediff("d", dCurrDate , aBookings(j,3)) <= 0 then '/// Checks if the current date is after the end date for this record, if so, move to the next record
						j = j + 1 ' move the recordset along one to get the next customer for this reg
						i = i - 1 ' move this date iteration back to perform date check on new record.
					else
						response.Write(padText("Free") & "  |  ")	
					end if
				else
					response.Write(padText("Free") & "  |  ")
				end if
			end if
		end if
    next

next

function padText(sTxt)
	dim iLen : iLen = 10
	padText = left(sTxt & string(iLen," "), iLen)
end function

%>

This produces:

       |  01/08/2006  |  02/08/2006  |  03/08/2006  |  04/08/2006  |  05/08/2006  |  06/08/2006  |  07/08/2006  |  
REG1:  |  Cust 1      |  Cust 1      |  Cust 1      |  Free        |  Free        |  Cust 2      |  Cust 2      |  
REG2:  |  Free        |  Free        |  Free        |  Free        |  Cust 3      |  Cust 3      |  Free        |  
REG3:  |  Free        |  Free        |  Cust 4      |  Cust 4      |  Cust 5      |  Cust 5      |  Free        |  
REG4:  |  Cust 6      |  Cust 6      |  Cust 6      |  Cust 6      |  Cust 6      |  Cust 6      |  Free        |  
REG5:  |  Free        |  Free        |  Free        |  Free        |  Free        |  Free        |  Free        |

You will need to create a recordset that has the same properties as the array (e.g. sorted correctly etc) and you can set the date range to be whatever you like (e.g. 3 days, 1 week, 1 month etc). You will also need to play with the formatting - maybe put it into a table and set all the links etc.

I've not fully tested it, but it should be a good start for you.

Best of luck.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

just wanted to say thanks again for the guidance and appreciate you making me work it out for myself - if i had the time im sure i could of cracked it!

 

[damber]

no problem.. are you happy with my last post's example? do you understand it enough to make it work with your data ?

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

looks great but am finishing off with what ive got -
am not ungrateful and will use your code with my next
project [similar booking gubbins] must get this finished
or my boss is guna go ape!

ps - am smiling

 

[mthompo]

hi - had some time so thought i would give yur code a go
am new to arrays so am stuck

get this error

Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "HJ06TYH"]'

on this line

if datediff("d",dCurrDate,(aBookings(j,2))) <= 0 and _

have built an array from the rs -tried to use getrows
but didnt work so tried the ol fashioned way
sure i have info in right order in array

sql

SQL = "SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carID, CC.model, CB.courtcarbookid, tran.trandesc "_   
& "FROM courtcar AS CC "_   
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_   
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid "_   
& " LEFT JOIN cus ON cus.cusid = CB.cusid "_
& " AND CB.bookst <= '" & dwhenstart & "' AND CB.bookend >= '" & dwhenend & "' " _
& "ORDER BY CC.carid, CB.bookst"

asp

dStartDate = DateSerial(2006,08,01)
dEndDate = DateSerial(2006,08,07)

Set RS = Conn.Execute(sql)
'Put the recordset in an array
Dim aBookings()
numRows = 0
Do While NOT RS.EOF
    numRows = numRows + 1
    ReDim Preserve aBookings(3, numRows)
    aBookings(0, numRows - 1) = RS(0)
    aBookings(1, numRows - 1) = RS(1)
    aBookings(2, numRows - 1) = RS(2)
    aBookings(3, numRows - 1) = RS(3)
    RS.MoveNext
Loop

Set RS = conn.Execute( SQL ) 
If RS.EOF Then 
    Response.Redirect "No records" 
    Response.End 
End If 

response.ContentType = "text/plain" 

'///// Create Column Headers with the date range
Response.Write("       |  ")
for i=0 to DateDiff("d",dStartDate,dEndDate)
    Response.Write(dateadd("d",i,dStartDate) & "  |  ")
next
Response.Write(vbcrlf)

sCurrentReg = ""  'Used to keep track of the Registration for row separation

for j=0 to ubound(aBookings,1)-1  'iterates through each row in the recordset/array
	
if sCurrentReg <> "" then response.Write(vbcrlf) end if
sCurrentReg = aBookings(j,1)
Response.Write(sCurrentReg & ":  |  ")

for i=0 to DateDiff("d",dStartDate,dEndDate)
dCurrDate = DateAdd("d", i, dStartDate)

if isnull(aBookings(j,2)) or isnull(aBookings(j,3)) then
response.Write(padText("Free") & "  |  ")
        else

if datediff("d",dCurrDate,(aBookings(j,2))) <= 0 and _
datediff("d", dCurrDate,(aBookings(j,3))) >= 0 then
response.Write(padText(aBookings(j,0)) & "  |  ")  
            else
if aBookings(j+1,1) = sCurrentReg then 
if datediff("d", dCurrDate , aBookings(j,3)) <= 0 then                         
j = j + 1 ' move the recordset along one to get the next customer for this reg
i = i - 1 ' move this date iteration back to perform date check on new record.
else
response.Write(padText("Free") & "  |  ")    
end if
else
response.Write(padText("Free") & "  |  ")
end if
end if
end if
next

Next

 

[Tarwn]

1) This section is unnecessary and can be replaced as GetRows, a apparently was mentioned earlier. The only difference is that the array produced by GetRows has the first index as column # and second index as row #

Dim aBookings()
numRows = 0
Do While NOT RS.EOF
    numRows = numRows + 1
    ReDim Preserve aBookings(3, numRows)
    aBookings(0, numRows - 1) = RS(0)
    aBookings(1, numRows - 1) = RS(1)
    aBookings(2, numRows - 1) = RS(2)
    aBookings(3, numRows - 1) = RS(3)
    RS.MoveNext
Loop

2) This section is unnecessary

Set RS = conn.Execute( SQL )
If RS.EOF Then
    Response.Redirect "No records"
    Response.End
End If

if you wanted to escape when the recordset was empty, funcitonally it would make more sense to do it before your loop/array rather than executing the same SQL query twice in a row.

3) UBound returns the Upper bound of an array index, not the count. Therefore looping to UBound()-1 means yourthrowing out the last record, which would likely be incorrect:

for j=0 to ubound(aBookings,1)-1

4) When writing a single-line If/Then statement it is unnecessary to supply the End If:

if sCurrentReg <> "" then response.Write(vbcrlf) end if

As far as your error is concerned, output the raw array data to the page. That way you cane see the raw data anddetermine if somehow a car id wandered into a date field, or if somehow your array got mis-built.

-T

 

[damber]

Tarwn,

When writing a single-line If/Then statement it is unnecessary to supply the End I

That would be me - I like things to be uniform and syntactically complete - it just helps to give a visual cue that the statement is complete - when you're skimming through debugging, it helps to line up if / end if to ensure things are where they are supposed to b, whilst still keeping the code short and readable. As you note, it is not necessary, just personal preference.

I'm the same with Java/C#....
never:
if (x==y)
x++;

always with brackets:
if (x==y)
{ x++; }
or
if (x==y) { x++; }
etc

no idea why it bugs me so much doing it the otherway

UBound returns the Upper bound of an array index, not the count.

You're right it does.. but I think you've overlooked the starting value of j. The array Ubound value would be 7 in the example I gave.. as there are 7 'rows', therefore if you start at 0 (arrays base index) you need to remove one from the ubound to not go passed the upper index of 6.


mthompo,

Read Tarwn's point 1 - the column and rows are the opposite way round when using getrows to build the array.
Google has some useful links about getrows

Also look at his last comment - writing the values of data out whilst debugging can really help you understand what's going on.

And look at the error, it is complaining about a value: "HJ06TYH" when comparing the dates - this unlikely what you intended...

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

are your i and js the right way round?
i = row
j = column

so in that line
(aBookings(j,2)

it is looking at row2 - am i on the right track?

got my rs into an array with getrows and printed it out
in a table with i and j next to each value using asp101 example

iRecFirst   = LBound(aBookings, 2)
iRecLast    = UBound(aBookings, 2)
iFieldFirst = LBound(aBookings, 1)
iFieldLast  = UBound(aBookings, 1)

<table border="1">
<%
' Loop through the records (second dimension of the array)
For I = iRecFirst To iRecLast
Response.Write "<tr>" & vbCrLf	
For J = iFieldFirst To iFieldLast
Response.Write vbTab & "<td>" & aBookings(J, I) & "j="& j & "i=" &i & "</td>" & vbCrLf
Next ' J
Response.Write "</tr>" & vbCrLf
Next ' I
%>
</table>

 

[damber]

The Array I constructed was referenced like this:
Array(rows, colums)

The GetRows function returns an array like this:
Array(columns, rows)

with that knowledge you should be able to work out how to change my code example to work with an array of the same dimensions (and order of columns) using getrows.

the layout of an array can be whatever you like.. e.g. Array(x,y,z) or Array(Category, SubCat, Topic, SubTopic) or any other combination you like, but GetRows returns a specific organisation. In my opinion GetRows is backwards, as it makes more logical sense to put the rows as the first dimension (it is a higher order object) - but it really doesn't matter.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

i was just getting the hang of arrays and now
trying to change your code is making my brain hurt!

is it a case of swapping all the i and js?
what about the loops do they still need changing?

 

[damber]

The i's and j's in your example have nothing to do with the i's and j's in my example... my last post explains what you need to consider.

Have you run the code ?

It works.

Which means...

a) you need to produce an array of the same type (e.g. structure, order etc)
b) getrows will do pretty much that
c) however, your field positions (columns) will not be the same (you will need to see everywhere a column is referenced to change it to the index of the column in your output)
d) getrows doesn't create rows/columns it creates columns/rows - again, this should be simple.. think about it.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

hi - switched columns with rows and got it to work, am learning alot

am having trouble with this line

for j=0 to ubound(aBookings,1)-1

at the moment it only displays two cars - unless there are no bookings in which case it works fine

SQL = "SELECT cus.lname, CC.reg, CB.bookst, CB.bookend "_   
& "FROM courtcar AS CC "_   
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_   & " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid "_   
& " LEFT JOIN cus ON cus.cusid = CB.cusid "_
& " AND CB.bookst <= '" & dstartdate & "' AND CB.bookend >= '" & denddate & "' " _
& "ORDER BY CC.carid, CB.bookst"  

dStartDate = DateSerial(2006,08,01)
dEndDate = DateSerial(2006,08,07)

response.ContentType = "text/plain" 
Set RS = Conn.Execute(sql)
aBookings = rs.GetRows()

rs.Close
Set rs = Nothing

Response.Write("          |  ")
for i=0 to DateDiff("d",dStartDate,dEndDate)
Response.Write(dateadd("d",i,dStartDate) & "  |  ")
next
Response.Write(vbcrlf)

sCurrentReg = ""  

for j=0 to ubound(aBookings,1)-1  'iterates through each row in the recordset/array
if sCurrentReg <> "" then response.Write(vbcrlf) end if
sCurrentReg = aBookings(1,j)
Response.Write(sCurrentReg & ":  |  ")
    for i=0 to DateDiff("d",dStartDate,dEndDate)
        dCurrDate = DateAdd("d", i, dStartDate)
        if isnull(aBookings(2,j)) or isnull(aBookings(3,j)) then response.Write(padText("Free") & "  |  ")
        else
if datediff("d",dCurrDate,(aBookings(2,j))) <= 0 and _
datediff("d", dCurrDate,(aBookings(3,j))) >= 0 then
response.Write(padText(aBookings(0,j)) & "  |  ")  
            else
if aBookings(1,j+1) = sCurrentReg then 
if datediff("d", dCurrDate , aBookings(3,j)) <= 0 then 
j = j + 1                         
i = i - 1   
else
response.Write(padText("Free") & "  |  ")    
end if
else
response.Write(padText("Free") & "  |  ")
                end if
            end if
        end if
    next
Next

function padText(sTxt)
    dim iLen : iLen = 10
    padText = left(sTxt & string(iLen," "), iLen)
end function

 

[damber]

for j=0 to ubound(aBookings,1)-1  'iterates through each row in the recordset/array

Read the comment.. it is using the number of rows to iterate. However, look at which of the dimensions ubound is using to get the ubound index. Then consider the way the getrows array is constructed.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

changed the dimension to 2

for j=0 to ubound(aBookings,2)

whch works ok as long as you dont book
slots for the last car in the courtcar table!!

 

[damber]

You'll have to explain some more, the code I posted shows the last entry fine, whether it has a booking or not.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

as soon as i add a booking for the last car that is in the courtcar table i get this error

Subscript out of range: '[number: 11]'
/mfit/courtcardamber.asp, line 90

line

if aBookings(1,j+1) = sCurrentReg then

let me know if im not making sense

 

[damber]

You should wrap that condition inside a check if the current record is the last record - e.g. compare j with ubound(...,2) -as it is essentially a lookahead and the next record may not be there (if it is the end of the array).

You could also change the logic around to remove the need for the look ahead, but that may jus confuse you unnecessarily.

(btw, your post before last omits the -1 at the end of the line... is this just a typo ?)

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

if leave the -1 it removes the last car from the list
could this be what is causing the trouble?