ADODB.Fields error '800a0cc1' Item cannot be found in the collection
I need to create a select box with query data from my Database by using the query technique that I've done it successfully many times. However, today when I created the new select box, the page keep telling me that there're error "ADODB.Fields error '800a0cc1' Item cannot be found in the collection ....".
At first I thought it was my SQL query that made this error, however i've tested this on ACCES, it can produce the output as I expected. So now i'm stuck.. i don't know what happened to my coding.
So please help me on this.. thank you. below is my code
user_id = 1
client_id = 7
response.Write "user_id = " & user_id
Dim rsClient
set rsClient = Server.CreateObject("ADODB.Recordset")
rsClient.ActiveConnection = Conn_string
rsClient.Source ="SELECT tbl_asgnClient.* , tbl_client.* FROM tbl_asgnClient INNER JOIN tbl_client ON tbl_asgnClient.client_id = tbl_client.client_id WHERE tbl_asgnClient.user_id =" & user_id & ""
rsClient.CursorType = 0
rsClient.CursorLocation = 3
rsClient.LockType = 3
rsClient.Open()
rsClient.PageSize = 20
%>
<html>
<body>
<select name = "client_id" onChange="showActivity(this.value)">
<option selected>select client</option>
<%
if rsClient.eof then
call displayNotFoundRecord
Else
Do Until rsClient.Eof
%>
<option value="<%=rsClient.fields.item("client_id")%>"><%=rsClient.fields.item("client_id")%></option>
<%
rsClient.movenext
Loop
End if
%>
</select>
I can ensure that there're nothing else except SQL that can make this error.. because i've tried to change it SQL with below queries and it work just fine.
rsClient.Source ="SELECT tbl_asgnClient.* FROM tbl_asgnClient WHERE tbl_asgnClient.user_id =" & user_id & " "
or this ...
rsClient.Source ="SELECT tbl_client.* FROM tbl_client "
I'm also ensure that the once that I currently used is be able to produce the output in MS ACCESS. So please help me..
Answer
Your SQL has two columns with the same name - while the values will be the same, ADO doesn't know which field you want