ASP Code Help

[J P Singh]

Hi All

I am not an ASP expert but some kind soul gave me this code to create a
visual display for the dates stored in a table. It works fine except I now
want to modify it to have the Name and department of the users displayed and
not display the user id field.

The field names in the table are

FirstName
LastName
Department

Can anyone help please?

Thanks a lot.

Regards

Jas


<%@ Language=vbScript %>
<%
dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
dim iDays, iStartDay, iEndDay
dim lCurUser, lNewUser
dim i, j, dHolStart, dHolEnd,k
const cUser = 0
const cFrom = 1
const cTo = 2
const cHoliday = "yellow"
const cWorkday = "white"

iMth=request.form("mth")
if len(iMth) = 0 then iMth = 7
iYr=request.form("yr")
if len(iYr) = 0 then iYr = 2003

'validate them, then
dStart=dateserial(iYr,iMth,1)
dEnd=dateadd("m",1,dStart)
dEnd= dateadd("d",-1,dEnd)
iDays = Day(dEnd)
Redim arDates(iDays)
'this is effectively a one-based array - we will ignore the
'zero element in later code

set cn=server.CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("Includes\holidays.mdb")
set rs=server.createobject("adodb.recordset")
strSQL = "SELECT UserID, FDate, TDate FROM Holidays "
strSQL = strSQL & " WHERE (TDate Between #" & dStart & "# and #" & dEnd &
"#) "
strSQL = strSQL & " and (FDate Between #" & dStart & "# and #" & dEnd & "#)
"

response.write strSql & "<br>"



cn.qGetHolidays dStart,dEnd, rs
if not rs.eof then
arData=rs.Getrows
end if
rs.close
set rs=nothing
cn.close
set cn=nothing
if not isArray(arData) then
response.write "No records returned"
else
' stop
' for i=0 to ubound(arData,2)
' for j=0 to ubound(arData,1)
' Response.Write arData(j,i) & "; "
' next
' Response.Write "<BR>"
' next
' Response.End
'pass arDates and dStart to a sub that creates your
'table heading
CreateHeading arDates, dStart
lCurUser = arData(cUser,0)
for i = 1 to iDays
arDates(i) = cWorkday
next
for i = 0 to ubound(arData,2)
lNewUser = arData(cUser,i)
if lCurUser <> lNewUser then
'pass arDates and lCurUser to a sub that creates the
'table row for the user
CreateUserRow arDates,lCurUser
lCurUser = lNewUser
for j = 1 to iDays
arDates(j) = cWorkday
next
end if
dHolStart = CDate(arData(cFrom,i))
iStartDay = Day(dHolStart)
dHolEnd= CDate(arData(cTo,i))
iEndDay= Day(dHolEnd)
if dHolEnd <= iStartDay then iStartDay =1
For j = iStartDay to iEndDay
arDates(j) = cHoliday
next
next
CreateUserRow arDates,lCurUser
response.write "</table>"
erase arData
erase arDates
end if

Sub CreateHeading(pAr,pStart)
dim i
response.write "<br><br>"
response.write "<div align=""center"">" & _
"<center>"

response.write "<TABLE border=1 style=""border-collapse:collapse"">" & _
"<TR><TH colspan="
response.write ubound(pAr) + 1
response.write " align = center>Holidays for "
response.write monthname(month(pStart)) & " " & year(pStart)
response.write "</TH></TR><TR><TH>"
response.write "User ID" & "</TH>"
for i = 1 to ubound(pAr)
response.write "<TH width=15>" & i & "</TH>"
next
response.write "</TR>"
end sub

sub CreateUserRow(pAr, pUser)
dim i
response.write "<tr><th>" & pUser & "</th>"
for i = 1 to ubound(pAr)
response.write "<td bgcolor= "
response.write pAr(i)
response.write ">&nbsp;&nbsp;</td>"
next
response.write "</tr>"
end sub

%>

 

[Bob Barrows]

Hi All

I am not an ASP expert but some kind soul gave me this code to create
a visual display for the dates stored in a table. It works fine
except I now want to modify it to have the Name and department of the
users displayed and not display the user id field.

This was for Access, right?

The field names in the table are

FirstName
LastName
Department

I guess I have to assume that these are in the Holidays table and not some
other related table ... ?

strSQL = "SELECT UserID, FDate, TDate FROM Holidays "

Change this to:
strSQL = "SELECT " & _
"FirstName & ' ' & LastName & ' - ' & Department As [User], " & _
"FDate, TDate FROM Holidays "


HTH,
Bob Barrows

 

[J P Singh]

Hi Bob

I remember you wrote the code initially.

You are right the database is access.

The Firstname, LastName and Department do come from a different table

This is how it is made up

EmpProfile(Table)

EmployeeNumber
FirstName
LastName
Department

Holidays(Table)

UserId
Fdate
TDate

If possible can I email you the database. The code actually works perfectly
well and all I want to do it to display the name of the person instead of
the employee number

Cheers

Jas

Hi All

I am not an ASP expert but some kind soul gave me this code to create
a visual display for the dates stored in a table. It works fine
except I now want to modify it to have the Name and department of the
users displayed and not display the user id field.

This was for Access, right?

The field names in the table are

FirstName
LastName
Department

I guess I have to assume that these are in the Holidays table and not some
other related table ... ?

strSQL = "SELECT UserID, FDate, TDate FROM Holidays "

Change this to:
strSQL = "SELECT " & _
"FirstName & ' ' & LastName & ' - ' & Department As [User], " & _
"FDate, TDate FROM Holidays "


HTH,
Bob Barrows

 

[Bob Barrows]

No, I don't need the database, assuming that the two tables can be joined by
the userid and employeenumber fields. The query should be changed to this:

Select FirstName & ' ' & LastName & ' - ' & Department As [User],
FDate, TDate FROM Holidays h INNER JOIN EmpProfile e
ON h.UserID = e.EmployeeNumber
WHERE ...

Test this in Access to see the result.

HTH,
Bob Barrows

Hi Bob

I remember you wrote the code initially.

You are right the database is access.

The Firstname, LastName and Department do come from a different table

This is how it is made up

EmpProfile(Table)

EmployeeNumber
FirstName
LastName
Department

Holidays(Table)

UserId
Fdate
TDate

If possible can I email you the database. The code actually works
perfectly well and all I want to do it to display the name of the
person instead of the employee number

Cheers

Jas

Hi All

I am not an ASP expert but some kind soul gave me this code to
create
a visual display for the dates stored in a table. It works fine
except I now want to modify it to have the Name and department of
the users displayed and not display the user id field.

This was for Access, right?

The field names in the table are

FirstName
LastName
Department

I guess I have to assume that these are in the Holidays table and
not some other related table ... ?

strSQL = "SELECT UserID, FDate, TDate FROM Holidays "

Change this to:
strSQL = "SELECT " & _
"FirstName & ' ' & LastName & ' - ' & Department As [User], " & _
"FDate, TDate FROM Holidays "


HTH,
Bob Barrows