error '80020009'

[mah-skeet]

I have the following:

sqlstr = "SELECT Budgets.Cilcode, Budgets.[Cost Centre], Sum([Day]/60.0/8.0)
AS TSDays FROM (CIL.dbo.TIMEBIDTS INNER JOIN CIL.dbo.TIMEBIDUSERDEFTS ON
CIL.dbo.TIMEBIDTS.taskID = CIL.dbo.TIMEBIDUSERDEFTS.TaskID) INNER JOIN
CIL.dbo.Budgets ON CIL.dbo.TIMEBIDUSERDEFTS.BudgetID = CIL.dbo.Budgets.ID
WHERE (((TIMEBIDTS.Refdate)<'" &
MedDate(dateadd("m",1,request.form("RefDate"))) & "')) GROUP BY
Budgets.Cilcode, Budgets.[Cost Centre] HAVING (((Budgets.Cilcode)=" &
request.form("Cilcode") & ")) ORDER BY Budgets.[Cost Centre];"
rst.Open sqlstr, cnnSearch, 2,3

sqlstr2 = "SELECT CostCentre, TSDays FROM PMRResource WHERE CILCode=" &
request.form("Cilcode") & " and MonthID=" & request.form("MonthID") & " ORDER
BY CostCentre;"
rst2.Open sqlstr2, cnnSearch, 2,3

do while not rst2.eof
if rst2("CostCentre") = rst("Cost Centre") then
rst2("TSDays") = rst("TSDays")
if not rst.eof then
rst.movenext
end if
else
rst2("TSDays") = 0
end if
rst2.movenext
loop

I'm getting the error on the line - "if rst2("CostCentre") = rst("Cost
Centre") then"

I don't know why becuase it works sometimes, but not others

Thanks in advance for the help.

Mark

[Bob Barrows]

I don't understand why you did not tell us the text of the error message.
Sure, we can google it and find out ourselves ... _but so can't you!_ And of
course, the benefit to you is that you might have already found the answer
to your problem ...

Probably nothing to do with your problem, but what I can comment on is this:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Select statement:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

mah-skeet wrote:
> I have the following:

Here is the first result from a google search. Does it help?

[Dan]

"mah-skeet" <mahs...@discussions.microsoft.com> wrote in message
news:8D8C4433-EEB7-47E9...@microsoft.com...

Your code doesn't take into account what happens when rst is EOF, which is
why you get the error. You need to add another If statement in before you
compare the fields.

do while not rst2.eof
'check rst is not eof here!
if not rst.eof then
'your code from above
else
'what to do when rst is eof
end if
rst2.movenext
loop

--
Dan

[Bob Barrows]

Bob Barrows wrote:
> mah-skeet wrote:
>> I have the following:
>
> Here is the first result from a google search. Does it help?

Oops! Somehow forgot the link! Here goes:
http://classicasp.aspfaq.com/general/why-do-i-get-80020009-errors.html

--
HTH,
Bob Barrows