HELP - Item cannot be found in the collection 2

[LongFeiFengWu]

I have a SELECT statement that I'm using to generate a table. One of the variables in the statement comes from a form field "item". The form field is carrying the value to this page correctly and the column in the table does exist, but I'm getting the following error message when the code runs:

Item cannot be found in the collection corresponding to the requested name or ordinal.

I have marked the errored lines in red below. Any help is greatly appreciated.

<%

IF "" & request.form("D1") <> "" THEN

D1 = request.form("D1")
D2 = request.form("D2")
item = request.form("item")

SQL = "SELECT COUNT(ID) AS CID, "& item &" FROM Visits WHERE VisitDate BETWEEN #"& D1 &"# AND #"& D2 &"# "
SQL = SQL & "GROUP BY "& item &" "
objrec.open SQL,objcon,3,3

	IF objrec.recordcount > 0 THEN
	
	do while not objrec.eof
	
	Link = "<a href='/ChanInn/index.asp?page=listvisit&D1="& D1 &"&D2="& D2 &"&item="& [COLOR=red]objrec(request.form("item"))[/color] &"'>"
	
	CID = objrec("CID")
		
	response.write "<tr>"
	response.write "<td align=center colspan=2>"& LINK & [COLOR=red]objrec(request.form("item"))[/color] &"</td>"
	response.write "<td align=center colspan=2>"& CID &"</td>"
	response.write "</tr>"
	
	objrec.movenext
	
	loop
	
	END IF

END IF

%>

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[DotNetGnat]

how abt this:

SQL = "SELECT COUNT(ID) AS CID, "& item &" as blah FROM Visits WHERE VisitDate BETWEEN #"& D1 &"# AND #"& D2 &"# "
SQL = SQL & "GROUP BY "& item &" "

then use

objrec(request.form("blah"))

-DNG

 

[Sheco]

This is not a tip for your problem but rather just a general tip for this type of problem...

When you've got a disjunct between the fields that should be in your recordset and the fields that actually are in the recordset, it might help debug if you iterate through the .Fields collection:[tt]
<%
Response.Write "My recordset has these fields: "
i = 0
For Each oFld In objrec.Fields
Response.Write "Index: " & i & " Name: " & oFld.Name & "<BR>"
i = i + 1
Next
%>
[/tt]

 

[Tarwn]

Response.Write that form field. Make sure it doesn't have several fieldnames in it, a numerical value, etc. A list of form fdield would work in the sql statement but not as a fieldname for the recordset. A number would work in the SQL but would be retained as the value for an unnamed field for each row. Same with a value between single quotes, etc.

Follow Sheco's advice as well as Response.Write'ing your form field value. I would also Response.Write your SQL statement. With those three pieces of information you should have a complete view of what the code is working with.