p2p.wrox.com: Archives - access

access_asp thread: String Criteria Can't Find Record

Message #1 by "Azlin" <azlin08@t...> on Tue, 9 Apr 2002 14:35:02

Hello. Can anyone help me on how to code find record where value in the 
field is not in trim string.


Error Type:

ADODB.Recordset error '800a0bb9' 

Arguments are of the wrong type, are out of acceptable range, or are in 
conflict with one another. 

/FindChequeNumber.asp, line 26 


add here is part my code:
<%
  Option Explicit
  Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib" 
              FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -
->
<HTML>
<HEAD>
<TITLE>Using the Find Method to find a Cheque Number</TITLE>
</HEAD>
<BODY>

<% 
  Dim strDirector, strCriteria
  strDirector = Request.Form("ChequeNumber")

  Dim objRS
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "BankStmt", strConnect, adOpenStatic, adLockReadOnly, 
adCmdTable

  strCriteria = " '%"+ TRIM(objRS("RecNo"))+"%'=' " & strDirector & " ' "

  objRS.Find strCriteria       ' show the first relevant record
  If objRS.EOF Then
    Response.Write "The database does not contain any Cheques by the 
Cheque " & _
                    strDirector
  Else
    Response.Write "<H2>Directed by " & strDirector & ":</H2>" & _
         "<B>Chuque No:</B> "   & objRS("RecNo")   & "<BR>" & _
         "<B>Cleared Date:</B> " & objRS("TrxDate") & "<BR>" & _
         "<B>Amount:</B> "   & objRS("Amount") & "<BR><BR><BR>"
  End If
  objRS.Close
  Set objRS = Nothing
%>
</BODY>
</HTML>


any ideas?

Thanks!

Message #2 by "Rob Parkhouse" <rparkhouse@o...> on Wed, 10 Apr 2002 01:29:18

The Find method needs criteria containing a valid piece of SQL (the WHERE 
part)

your example:
  strCriteria = " '%"+ TRIM(objRS("RecNo"))+"%'=' " & strDirector & " ' "

does not look like it would evaluate to valid SQL. I suspect you should 
have the % surrounding the form variable strDirector, and if using % 
wildcard the operator would be LIKE, and not =.

perhaps it should be something like:

  strCriteria = TRIM(objRS("RecNo")) + " LIKE '%" & strDirector & "%' "

what is the value of objRS("RecNo")?? Maybe the criteria should be

  strCriteria = "RecNo LIKE '%" & strDirector & "%' "

I didn't quite understand what you are trying to do so if I have 
misunderstood and have not answered your question you could expand on the 
requirement.

Regards

> Hello. Can anyone help me on how to code find record where value in the 
f> ield is not in trim string.

> 
E> rror Type:

> ADODB.Recordset error '800a0bb9' 

> Arguments are of the wrong type, are out of acceptable range, or are in 
c> onflict with one another. 

> /FindChequeNumber.asp, line 26 

> 
a> dd here is part my code:
<> %
 >  Option Explicit
 >  Dim strConnect
%> >
<> !-- #include file="DataStore.asp" -->
<> !-- METADATA TYPE="typelib" 
 >              FILE="C:\Program Files\Common 
Files\System\ado\msado15.dll" -
-> >
<> HTML>
<> HEAD>
<> TITLE>Using the Find Method to find a Cheque Number</TITLE>
<> /HEAD>
<> BODY>

> <% 
 >  Dim strDirector, strCriteria
 >  strDirector = Request.Form("ChequeNumber")

>   Dim objRS
 >  Set objRS = Server.CreateObject("ADODB.Recordset")
 >  objRS.Open "BankStmt", strConnect, adOpenStatic, adLockReadOnly, 
a> dCmdTable

>   strCriteria = " '%"+ TRIM(objRS("RecNo"))+"%'=' " & strDirector & " ' "

>   objRS.Find strCriteria       ' show the first relevant record
 >  If objRS.EOF Then
 >    Response.Write "The database does not contain any Cheques by the 
C> heque " & _
 >                    strDirector
 >  Else
 >    Response.Write "<H2>Directed by " & strDirector & ":</H2>" & _
 >         "<B>Chuque No:</B> "   & objRS("RecNo")   & "<BR>" & _
 >         "<B>Cleared Date:</B> " & objRS("TrxDate") & "<BR>" & _
 >         "<B>Amount:</B> "   & objRS("Amount") & "<BR><BR><BR>"
 >  End If
 >  objRS.Close
 >  Set objRS = Nothing
%> >
<> /BODY>
<> /HTML>

> 
a> ny ideas?

> Thanks!

Message #3 by "Ken Schaefer" <ken@a...> on Wed, 10 Apr 2002 16:44:04 +1000

Instead of using the Recordset's .Find method, only return the resultset you
need in the first place:

<%
strSQL = _
    "SELECT field1, field2, field3 " & _
    "FROM BankStmt " & _
    "WHERE RecNo = '" & strDirector & "'"

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
%>

Also, as I have said several times recently on this list - always supply an
ADO connection *object* to the Recordset's .Open method, NOT a connection
*string*.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Azlin" <azlin08@t...>
Subject: [access_asp] String Criteria Can't Find Record


: Hello. Can anyone help me on how to code find record where value in the
: field is not in trim string.
:
:
: Error Type:
:
: ADODB.Recordset error '800a0bb9'
:
: Arguments are of the wrong type, are out of acceptable range, or are in
: conflict with one another.
:
: /FindChequeNumber.asp, line 26
:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Return to Index

P2P Archives