473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

using ASP and parametized query

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_STRING

set conn = CreateObject("ADODB.Connection")
conn.Mode=adModeRead
mdb = Server.MapPath("songs.mdb")
mdw = Server.MapPath("system.mdw")

MM_GenKAccess_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & mdb & ";" & _
"Jet OLEDB:Database Password=xyz;" & _
"Jet OLEDB:System database=" & mdw

conn.open MM_GenKAccess_STRING
------------------------------
<% OPTION EXPLICIT %>
<%
Dim T
T = "%"
If (Request("title") <> "") Then
T = Request("title")
End If
%>
<%
Dim A
A = "%"
If (Request("artist") <> "") Then
A = Request("artist")
End If
%>
<%
Dim C
C = "%"
If (Request("category") <> "") Then
C = Request("category")
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("singleartist") <> "") Then
SA = Request("singleartist")
End If
%>
<%
Dim rsResults
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '" & TY &
"', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>
Jul 19 '05 #1
9 11732
shank wrote:
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
& TY & "', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>


Do this instead:

dim cn
Set cn=server.createobject("adodb.connection")
cn.open MM_GenKAccess_STRING
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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"
Jul 19 '05 #2
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uf**************@TK2MSFTNGP10.phx.gbl...
shank wrote:
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
& TY & "', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>
Do this instead:

dim cn
Set cn=server.createobject("adodb.connection")
cn.open MM_GenKAccess_STRING
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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?

----------------------------------------------
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_FindSongs 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.CreateObject("ADODB.connection")
cn.open MM_GenKAccess_STRING
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
%>
Jul 19 '05 #3
shank wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uf**************@TK2MSFTNGP10.phx.gbl...
shank wrote:
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
& TY & "', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>
Do this instead:

dim cn
Set cn=server.createobject("adodb.connection")
cn.open MM_GenKAccess_STRING
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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?

----------------------------------------------
I'm on a local station


I'm not sure what you mean by "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: "
It's not a string, it's a call to a method.

cn.qry_FindSongs 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?
Correct. it always works for me.
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.


Does the error message point to this line?

Check to verify that your connection is open

<%
dim cn, rsResults
Set cn=Server.CreateObject("ADODB.connection")
cn.open MM_GenKAccess_STRING
if cn.State = 1 then
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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_STRING = "Provider=Microsoft.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"
Jul 19 '05 #4

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ox****************@TK2MSFTNGP11.phx.gbl...
shank wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uf**************@TK2MSFTNGP10.phx.gbl...
shank wrote:
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
& TY & "', '" & M & "', '" & SA & "'"
rsResults.Open rsResults, 0, 4
%>

Do this instead:

dim cn
Set cn=server.createobject("adodb.connection")
cn.open MM_GenKAccess_STRING
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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? ----------------------------------------------
I'm on a local station


I'm not sure what you mean by "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: "


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

cn.qry_FindSongs 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?


Correct. it always works for me.
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.


Does the error message point to this line?

Check to verify that your connection is open

<%
dim cn, rsResults
Set cn=Server.CreateObject("ADODB.connection")
cn.open MM_GenKAccess_STRING
if cn.State = 1 then
Set rsResults = Server.CreateObject("ADODB.Recordset")
cn.qry_FindSongs 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_STRING = "Provider=Microsoft.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

---------------------------------------------------
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.CreateObject("ADODB.Recordset")
conn.qry_SelectCat, 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.CreateObject("ADODB.Recordset")
conn.qry_SelectCategories C, rsResults
Response.Write "Connection is open<BR>"
else
Response.Write "Connection is not open<BR>"
end if
%>
Jul 19 '05 #5
shank wrote:
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.CreateObject("ADODB.Recordset")
conn.qry_SelectCategories C, rsResults
Response.Write "Connection is open<BR>"
else
Response.Write "Connection is not open<BR>"
end if
%>

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.
Jul 19 '05 #6

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uw*************@TK2MSFTNGP12.phx.gbl...
shank wrote:
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.CreateObject("ADODB.Recordset")
conn.qry_SelectCategories C, rsResults
Response.Write "Connection is open<BR>"
else
Response.Write "Connection is not open<BR>"
end if
%>

Can you show the SQL for the query that accepts the parameter? I suspect
that the query itself is the problem.

-------------------
I cut-n-pasted these from SQL view in Access.
This query requests 1 variable...
SELECT DISTINCT GenKStock.Category
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber
WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
ORDER BY GenKStock.Category;

This query has no variables...
SELECT DISTINCT GenKStock.Category
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber
ORDER BY GenKStock.Category;

This is the original query that I need to work...
SELECT GenKStock.OrderNo, GenKTitles.Title, GenKTitles.Artist,
GenKStock.Category, GenKStock.Type, GenKStock.Manuf, GenKStock.SingleArtist,
GenKTitles.mp3Files
FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber
WHERE (((GenKTitles.Title) Like "*" & [T] & "*") AND ((GenKTitles.Artist)
Like "*" & [A] & "*") AND ((GenKStock.Category) Like "*" & [C] & "*") AND
((GenKStock.Type) Like "*" & [TY] & "*") AND ((GenKStock.Manuf) Like "*" &
[M] & "*") AND ((GenKStock.SingleArtist) Like "*" & [SA] & "*"));
Jul 19 '05 #7
shank wrote:
WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
ORDER BY GenKStock.Category;


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.
Jul 19 '05 #8

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
shank wrote:
WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
ORDER BY GenKStock.Category;


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.


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!!!!
Jul 19 '05 #9
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 ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\documents and settings\tom\desktop\fundhomev7ak1.mdb;Jet OLEDB:Database Password=xxxxxx"

Dim Connection As adodb.Connection
Set Connection = New adodb.Connection
Connection.ConnectionString = ConnectionString
Connection.Open

Debug.Print Connection.State = ObjectStateEnum.adStateOpen

'Define Recordset and access query
'sql = "SELECT tblMonVal.MVID, tblMonVal.MVDate, tblMonVal.MVUSDVal, tblMonVal.MVAssetID, tblMonVal.MVAssetPX FROM tblmonval WHERE (((tblMonVal.MVDate) is not null and (tblmonval.mvdate) Between '" & startdate & "' And '" & enddate & "')) ORDER BY tblmonval.mvassetid;"

Debug.Print sql

Set rstmonval = New adodb.Recordset
Connection.qryvalsbymonth startdate, enddate, rstmonval
If (Connection.State = ObjectStateEnum.adStateOpen) Then
Connection.Close
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
May 24 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: | last post by:
I am trying to improve the robustness and elegance of my parametized sql statements in ASP 3.0 as they get passed to the sql server SP. Could anyone tell me if there are weaknessess in the way I...
3
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am...
1
by: GrantS | last post by:
I am unable to get the connection to work with using the app.config file. the connection works when I use 'in line' connection as below:...
11
by: Dacuna | last post by:
Is it possible to use a recursive function to loop through a recordset faster? I have a table that I need to edit its contents after doing some calculation. The table has one field has an RawData...
18
by: Jen | last post by:
I'm using Microsoft's own VB.NET FTP Example: http://support.microsoft.com/default.aspx?scid=kb;en-us;832679 I can get the program to create directories, change directories, etc., but I can't...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
6
by: 2005 | last post by:
Hi I have initialized a member as below: class CNode { public: CNode() : m_pNext(0), m_ticketNum(0) {} ---- private: int m_ticketNum; // ticket number of car CarNode *m_pNext;
21
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Uploading files from a local computer to a remote web server has many useful purposes, the most...
6
by: raylopez99 | last post by:
This thread is about how variables or parameters (including objects) are passed between forms (namely, using parameterized constructors, e.g., to pass an int between forms (e.g., a calling form and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.