ADODB.Recordset error 800a0cc1

[sfalin]

I'm stumped. I've gone through all the code, the fields are spelled correctly (incl. case) and are definately columns in my table, but I get that stupid "Item cannot be found in the collection corresponding to the requested name or ordinal". Any ideas? My code is below.
Also, let me say this. I didn't design the database, so I've had to rig a few things (Conversions); so, don't blame me for apparent poor DB design.

<%
Dim strURL
Dim cnnSearch
Dim rstSearch
Dim strSQL
Dim strSearch

strURL = Request.ServerVariables(&quot;URL&quot

strSearch = Request.QueryString(&quot;search&quot
strSearch = Replace(strSearch, &quot;'&quot;, &quot;''&quot
%>
Search for Manual PO by PO#, Aclaim ID, Client, Member First Name, Member Last Name, Last 4 of Call Back #, VIN, or Date Entered (% returns all):<BR>
<form action=&quot;<%= strURL %>&quot; method=&quot;get&quot;>
<input name=&quot;search&quot; value=&quot;<%= strSearch %>&quot; type=&quot;text&quot;>
<input type=&quot;submit&quot;>
</form>
<%
If strSearch <> &quot;&quot; Then
Set cnnSearch = Server.CreateObject(&quot;ADODB.Connection&quot
cnnSearch.open=&quot;Provider=SQLOLEDB;Data Source=HGSNQ002;User Id=devpw;Password=pwdev&quot;

strSQL = &quot;SELECT Convert(VarChar, ID), AclaimID, Client, Mfnam, Mlnam, Convert(VarChar, callb1), Convert(VarChar, callb2), Convert(VarChar, callb3), VIN, Dte FROM tblPOForm WHERE Convert(VarChar, ID) LIKE '%&quot; & Replace(Right(strSearch,5), &quot;'&quot;, &quot;''&quot & &quot;%' OR AclaimID LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Client LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Mfnam LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Mlnam LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb1) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb2) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb3) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR VIN LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Dte LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' ORDER BY ID DESC;&quot;

Set rstSearch = cnnSearch.Execute(strSQL)

%>
<TABLE CELLPADDING=4 CELLSPACING=0 WIDTH=100% BORDER=1 BORDERCOLOR=&quot;black&quot;><TR>
<TD CLASS=&quot;title&quot;>PO #</TD>
<TD CLASS=&quot;title&quot;>Aclaim ID</TD>
<TD CLASS=&quot;title&quot;>Client</TD>
<TD CLASS=&quot;title&quot;>Call Back #</TD>
<TD CLASS=&quot;title&quot;>Member First Name</TD>
<TD CLASS=&quot;title&quot;>Member Last Name</TD>
<TD CLASS=&quot;title&quot;>VIN</TD>
<TD CLASS=&quot;title&quot;>Date Created</TD>
</TR>
<%
Do while not rstSearch.eof
%>
<TR onmouseover=&quot;MouseOver(this)&quot; onmouseout=&quot;MouseOut(this)&quot; onclick=&quot;MouseClick(this)&quot;>
<TD CLASS=&quot;po&quot; ALIGN=center><a href=&quot;POView.asp?ID=<%=rstSearch.Fields(&quot;ID&quot.Value%>&quot;>92<%=rstSearch.Fields(&quot;ID&quot.Value%></a></TD>
<TD><%=rstSearch.Fields(&quot;AclaimID&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;Client&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;callb1&quot.Value%> <%=rstSearch.Fields(&quot;callb2&quot.Value%> <%=rstSearch.Fields(&quot;callb3&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;Mfnam&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;Mlnam&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;VIN&quot.Value%></TD>
<TD><%=rstSearch.Fields(&quot;Dte&quot.Value%></TD>
</TR>
<%
rstSearch.MoveNext
Loop
%>
</TABLE>
<%
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>

 

[alsoknownas]

Type the error# in the search box to see if you find a solution to your problem...

 

[SarkMan]

Try giving your converted values columns names
i.e.


strSQL = &quot;SELECT Convert(VarChar, ID) as ID, AclaimID, Client, Mfnam, Mlnam, Convert(VarChar, callb1) as callb1, Convert(VarChar, callb2) as callb2, Convert(VarChar, callb3) as callb3, VIN, Dte FROM tblPOForm WHERE Convert(VarChar, ID) LIKE '%&quot; & Replace(Right(strSearch,5), &quot;'&quot;, &quot;''&quot & &quot;%' OR AclaimID LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Client LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Mfnam LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Mlnam LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb1) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb2) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Convert(VarChar, callb3) LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR VIN LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' OR Dte LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot & &quot;%' ORDER BY ID DESC;&quot;

 

[alsoknownas]

sorry, forgot the url

http://www.aspfaq.com

 

[onpnt]

have you throw that into query analyzer to see what it says about it? sometimes this error can be the result of a missing ) or ' &quot;. the reasoning for it sometimes being that is the opener not being closed or even opened causes the names to run together.

_____________________________________________________________________

http://www.onpntwebdesigns.com

 

[sfalin]

Thanks guys!! Giving the converted values column names did it for me. I really appreciate your time.

 

[xaviero]

insert this BEFORE your cnnSearch.Execute(strSQL) and run it to see what your result sql statement would be this way you know exactly what is being sent to your sql server

response.write strSQL
response.end