using ASP and parametized query

[shank]

Per a previous suggestion, I'm trying to use a parametized query in Access
2002. The query functions properly in Access. Now I'm trying to call it from
ASP. I'm using code I found at
http://www.xefteri.com/articles/apr302002/default.aspx and trying to adjust
for my needs. I'm getting this error. The query is there and functioning. It
appears that I'm not connecting. Can I get some more insight?
thanks!
------------------------------
Microsoft VBScript runtime (0x800A01A8)
Object required: 'qry_FindSongs 'White'

------------------------------
My connection string is
------------------------------
Dim conn, mdb, mdw, MM_GenKAccess_S TRING

set conn = CreateObject("A DODB.Connection ")
conn.Mode=adMod eRead
mdb = Server.MapPath( "songs.mdb" )
mdw = Server.MapPath( "system.mdw ")

MM_GenKAccess_S TRING = "Provider=Micro soft.Jet.OLEDB. 4.0; " & _
"Data Source=" & mdb & ";" & _
"Jet OLEDB:Database Password=xyz;" & _
"Jet OLEDB:System database=" & mdw

conn.open MM_GenKAccess_S TRING
------------------------------
<% OPTION EXPLICIT %>
<%
Dim T
T = "%"
If (Request("title ") <> "") Then
T = Request("title" )
End If
%>
<%
Dim A
A = "%"
If (Request("artis t") <> "") Then
A = Request("artist ")
End If
%>
<%
Dim C
C = "%"
If (Request("categ ory") <> "") Then
C = Request("catego ry")
End If
%>
<%
Dim TY
TY = "%"
If (Request("type" ) <> "") Then
TY = Request("type")
End If
%>
<%
Dim M
M = "%"
If (Request("manuf ") <> "") Then
M = Request("manuf" )
End If
%>
<%
Dim SA
SA = "%"
If (Request("singl eartist") <> "") Then
SA = Request("single artist")
End If
%>
<%
Dim rsResults
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
rsResults.Activ eConnection = MM_GenKAccess_S TRING
rsResults = "qry_FindSo ngs '" & T & "', '" & A & "', '" & C & "', '" & TY &
"', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>

[Bob Barrows [MVP]]

Re: using ASP and parametized query

shank wrote:[color=blue]
> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> rsResults.Activ eConnection = MM_GenKAccess_S TRING
> rsResults = "qry_FindSo ngs '" & T & "', '" & A & "', '" & C & "', '"
> & TY & "', '" & M & "', '" & SA & "'"
> rsResults.Open rsResults, 0, 4
> %>[/color]

Do this instead:

dim cn
Set cn=server.creat eobject("adodb. connection")
cn.open MM_GenKAccess_S TRING
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
cn.qry_FindSong s T,A,C,TY,M,SA, rsResults

See? No delimiters to worry about.

Bob Barrows

PS. You don't seem to have provided a user name for your connection. Are you
sure you have workgroup security on your database?

--
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"

[shank]

Re: using ASP and parametized query

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:ufvm37mfEH A.2908@TK2MSFTN GP10.phx.gbl...[color=blue]
> shank wrote:[color=green]
> > Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> > rsResults.Activ eConnection = MM_GenKAccess_S TRING
> > rsResults = "qry_FindSo ngs '" & T & "', '" & A & "', '" & C & "', '"
> > & TY & "', '" & M & "', '" & SA & "'"
> > rsResults.Open rsResults, 0, 4
> > %>[/color]
>
> Do this instead:
>
> dim cn
> Set cn=server.creat eobject("adodb. connection")
> cn.open MM_GenKAccess_S TRING
> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
>
> See? No delimiters to worry about.
>
> Bob Barrows
>
> PS. You don't seem to have provided a user name for your connection. Are[/color]
you[color=blue]
> sure you have workgroup security on your database?[/color]
----------------------------------------------
I'm on a local station and the connection string works for all other pages.
I connected with your string, but got no results, where I should be. To be
clear, this string: "cn.qry_FindSon gs T,A,C,TY,M,SA, rsResults" passes the
values of the variables T,A,C,TY,M,SA into the query qry_FindSongs and
returns a recordset rsResults... correct? I'm not getting a return on
rsResults.

I get this error:
Response object, ASP 0185 (0x8002000E)
A default property was not found for the object.

The variables are being filled....
T: water
A: %
C: %
TY: %
M: %
SA: %

<%
dim cn, rsResults
Set cn=Server.Creat eObject("ADODB. connection")
cn.open MM_GenKAccess_S TRING
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
%>

[Bob Barrows [MVP]]

Re: using ASP and parametized query

shank wrote:[color=blue]
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:ufvm37mfEH A.2908@TK2MSFTN GP10.phx.gbl...[color=green]
>> shank wrote:[color=darkred]
>>> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
>>> rsResults.Activ eConnection = MM_GenKAccess_S TRING
>>> rsResults = "qry_FindSo ngs '" & T & "', '" & A & "', '" & C & "', '"
>>> & TY & "', '" & M & "', '" & SA & "'"
>>> rsResults.Open rsResults, 0, 4
>>> %>[/color]
>>
>> Do this instead:
>>
>> dim cn
>> Set cn=server.creat eobject("adodb. connection")
>> cn.open MM_GenKAccess_S TRING
>> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
>> cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
>>
>> See? No delimiters to worry about.
>>
>> Bob Barrows
>>
>> PS. You don't seem to have provided a user name for your connection.
>> Are you sure you have workgroup security on your database?[/color]
> ----------------------------------------------
> I'm on a local station[/color]

I'm not sure what you mean by "local station"
[color=blue]
> and the connection string works for all other
> pages. I connected with your string, but got no results, where I
> should be. To be clear, this string: "[/color]

It's not a string, it's a call to a method.

cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
[color=blue]
> " passes the values of the variables T,A,C,TY,M,SA into the
> query qry_FindSongs and returns a recordset rsResults... correct?[/color]

Correct. it always works for me.
[color=blue]
> I'm not getting a return on rsResults.
>
> I get this error:
> Response object, ASP 0185 (0x8002000E)
> A default property was not found for the object.[/color]

Does the error message point to this line?

Check to verify that your connection is open

<%
dim cn, rsResults
Set cn=Server.Creat eObject("ADODB. connection")
cn.open MM_GenKAccess_S TRING
if cn.State = 1 then
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
else
Response.Write "Connection is not open<BR>"
end if
%>

Your connection string still looks wierd to me. When you open the database
in Access, do you have to supply both a user name and a password? If not,
you are not using workgroup security and you don't need that business about
a system database in your connection string. If you don't have to enter a
password, then your database is not password-protected and the only thing
needed in your connection string is:

MM_GenKAccess_S TRING = "Provider=Micro soft.Jet.OLEDB. 4.0; " & _
"Data Source=" & mdb

If none of these suggestions help, I will need to look at it first-hand.
Would it be possible to send your database to my email address? Or, you can
extract the relevant objects into another database and remove any sensitive
data if that allows you to send it.

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"

[shank]

Re: using ASP and parametized query


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:Oxks7%23nf EHA.3320@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> shank wrote:[color=green]
> > "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> > news:ufvm37mfEH A.2908@TK2MSFTN GP10.phx.gbl...[color=darkred]
> >> shank wrote:
> >>> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> >>> rsResults.Activ eConnection = MM_GenKAccess_S TRING
> >>> rsResults = "qry_FindSo ngs '" & T & "', '" & A & "', '" & C & "', '"
> >>> & TY & "', '" & M & "', '" & SA & "'"
> >>> rsResults.Open rsResults, 0, 4
> >>> %>
> >>
> >> Do this instead:
> >>
> >> dim cn
> >> Set cn=server.creat eobject("adodb. connection")
> >> cn.open MM_GenKAccess_S TRING
> >> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> >> cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
> >>
> >> See? No delimiters to worry about.
> >>
> >> Bob Barrows
> >>
> >> PS. You don't seem to have provided a user name for your connection.
> >> Are you sure you have workgroup security on your database?[/color]
> > ----------------------------------------------
> > I'm on a local station[/color]
>
> I'm not sure what you mean by "local station"
>[color=green]
> > and the connection string works for all other
> > pages. I connected with your string, but got no results, where I
> > should be. To be clear, this string: "[/color]
>
> It's not a string, it's a call to a method.
>
> cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
>[color=green]
> > " passes the values of the variables T,A,C,TY,M,SA into the
> > query qry_FindSongs and returns a recordset rsResults... correct?[/color]
>
> Correct. it always works for me.
>[color=green]
> > I'm not getting a return on rsResults.
> >
> > I get this error:
> > Response object, ASP 0185 (0x8002000E)
> > A default property was not found for the object.[/color]
>
> Does the error message point to this line?
>
> Check to verify that your connection is open
>
> <%
> dim cn, rsResults
> Set cn=Server.Creat eObject("ADODB. connection")
> cn.open MM_GenKAccess_S TRING
> if cn.State = 1 then
> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> cn.qry_FindSong s T,A,C,TY,M,SA, rsResults
> else
> Response.Write "Connection is not open<BR>"
> end if
> %>
>
> Your connection string still looks wierd to me. When you open the database
> in Access, do you have to supply both a user name and a password? If not,
> you are not using workgroup security and you don't need that business[/color]
about[color=blue]
> a system database in your connection string. If you don't have to enter a
> password, then your database is not password-protected and the only thing
> needed in your connection string is:
>
> MM_GenKAccess_S TRING = "Provider=Micro soft.Jet.OLEDB. 4.0; " & _
> "Data Source=" & mdb
>
> If none of these suggestions help, I will need to look at it first-hand.
> Would it be possible to send your database to my email address? Or, you[/color]
can[color=blue]
> extract the relevant objects into another database and remove any[/color]
sensitive[color=blue]
> data if that allows you to send it.
>
> Bob Barrows[/color]
---------------------------------------------------
I have narrowed the problem down. The connection is open. I made this small
test code and it works. I created a query in the Access DB that does not
require a variable. And it works....

<%
'this code works and there is no variables
if conn.State = 1 then
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
conn.qry_Select Cat, rsResults
Response.Write "Connection is open<BR>"
else
Response.Write "Connection is not open<BR>"
end if
%>

.... Then I created a query that requires 1 simple variable and I get this
error...
"Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record" which suggests to me that the
variables are not getting passed to the query. How can I troubleshoot
further? Thanks!

<%
'I introduce 1 variable and it appears the variable is not getting passed to
the query
C = "fav"
if conn.State = 1 then
Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
conn.qry_Select Categories C, rsResults
Response.Write "Connection is open<BR>"
else
Response.Write "Connection is not open<BR>"
end if
%>

[Bob Barrows [MVP]]

Re: using ASP and parametized query

shank wrote:
[color=blue]
> this error...
> "Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record" which suggests to me
> that the variables are not getting passed to the query. How can I
> troubleshoot further? Thanks!
>
> <%
> 'I introduce 1 variable and it appears the variable is not getting
> passed to the query
> C = "fav"
> if conn.State = 1 then
> Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> conn.qry_Select Categories C, rsResults
> Response.Write "Connection is open<BR>"
> else
> Response.Write "Connection is not open<BR>"
> end if
> %>[/color]
Can you show the SQL for the query that accepts the parameter? I suspect
that the query itself is the problem.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[shank]

Re: using ASP and parametized query


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:uwxyFmxfEH A.140@TK2MSFTNG P12.phx.gbl...[color=blue]
> shank wrote:
>[color=green]
> > this error...
> > "Either BOF or EOF is True, or the current record has been deleted.
> > Requested operation requires a current record" which suggests to me
> > that the variables are not getting passed to the query. How can I
> > troubleshoot further? Thanks!
> >
> > <%
> > 'I introduce 1 variable and it appears the variable is not getting
> > passed to the query
> > C = "fav"
> > if conn.State = 1 then
> > Set rsResults = Server.CreateOb ject("ADODB.Rec ordset")
> > conn.qry_Select Categories C, rsResults
> > Response.Write "Connection is open<BR>"
> > else
> > Response.Write "Connection is not open<BR>"
> > end if
> > %>[/color]
> Can you show the SQL for the query that accepts the parameter? I suspect
> that the query itself is the problem.[/color]
-------------------
I cut-n-pasted these from SQL view in Access.
This query requests 1 variable...
SELECT DISTINCT GenKStock.Categ ory
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.Order No =
GenKTitles.Item Number
WHERE (((GenKStock.Ca tegory) Like "*" & [C] & "*"))
ORDER BY GenKStock.Categ ory;

This query has no variables...
SELECT DISTINCT GenKStock.Categ ory
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.Order No =
GenKTitles.Item Number
ORDER BY GenKStock.Categ ory;

This is the original query that I need to work...
SELECT GenKStock.Order No, GenKTitles.Titl e, GenKTitles.Arti st,
GenKStock.Categ ory, GenKStock.Type, GenKStock.Manuf , GenKStock.Singl eArtist,
GenKTitles.mp3F iles
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.Order No =
GenKTitles.Item Number
WHERE (((GenKTitles.T itle) Like "*" & [T] & "*") AND ((GenKTitles.Ar tist)
Like "*" & [A] & "*") AND ((GenKStock.Cat egory) Like "*" & [C] & "*") AND
((GenKStock.Typ e) Like "*" & [TY] & "*") AND ((GenKStock.Man uf) Like "*" &
[M] & "*") AND ((GenKStock.Sin gleArtist) Like "*" & [SA] & "*"));

[Bob Barrows [MVP]]

Re: using ASP and parametized query

shank wrote:[color=blue]
> WHERE (((GenKStock.Ca tegory) Like "*" & [C] & "*"))
> ORDER BY GenKStock.Categ ory;[/color]

You have to change the * to % in order to call this query from ADO. It
didn't make sense to me the first time I encountered this issue, either.

Bob barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[shank]

Re: using ASP and parametized query


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:%23eQ1vxyf EHA.2000@tk2msf tngp13.phx.gbl. ..[color=blue]
> shank wrote:[color=green]
> > WHERE (((GenKStock.Ca tegory) Like "*" & [C] & "*"))
> > ORDER BY GenKStock.Categ ory;[/color]
>
> You have to change the * to % in order to call this query from ADO. It
> didn't make sense to me the first time I encountered this issue, either.
>[/color]

Shazam! Very strange. The ASP gets a recordset, but when using the Access
interface, '%' does not yield any results. Oh well I guess. Thanks very very
much!!!!

[tomacheski]

microsoft jet engine cannot find the input table or query

Hi - I have an extremely similar problem but am using excel vba to try and run a parameter query based on start and end dates, the code I am using looks like this:
Dim rstmonval As adodb.Recordset
Dim wssheet As Worksheet
Dim startdate As Date
Dim enddate As Date
Dim sql As String

startdate = Me.stdate.Text
enddate = Me.endate.Text


'Open Connection to Database
Const ConnectionStrin g As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\docum ents and settings\tom\de sktop\fundhomev 7ak1.mdb;Jet OLEDB:Database Password=xxxxxx "

Dim Connection As adodb.Connectio n
Set Connection = New adodb.Connectio n
Connection.Conn ectionString = ConnectionStrin g
Connection.Open

Debug.Print Connection.Stat e = ObjectStateEnum .adStateOpen

'Define Recordset and access query
'sql = "SELECT tblMonVal.MVID, tblMonVal.MVDat e, tblMonVal.MVUSD Val, tblMonVal.MVAss etID, tblMonVal.MVAss etPX FROM tblmonval WHERE (((tblMonVal.MV Date) is not null and (tblmonval.mvda te) Between '" & startdate & "' And '" & enddate & "')) ORDER BY tblmonval.mvass etid;"

Debug.Print sql

Set rstmonval = New adodb.Recordset
Connection.qryv alsbymonth startdate, enddate, rstmonval
If (Connection.Sta te = ObjectStateEnum .adStateOpen) Then
Connection.Clos e
End If

However - the Jet engine tells me it cannot find this query, despite the fact that it exits....

can anyone give me a clue as to what I am doing wrong?

thanks

Tom