469,327 Members | 1,226 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

ASP Code Help

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

%>
Jul 19 '05 #1
3 2079
J P Singh wrote:
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 ... ?

<snip>
strSQL = "SELECT UserID, FDate, TDate FROM Holidays "


Change this to:
strSQL = "SELECT " & _
"FirstName & ' ' & LastName & ' - ' & Department As [User], " & _
"FDate, TDate FROM Holidays "
HTH,
Bob Barrows
Jul 19 '05 #2
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
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:uZ**************@TK2MSFTNGP12.phx.gbl...
J P Singh wrote:
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 ... ?

<snip>
strSQL = "SELECT UserID, FDate, TDate FROM Holidays "


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

Jul 19 '05 #3
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

J P Singh wrote:
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
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:uZ**************@TK2MSFTNGP12.phx.gbl...
J P Singh wrote:
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 ... ?

<snip>
strSQL = "SELECT UserID, FDate, TDate FROM Holidays "


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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by ChrisR | last post: by
6 posts views Thread by Mark Reed | last post: by
7 posts views Thread by Timothy Shih | last post: by
13 posts views Thread by gonzlobo | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.