Helpful Information
 
 
Category: ASP
i get error when i do many looping in a ASP pages

dear,

i'm doing a event calendar(like urs) when i do looping in ASP, i get previous few records in 1st looping and then the rest looping(is it bcos of the number of record? bcos it may have many records).
i get error... the error is:

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.


how???? is it need to set the range SQL server timeout more longer ? how to set it at ASP page?

can you post the code?

dear,

this code hav 3 loops. my friends say try to convert the three loops to array first. but i donno how? can u help me?

<!--#include file="../header.asp"-->

<html>
<head>
<title>Calendar</title>
</head>
<body bgcolor="#EAEAD5">
<%


dim navmonth,navyear,firstday,leapTestNumbers,leapTest,lastDate,lastMonth,lastYear,nextMonth,nextYear
navmonth = request.querystring("month")
navyear = request.querystring("year")

If navmonth = "" Then
navmonth = Month(Date)
End If
If navyear = "" Then
navyear = Year(Date)
End If
firstday = Weekday(CDate(navmonth & "/" & 1 & "/" & navyear))
leapTestNumbers = navyear / 4
leapTest = (leapTestNumbers) - Round(leapTestNumbers)
If navmonth = 2 Then
If leapTest <> 0 Then
lastDate = 28
Else
lastDate = 29
End If
ElseIf ((navmonth = 4) OR (navmonth = 6) OR (navmonth = 9) OR (navmonth = 11)) Then
lastDate = 30
Else
lastDate = 31
End If

lastMonth = navmonth - 1
lastYear = navyear
If lastMonth < 1 Then
lastMonth = 12
lastYear = lastYear - 1
End If

nextMonth = navmonth + 1
nextYear = navyear
If nextMonth >12 Then
nextMonth = 1
nextYear = nextYear + 1
End If

dim dateCounter,weekCount,DateEnd,DateBegin,firstDate
dateCounter = 1
weekCount = 1
DateEnd = lastDate
DateBegin = firstDate

%>
<table border="1" cellpadding="0" cellspacing="0" align="center">
<tr>
<td align="center">
<table border="0" cellpadding="2" cellspacing="0" width="100%">
<tr>
<td align="left"><a href="calendar.asp?month=<%=lastMonth%>&year=<%=lastYear%>"><%=MonthName(lastMonth)%></a></td>
<td align="center"><%=MonthName(navMonth)%>&nbsp;<%=navyear%></b></font></td>
<td align="right"><a href="calendar.asp?month=<%=nextMonth%>&year=<%=nextYear%>"><%=MonthName(nextMonth)%></a></td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" align="center">
<tr>
<td>
<table border="1" cellpadding="2" cellspacing="0" bordercolor="#ffffff">
<tr>
<td width=90 align="center"> Sunday</td>
<td width=90 align="center"> Monday</td>
<td width=90 align="center"> Tuesday</td>
<td width=90 align="center"> Wednesday</td>
<td width=90 align="center"> Thursday</td>
<td width=90 align="center"> Friday</td>
<td width=90 align="center"> Saturday</td>
</tr>
<tr>
<%

' Retrieve record from Calendar Table.
sub CalendarEvent()
dim RSEVENT,rsdate,rsbody,conn
Conn = int_Databasepath
Set RSEVENT = Server.CreateObject("ADODB.RecordSet")
RSEVENT.Open "SELECT * FROM Calendar where month(CalDate)='"&navmonth&"'", Conn, 1, 3
Do while NOT RSEVENT.EOF
rsdate = RSEVENT("CalDate")
rsbody = trim(RSEVENT("Subject"))
If (Day(rsdate) = dateCounter) AND (Month(rsdate) = CInt(navmonth)) AND (Year(rsdate) = CInt(navyear)) Then
Response.write rsbody&"<Br>"
End If
RSEVENT.movenext
Loop
RSEVENT.close

end sub

' Retrieve record from Holidays Table.
SUB CalendarHolidays()
dim strConn,objconn,strSQL,objREC,dtSelected
strconn = int_Databasepath
set objconn = server.createobject("adodb.connection")
objconn.open strconn
strsql = "select * from Holidays"
set objrec = objconn.execute(strsql)
do while not objrec.eof
dim rsholday,rsholdesc
rsholday = objrec("FDate")
rsholdesc = trim(objrec("FDESC"))
If (Day(rsholday) = dateCounter) AND (Month(rsholday) = CInt(navmonth)) AND (Year(rsholday) = CInt(navyear)) Then
response.write "<img src='../images/blinkbullet_ani.gif' ALT='"&rsholdesc&"' align='absmiddle'>"
response.write rsholdesc
response.write "<BR>"
end if
objrec.movenext
Loop
objrec.close
END SUB

' Retrieve record from EVENT Table.
sub Events()
dim RSEVT,rsEvtdate,rsEvtbody,conn,adstateopen
Conn = int_Databasepath
Set RSEVT = Server.CreateObject("ADODB.RecordSet")
RSEVT.Open "SELECT * FROM EventMaster where month(FEVENTDATE)='"&navmonth&"'", Conn, 1, 3
Do while NOT RSEVT.EOF
rsEvtdate = RSEVT("FEVENTDATE")
rsEvtbody = trim(RSEVT("FEVENTTITLE"))
If (Day(rsEvtdate) = dateCounter) AND (Month(rsEvtdate) = CInt(navmonth)) AND (Year(rsEvtdate) = CInt(navyear)) Then
Response.write rsEvtbody&"<Br>"
End If
RSEVT.movenext
Loop
'RSEVT.close
end sub
' Get today date and bgcolor.
SUB getTodayDate()
dim todaydate
todayDate =month(now())&"/"&Day(now())&"/"&Year(Now())
if dateSelect = todayDate then
strbgcolor="gold"
else
strbgcolor="#0099ff"
end if
END SUB
'dim conn

dim newdate,dateSelect,strBGCOLOR
Do while weekCount <= 7
dateSelect = navmonth & "/" & dateCounter & "/" & navyear
If (weekCount < firstDay) OR (dateCounter > lastDate) Then
Response.write "<td height=90 bgcolor=#666666>&nbsp;</td>" 'Blank day
else
CALL getTodayDate()
Response.write "<td height=90 valign=top bgcolor="&strbgcolor&"><a href='Calendar.asp?date="&dateSelect&"'>"&dateCounter&"</a><br>"
CALL Events()
CALL CalendarEvent()
CALL CalendarHolidays()
Response.write "</td>"&vbcrlf
dateCounter = dateCounter + 1
end if
weekCount = weekCount + 1
Loop
weekCount = 1
%>
</tr>
<% Do while dateCounter <= lastDate %>
<tr>
<%
Do while weekCount <= 7
dateSelect = navmonth & "/" & dateCounter & "/" & navyear
If dateCounter > lastDate Then
RESPONSE.WRITE "<td height=90 bgcolor=#666666>&nbsp;</td>" 'Blank
else
CALL getTodayDate()
RESPONSE.WRITE "<td height=90 bgcolor="&strBGCOLOR&" valign=top><a href='Calendar.asp?date="&dateSelect&"'>"&dateCounter&"</a><br>"
CALL Events()
CALL CalendarEvent()
CALL CalendarHolidays()
RESPONSE.WRITE "</td>"&vbcrlf
dateCounter = dateCounter + 1
end if
weekCount = weekCount + 1
Loop
weekCount = 1
%>
</tr>
<% Loop %>
</table>

</td>
</tr>
</table>

</td>
</tr>
</table>
</body>
</html>

you have a variable int_Databasepath, maybe it was set in the include file. I believe it's the connection string containing the DSN name. This is what you did in CalendarHolidays():


strconn = int_Databasepath
set objconn = server.createobject("adodb.connection")
objconn.open strconn


this is correct but in other subroutines, you called it this way:


Conn = int_Databasepath
Set RSEVT = Server.CreateObject("ADODB.RecordSet")
RSEVT.Open "SELECT * FROM EventMaster where month(FEVENTDATE)='"&navmonth&"'", Conn, 1, 3

in the above code, you passed a string instead of a connection object.

You only need 1 db connection in a single page.
So you need to make a global connection object variable

dim objConn
strconn = int_Databasepath
set objConn = server.createobject("ADODB.Connection")
objConn.open strconn

put this at the top of the page then in your subroutines that open recordset, just pass the parameter objConn

dear,

it not solve my problem actually! the looping still not work properly. the error still occured. may be the records are too many... :(

maybe you can insert response.write's inside the loops to see where it stopped...

can you post the revised code again...
also include the error message displayed and tell me exactly what statement contains the error.


Originally posted by NinjaTurtle
dear,

it not solve my problem actually! the looping still not work properly. the error still occured. may be the records are too many... :(

i will not be able to help you solve your problem now, maybe tomorrow. gtg, it's 5:30 PM here.
bye NinjaTurtle :)

Dear,

i follow what u mean(may be i get wrong mssg from u, my fault!), but still get back the same error, then later i do some modification, and i get the expected result... but here want to thanks :thumbsup: glenngv. :thumbsup:

which with "**********" is the changes.

sub Tasks()
dim RSTASK,rsTaskdate,rsTaskbody, strSQL
' **********Changes is start from here******************
strSQL = "SELECT * FROM TaskMaster where month(FTASKASSIGNON)='"&navmonth&"' AND FTASKASSIGNTO='"&Session("LHHBUSERID")&"'"
set RSTASK = objConn.Execute(strSQL)
' **********Changes is end to here*********************
Do while NOT RSTASK.EOF
rsTaskdate = RSTASK("FTASKASSIGNON")
rsTaskbody = trim(RSTASK("FTASKTITLE"))
If (Day(rsTaskdate) = dateCounter) AND (Month(rsTaskdate) = CInt(navmonth)) AND (Year(rsTaskdate) = CInt(navyear)) Then
Response.write "^"&rsTaskbody&"<Br>"
End If
RSTASK.movenext
Loop
RSTASK.close
end sub










privacy (GDPR)