ADO multiple field recordset search

This is a multi-part message in MIME format.

------=_NextPart_000_000C_01C53A0F.CEB7CE40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hey there,

I have a script that writes to an Access database with the following =
columns.

ComputerName UserID ProfileSize LastModDate

I would like to perform a multiple field search in ADO rather than a =
single column recordset search. Is there anyway to do that. Below is =
what I currently have for single column searches.

****************************
PCName =3D "mycomputer"
ProfileName =3D "dabungis"

strSearchCriteria =3D "ComputerName =3D '" & PCName & "'"
objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then 'if not found, then add new record
objRecordset.AddNew
End If
************************

I would like to perform a multiple field search such as below.

******************************
PCName =3D "mycomputer"
ProfileName =3D "dabungis"

strSearchCriteria =3D "ComputerName =3D '" & PCName & "' and UserID =3D =
'" & ProfileName & "'"
objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then 'if not found, then add new record
objRecordset.AddNew
End If
******************************

However, I always receive an error like below.
"ADODB.Recordset: Arguments are of the wrong type, are out of acceptable =
range, or are in conflict with one another."

What's wrong?

-Jeff
------=_NextPart_000_000C_01C53A0F.CEB7CE40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1492" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial>Hey there,</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>I have a script that writes to an Access =
database with the=20
following columns.</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>ComputerName    =
UserID   =20
ProfileSize    LastModDate</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>I would like to perform a multiple field search =
in ADO=20
rather than a single column recordset search.  Is there anyway to =
do=20
that.  Below is what I currently have for single column=20
searches.</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>****************************</FONT></DIV>
<DIV><FONT face=3DArial>PCName =3D "mycomputer"</FONT></DIV>
<DIV><FONT face=3DArial>ProfileName =3D "dabungis"</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>strSearchCriteria =3D "ComputerName =3D '" =
& PCName=20
& "'"<BR>  objRecordSet.Find strSearchCriteria</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>  If objRecordset.EOF Then   'if =
not found,=20
then add new record<BR>    objRecordset.AddNew<BR>  =
End=20
If</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial>************************</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>I would like to perform a multiple field search =
such as=20
below.</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>******************************</FONT></DIV>
<DIV><FONT face=3DArial>
<DIV><FONT face=3DArial>PCName =3D "mycomputer"</FONT></DIV>
<DIV><FONT face=3DArial>ProfileName =3D "dabungis"</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>strSearchCriteria =3D "ComputerName =3D '" =
& PCName=20
& "' and UserID =3D '" & ProfileName & "'"<BR>  =
objRecordSet.Find=20
strSearchCriteria</FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>  If objRecordset.EOF Then   'if =
not found,=20
then add new record<BR>    objRecordset.AddNew<BR>  =
End=20
If</FONT></DIV>
<DIV>******************************</DIV>
<DIV> </DIV>
<DIV>However, I always receive an error like below.</DIV>
<DIV>"ADODB.Recordset: Arguments are of the wrong type, are out of =
acceptable=20
range, or are in conflict with one another."</DIV>
<DIV> </DIV>
<DIV>What's wrong?</DIV></FONT></DIV>
<DIV><FONT face=3DArial></FONT> </DIV>
<DIV><FONT face=3DArial>-Jeff</FONT></DIV></DIV></BODY></HTML>

------=_NextPart_000_000C_01C53A0F.CEB7CE40--

Dave
Tue Apr 05 21:35:04 CDT 2005

All air code but try something like;

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Const adOpenDynamic = 2

strSQL = "SELECT ComputerName, UserID, ProfileName, LastModDate " _
& "FROM Table3 " _
& "WHERE (((ComputerName)='mycomputer') AND ((UserID)='dabungis'));
"
objRecordset.Open strSQL, objConnection, _
adOpenDynamic, adLockOptimistic
If objRecordset.EOF = True Then 'if not found, then add new record
objRecordset.AddNew
objRecordset("ComputerName") = "yada yada"
objRecordset("UserID") = "yada yada"
objRecordset("ProfileName") = "yada yada"
objRecordset("LastModDate") = "yada yada"
objRecordset.Update
End If
objRecordset.Close
objConnection.Close

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Jeff Giroux" wrote:
Hey there,

I have a script that writes to an Access database with the following
columns.

ComputerName UserID ProfileSize LastModDate

I would like to perform a multiple field search in ADO rather than a single
column recordset search. Is there anyway to do that. Below is what I
currently have for single column searches.

****************************
PCName = "mycomputer"
ProfileName = "dabungis"

strSearchCriteria = "ComputerName = '" & PCName & "'"
objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then 'if not found, then add new record
objRecordset.AddNew
End If
************************

I would like to perform a multiple field search such as below.

******************************
PCName = "mycomputer"
ProfileName = "dabungis"

strSearchCriteria = "ComputerName = '" & PCName & "' and UserID = '" &
ProfileName & "'"
objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then 'if not found, then add new record
objRecordset.AddNew
End If
******************************

However, I always receive an error like below.
"ADODB.Recordset: Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another."

What's wrong?

-Jeff

Jeff
Wed Apr 06 00:52:35 CDT 2005

Thanks,
But now I get the following error.

Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 1.

It's pointing to the below line(s).
strSQL = "SELECT ComputerName FROM Profiles WHERE ((ComputerName = '" &
PCName & "') AND (userID = '" & Profile & "'))"

objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic

Here's the whole script (important parts) just for reference. Is it because
I'm not using SQL? I'm only using an Access Database and below also shows
how I'm referencing it.

opening access mdb file
**********************
objConnection.Open "Provider=MSDASQL;" &_
"Driver={Microsoft Access Driver (*.mdb)};" &_
"Dbq=\\server\share\VBDatabase.mdb"

sub to search mdb file
**************************
Sub Database (PCName, Profile)
strSearchCriteria = "ComputerName = '" & PCName & "' and userID = '" &
Profile & "'"
' objRecordSet.Find strSearchCriteria

strSQL = "SELECT ComputerName FROM Profiles WHERE ((ComputerName = '" &
PCName & "') AND (userID = '" & Profile & "'))"

objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic

If objRecordset.EOF Then 'if computer not found, then add new record
objRecordset.AddNew
End If

'** These next lines actually update the values with new values

objRecordset("ComputerName") = PCName
objRecordSet("userID") = Profile
objRecordset("Profile Size") = ProfileSize
objRecordset("Last Modified Date") = FileDate
objRecordset.Update
End Sub
***************************************************

I tried it your way with all the different ((( and ))) and receive the
following error with the below code.

error
*******
Microsoft VBScript compilation error: Unterminated string constant

code
*******
same as above but the strSQL now shows below.

strSQL = "SELECT ComputerName FROM Profiles WHERE (((ComputerName) = '" &
PCName & "') AND ((userID) = '" & Profile & "'));

Jeff Giroux MSCIS Program UOP Online dabungis@email.uophx.edu
"Dave Patrick" <mail@Nospam.DSPatrick.com> wrote in message
news:uhRDzFlOFHA.3600@TK2MSFTNGP10.phx.gbl...
> All air code but try something like;
>
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adUseClient = 3
> Const adOpenDynamic = 2
>
>
> strSQL = "SELECT ComputerName, UserID, ProfileName, LastModDate " _
> & "FROM Table3 " _
> & "WHERE (((ComputerName)='mycomputer') AND
((UserID)='dabungis'));
> "
> objRecordset.Open strSQL, objConnection, _
> adOpenDynamic, adLockOptimistic
> If objRecordset.EOF = True Then 'if not found, then add new record
> objRecordset.AddNew
> objRecordset("ComputerName") = "yada yada"
> objRecordset("UserID") = "yada yada"
> objRecordset("ProfileName") = "yada yada"
> objRecordset("LastModDate") = "yada yada"
> objRecordset.Update
> End If
> objRecordset.Close
> objConnection.Close
>
>
> --
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
> "Jeff Giroux" wrote:
> Hey there,
>
> I have a script that writes to an Access database with the following
> columns.
>
> ComputerName UserID ProfileSize LastModDate
>
> I would like to perform a multiple field search in ADO rather than a
single
> column recordset search. Is there anyway to do that. Below is what I
> currently have for single column searches.
>
> ****************************
> PCName = "mycomputer"
> ProfileName = "dabungis"
>
> strSearchCriteria = "ComputerName = '" & PCName & "'"
> objRecordSet.Find strSearchCriteria
>
> If objRecordset.EOF Then 'if not found, then add new record
> objRecordset.AddNew
> End If
> ************************
>
> I would like to perform a multiple field search such as below.
>
> ******************************
> PCName = "mycomputer"
> ProfileName = "dabungis"
>
> strSearchCriteria = "ComputerName = '" & PCName & "' and UserID = '" &
> ProfileName & "'"
> objRecordSet.Find strSearchCriteria
>
> If objRecordset.EOF Then 'if not found, then add new record
> objRecordset.AddNew
> End If
> ******************************
>
> However, I always receive an error like below.
> "ADODB.Recordset: Arguments are of the wrong type, are out of acceptable
> range, or are in conflict with one another."
>
> What's wrong?
>
> -Jeff
>
>

Bob
Wed Apr 06 06:12:30 CDT 2005

Jeff Giroux wrote:
> Thanks,
> But now I get the following error.
>
> Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft
> Access Driver] Too few parameters. Expected 1.
>
> It's pointing to the below line(s).
> strSQL = "SELECT ComputerName FROM Profiles WHERE ((ComputerName =
> '" & PCName & "') AND (userID = '" & Profile & "'))"

I suspect "UserID" is a reserved keyword that must be surrounded by brackets
[] when used in queries executed via ADO.

Here is a safer and better way to pass values to a sql string:

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Best by far is to use a saved parameter query:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob
Wed Apr 06 06:19:36 CDT 2005

Jeff Giroux wrote:
> Hey there,
>
> I have a script that writes to an Access database with the following
> columns.
>
> ComputerName UserID ProfileSize LastModDate
>
> I would like to perform a multiple field search in ADO rather than a
> single column recordset search. Is there anyway to do that. Below
> is what I currently have for single column searches.
>
> ****************************
> PCName = "mycomputer"
> ProfileName = "dabungis"
>
> strSearchCriteria = "ComputerName = '" & PCName & "'"
> objRecordSet.Find strSearchCriteria

Use Filter instead of Find.

>
> If objRecordset.EOF Then 'if not found, then add new record
> objRecordset.AddNew
> End If
> ************************

Better yet, use an Insert statement

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Dave
Wed Apr 06 08:40:36 CDT 2005

Bob may be right about the reserved word or the data types are not all
string? The "Unterminated string constant" was because of line wrapping in
your newsreader. The last " was probably down one line.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Jeff Giroux" wrote:
| Thanks,
| But now I get the following error.
|
| Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft
| Access Driver] Too few parameters. Expected 1.
|
| It's pointing to the below line(s).
| strSQL = "SELECT ComputerName FROM Profiles WHERE ((ComputerName = '" &
| PCName & "') AND (userID = '" & Profile & "'))"
|
| objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic
|
| Here's the whole script (important parts) just for reference. Is it
because
| I'm not using SQL? I'm only using an Access Database and below also shows
| how I'm referencing it.
|
| opening access mdb file
| **********************
| objConnection.Open "Provider=MSDASQL;" &_
| "Driver={Microsoft Access Driver (*.mdb)};" &_
| "Dbq=\\server\share\VBDatabase.mdb"
|
| sub to search mdb file
| **************************
| Sub Database (PCName, Profile)
| strSearchCriteria = "ComputerName = '" & PCName & "' and userID = '" &
| Profile & "'"
| ' objRecordSet.Find strSearchCriteria
|
| strSQL = "SELECT ComputerName FROM Profiles WHERE ((ComputerName = '" &
| PCName & "') AND (userID = '" & Profile & "'))"
|
| objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic
|
| If objRecordset.EOF Then 'if computer not found, then add new record
| objRecordset.AddNew
| End If
|
| '** These next lines actually update the values with new values
|
| objRecordset("ComputerName") = PCName
| objRecordSet("userID") = Profile
| objRecordset("Profile Size") = ProfileSize
| objRecordset("Last Modified Date") = FileDate
| objRecordset.Update
| End Sub
| ***************************************************
|
| I tried it your way with all the different ((( and ))) and receive the
| following error with the below code.
|
| error
| *******
| Microsoft VBScript compilation error: Unterminated string constant
|
| code
| *******
| same as above but the strSQL now shows below.
|
| strSQL = "SELECT ComputerName FROM Profiles WHERE (((ComputerName) = '" &
| PCName & "') AND ((userID) = '" & Profile & "'));

ADO multiple field recordset search

Re: ADO multiple field recordset search by Dave

Re: ADO multiple field recordset search by Jeff

Re: ADO multiple field recordset search by Bob

Re: ADO multiple field recordset search by Bob

Re: ADO multiple field recordset search by Dave