help with getting drop down working

[minority]

hey all.

ok i been trying to get this to work i can either get it not to work and dispaly nothing or it displays everything.

basically it does a search on department gets the dept name and then uses it for the field name to see if it has access to a relevent document type


<select name="DocType">
<%rsgeolink.open strSQL, strCon
Do until rsgeolink.EOF
deptname = rsgeolink("Dept_Name")
depttrue = "true"
strSQL2 = "SELECT * FROM Document_Type WHERE Doc_type_ID = "&Doctypeid&" AND ["&deptname&"] = "&depttrue&""
'response.write(rsgeolink2(""&deptname&""))
rsgeolink2.open strSQL2, strCon
if rsgeolink2("&deptname&") = "true" then
Response.Write("<option value=""" &deptname& """ > " &deptname& "</option>" & vbCrLf)
end if
rsgeolink2.close
rsgeolink.MoveNext()
loop
rsgeolink.close
%>
</select>


i am currently getting this error
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

on line 91 which is after i open sql2 (rsgeolink2.open strSQL2, strCon)

this line to be exact if rsgeolink2("&deptname&") = "true" then i dont know if it is the way i have tried to do the link to the database or what. but deptname can have spaces.

anyhelp would be great.

i know it poor practice but the field names have spaces inbetween words :(
so i put [] around them.

[Bullschmidt]

Perhaps try changing this:

strSQL2 = "SELECT * FROM Document_Type WHERE Doc_type_ID = "&Doctypeid&" AND ["&deptname&"] = "&depttrue&""

To be more like this instead:

strSQL2 = "SELECT * FROM Document_Type WHERE (Doc_type_ID = " & Doctypeid & ") AND (deptname = '" & deptname & "')"

Notice the single quotes around the deptname variable above.

And of course be sure that the table name is relaly Document_Type and the field names in the table are really Doc_type_ID and deptname.

And here's a related link:

Why do I get 800A0CC1 errors?
http://www.aspfaq.com/show.asp?id=2275

[minority]

no the name of the field isnt deptname if you look at my sql you willl notice i am taking it from a variable which is why it is like this '"&deptname&"' and this relates to either a true or false on the database.

[Bullschmidt]

Well in your code (["&deptname&"] =) and my proposed solution (deptname = ) deptname is assumed to be a field name in the database. And of course if it's not then that is a major part of your trouble and can be easily fixed right there. :)

[russell_g_1]

i think see what you're trying to do there. the columns are actually the names of the departments?

think the code should look something like this


<select name="DocType">

<%
rsgeolink.open strSQL, strCon

Do until rsgeolink.EOF

deptname = rsgeolink("Dept_Name")
depttrue = "true"

strSQL2 = "SELECT * FROM Document_Type WHERE Doc_type_ID = " & Doctypeid & " AND [" & deptname & "] = '" & depttrue & "'"

rsgeolink2.open strSQL2, strCon

if not rsgeolink2.eof then

if lcase(rsgeolink2(deptname) & "") = "true" then

Response.Write "<option value=""" & deptname & """>" & deptname & "</option>" & vbCrLf

end if

end if

rsgeolink2.close
rsgeolink.MoveNext()

loop

rsgeolink.close
%>

</select>

the differences are adding quotes around the "true" string in the sql, adding an EOF check, getting the field from the rs and lcase-ing the value. (shouldn't really need to check this if you've done it in the sql anyway).

is this anything like right? :)

[minority]

you hit it on the nail russell sorry for being confusing if i was.....long day tired and need a holiday lol...back to uni in 3 weeks thou :(

[minority]

works exactly how i pictured it would thanks alot that was the only part that was getting me in a fix