query with parameter in Access

[Rydel]

Dear gurus,

I looked through MSDN, but couldn't find the answer. So here is the problem. I use Access database and I have there the following query with a parameter:

PARAMETERS currCatID IEEEDouble;
SELECT email
FROM experts
WHERE ID in (SELECT expertID
FROM catExp
WHERE CategoryID=currCatID);

Question: How can I pass this parameter (currCatID) from my ASP page?
Let's say, myConn is the connection object and myRS is the recordset, so what would be the SQL string for myRS?
Thanks a lot in advance!!!

---

http://www.geocities.com/rydel23/

---

 

[htin11]

u execute the sql statement and then return it into a result set and then from the results set you can pass it into your asp pages...this is how it is done in every language...java, and etc... Emagine Solutions, Inc.

http://www.emagine-solutions.com

 

[htin11]

two ways to do it into asp

opening the recordset or just executing and return back the record set:

1) opening the recordset:

set cnn = Server.CreateObject("ADODB.Connection&quot
set rst = Server.CreateObject("ADODB.RecordSet&quot
cnn.Open "driver={Microsoft Access Driver(*.mdb)};;DBQ=d:/Projects/billing/billing.mdb;"

addemployee_sql = "SELECT * FROM Clients"
rst.Open addemployee_sql,cnn,3,3

test = rst("Yahoo&quot

rst.Close

2) executing
set cnn = Server.CreateObject("ADODB.Connection&quot
cnn.Open "driver={Microsoft Access Driver(*.mdb)};;DBQ=d:/Projects/billing/billing.mdb;"
rightid = "SELECT RightID,RightCode FROM RightCode"
set Results = cnn.Execute(rightid)
Response.Write(&quot;<SELECT class='box1' name='rightid'>&quot
do until Results.eof
if (Results(&quot;RightCode&quot = &quot;Regular&quot then
Response.Write(&quot;<OPTION value='&quot; & Results(&quot;RightID&quot & &quot;' SELECTED>&quot; & Results(&quot;RightCode&quot)
else
Response.Write(&quot;<OPTION value='&quot; & Results(&quot;RightID&quot & &quot;' >&quot; & Results(&quot;RightCode&quot)
end if
Results.movenext
loop
Response.Write(&quot;</SELECT>&quot


Hope this example helps...

Hui Emagine Solutions, Inc.

http://www.emagine-solutions.com

 

[Rydel]

Thanks for taking time to answer, htin11. Unfortunately, it seems you misunderstood the problem. Let me rephrase the question. I have myDB.mdb and in it I have a query name myQuery:

PARAMETERS currAge;
SELECT * FROM experts WHERE Age>currAge;

I have the following ASP file:

set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "driver={Microsoft Access Driver(*.mdb)};;DBQ=d:/Projects/myDB.mdb;"
strSQL = ???????? ' here I want to call the above query and supply the parameter currAge!
set Results = cnn.Execute(strSQL)

Question: what goes into strSQL=?????? to call the Access query and supply the parameter for it.

Thanks a lot in advance! ---

http://www.geocities.com/rydel23/

---

 

[htin11]

u don't put the parameters in it

strsql = &quot;SELECT email FROM experts WHERE ID in (SELECT expertID FROM catExp WHERE CategoryID=currCatID)&quot;

you have to learn sql..

for example this tells the database to get all email from experrt where id is in expert id from catexp where categoryid-currcatid...

you are doing a nested sql within a sql...

you also have to check what you have created in the database.

for example i want to list all informition in the table catexp...i would do select * from catexp...
-> Select (what? should be a database field) from (table? which table) Where (a argument)...there is a lot of other things you can do with sql...like inner joins and etc...

here is a nice tutorial:

http://w3.one.net/~jhoffman/sqltut.htm

hui
Emagine Solutions, Inc.

http://www.emagine-solutions.com

 

[htin11]

set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
cnn.Open &quot;driver={Microsoft Access Driver(*.mdb)};;DBQ=d:/Projects/myDB.mdb;&quot;
strSQL = &quot;SELECT * FROM experts WHERE Age>currAge;&quot;
set Results = cnn.Execute(strSQL)

do until Results.eof
Response.Write(Results(&quot;currAge&quot)
Results.movenext
loop

here is a example...this will print all the currage more than age... Emagine Solutions, Inc.

http://www.emagine-solutions.com

 

[Rydel]

Dear htin11, thanks once again, I have 3-yrs experience with SQL in MS SQL Server and 2-yrs experience with ASP, and I believe you are really not understanding the question. You see, in MS SQL Server there is no queries but stored procedures, and Access uses very weird dialect of SQL, so my question is - what's the syntax for calling Access queries with parameters? Is it clear now? ---

http://www.geocities.com/rydel23/

---

 

[Rydel]

I hope I am getting across this time . It's just I begin feeling quite stupid (maybe the fact that I haven't slept for 24 hours contributes to my drop in IQ) ---

http://www.geocities.com/rydel23/

---

 

[Christiana]

Try using something like this
mySQL=&quot;PARAMETERS currCatID IEEEDouble;
SELECT email
FROM experts
WHERE ID in (SELECT expertID
FROM catExp
WHERE CategoryID=&quot; & currCatID) & &quot;;&quot;
if you want the variable treated as a string you should use
CategoryID='&quot; & currCatID) & &quot;';&quot;
Hope this what you were asking for

 

[Rydel]

Hi! I got an answer to my question (on the Access forum). For those curoius about this problem, please follow this link:

http://support.microsoft.com/support/kb/articles/q200/1/90.asp

It's a Knowledge Base article about sending parameters to Access queries from ASP pages. Bingo!

---

http://www.geocities.com/rydel23/

---

 

[Deadline]

Hi THat link doesn't seem to work. Could you please post the exact URL ?

Thanks!

RR

 

[Rydel]

it worked before (i tested). in any case, there is another one:

http://support.microsoft.com/support/kb/articles/Q142/9/38.asp

i have to say that it's quite complicated (i fail to understand why it should be). so, in my case i just deleted the query from the database and put the SQL code from that query into strSQL variable and then ran myConnection.Execute(strSQL). ---

http://www.geocities.com/rydel23/

---

 

[Deadline]

Are you using Visual Interdev project method or are you using individual ASP files.??? Thanks!

RR

 

[jonscott8]

The above URL has a space following ASP, that's why it's failing. Try:

http://support.microsoft.com/support/kb/articles/q200/1/90.asp

Jon Hawkins

 

[Deadline]

Thank you very much. Can you tell me how to insert into an access table WITHOUT using a stored query ? That article talks about SELECTing.. I am using Interdev project instead of individual .asp files.


Thanks!

RR

 

[jonscott8]

SET oConn = CreateObject(&quot;ADODB.CONNECTION&quot
oConn.Open &quot;DSN=MyDSN&quot;
lRecordsAffected = 0
sSQL = &quot;INSERT INTO MyTable (MyTextField1, MyTextField2, MyNumField1) VALUES ('MyTextVal', '&quot; & MyTextVal2 & &quot;',&quot; & CStr(MyNumVal1) & &quot&quot;

oConn.Execute sSQL, lRecordsAffected
IF lRecordsAffected > 0
Response.Write &quot;Your Insert Has Completed&quot;
ENDIF Jon Hawkins