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("URL"
strSearch = Request.QueryString("search"
strSearch = Replace(strSearch, "'", "''"
%>
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="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" type="text">
<input type="submit">
</form>
<%
If strSearch <> "" Then
Set cnnSearch = Server.CreateObject("ADODB.Connection"
cnnSearch.open="Provider=SQLOLEDB;Data Source=HGSNQ002;User Id=devpw;Password=pwdev"
strSQL = "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 '%" & Replace(Right(strSearch,5), "'", "''" & "%' OR AclaimID LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Client LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Mfnam LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Mlnam LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb1) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb2) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb3) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR VIN LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Dte LIKE '%" & Replace(strSearch, "'", "''" & "%' ORDER BY ID DESC;"
Set rstSearch = cnnSearch.Execute(strSQL)
%>
<TABLE CELLPADDING=4 CELLSPACING=0 WIDTH=100% BORDER=1 BORDERCOLOR="black"><TR>
<TD CLASS="title">PO #</TD>
<TD CLASS="title">Aclaim ID</TD>
<TD CLASS="title">Client</TD>
<TD CLASS="title">Call Back #</TD>
<TD CLASS="title">Member First Name</TD>
<TD CLASS="title">Member Last Name</TD>
<TD CLASS="title">VIN</TD>
<TD CLASS="title">Date Created</TD>
</TR>
<%
Do while not rstSearch.eof
%>
<TR onmouseover="MouseOver(this)" onmouseout="MouseOut(this)" onclick="MouseClick(this)">
<TD CLASS="po" ALIGN=center><a href="POView.asp?ID=<%=rstSearch.Fields("ID".Value%>">92<%=rstSearch.Fields("ID".Value%></a></TD>
<TD><%=rstSearch.Fields("AclaimID".Value%></TD>
<TD><%=rstSearch.Fields("Client".Value%></TD>
<TD><%=rstSearch.Fields("callb1".Value%> <%=rstSearch.Fields("callb2".Value%> <%=rstSearch.Fields("callb3".Value%></TD>
<TD><%=rstSearch.Fields("Mfnam".Value%></TD>
<TD><%=rstSearch.Fields("Mlnam".Value%></TD>
<TD><%=rstSearch.Fields("VIN".Value%></TD>
<TD><%=rstSearch.Fields("Dte".Value%></TD>
</TR>
<%
rstSearch.MoveNext
Loop
%>
</TABLE>
<%
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
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("URL"
strSearch = Request.QueryString("search"
strSearch = Replace(strSearch, "'", "''"
%>
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="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" type="text">
<input type="submit">
</form>
<%
If strSearch <> "" Then
Set cnnSearch = Server.CreateObject("ADODB.Connection"
cnnSearch.open="Provider=SQLOLEDB;Data Source=HGSNQ002;User Id=devpw;Password=pwdev"
strSQL = "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 '%" & Replace(Right(strSearch,5), "'", "''" & "%' OR AclaimID LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Client LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Mfnam LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Mlnam LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb1) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb2) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Convert(VarChar, callb3) LIKE '%" & Replace(strSearch, "'", "''" & "%' OR VIN LIKE '%" & Replace(strSearch, "'", "''" & "%' OR Dte LIKE '%" & Replace(strSearch, "'", "''" & "%' ORDER BY ID DESC;"
Set rstSearch = cnnSearch.Execute(strSQL)
%>
<TABLE CELLPADDING=4 CELLSPACING=0 WIDTH=100% BORDER=1 BORDERCOLOR="black"><TR>
<TD CLASS="title">PO #</TD>
<TD CLASS="title">Aclaim ID</TD>
<TD CLASS="title">Client</TD>
<TD CLASS="title">Call Back #</TD>
<TD CLASS="title">Member First Name</TD>
<TD CLASS="title">Member Last Name</TD>
<TD CLASS="title">VIN</TD>
<TD CLASS="title">Date Created</TD>
</TR>
<%
Do while not rstSearch.eof
%>
<TR onmouseover="MouseOver(this)" onmouseout="MouseOut(this)" onclick="MouseClick(this)">
<TD CLASS="po" ALIGN=center><a href="POView.asp?ID=<%=rstSearch.Fields("ID".Value%>">92<%=rstSearch.Fields("ID".Value%></a></TD>
<TD><%=rstSearch.Fields("AclaimID".Value%></TD>
<TD><%=rstSearch.Fields("Client".Value%></TD>
<TD><%=rstSearch.Fields("callb1".Value%> <%=rstSearch.Fields("callb2".Value%> <%=rstSearch.Fields("callb3".Value%></TD>
<TD><%=rstSearch.Fields("Mfnam".Value%></TD>
<TD><%=rstSearch.Fields("Mlnam".Value%></TD>
<TD><%=rstSearch.Fields("VIN".Value%></TD>
<TD><%=rstSearch.Fields("Dte".Value%></TD>
</TR>
<%
rstSearch.MoveNext
Loop
%>
</TABLE>
<%
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>