Returning recodset from multiple tables

[marko_one]

Hi All,

This is more a SQL question than an ASP question, but it is part of my ASP project any so here goes :

In my MS Access Database I have 2 tables which I need to use to get a result back from a query.

these two tables are fromed as :

Table 'StoreList' has primary key 'pkStore', it has a Field Name of StoreName (amongst other fields)

Table 'techEnquiry' has foreign Key pkStore. when a page is updated/added then I add pkStore to the techEnquiry table and If I want to view the list of storenames through the techEnquiry table I just pass in the pkStore from the techEnquiry into the StoreList and retrieve the storename from the StoreList to populate the page - that's as complex as it gets, until now.

What I need to do now is search the database for storenames from a given input string, but all I have in the techEnquiry table is pkStore and no storeName field. What I need to do is find all items in the database with say '%s%' and return the recordset back with a list of all pkStore's.

I was thinking of something like this :

SELECT * FROM StoreList INNER JOIN tescoTech ON StoreList.pkStore = techEnquiry.pkStore where StoreName LIKE '%s%';

But this returns no results whereas I know there are stores with 's' in thier names?

Dont' know if I explained that so well, but If anybody has any ideas I would appreciate it

Thanx

Marko.

[rdoekes]

SELECT * FROM StoreList
INNER JOIN tescoTech ON
StoreList.pkStore = techEnquiry.pkStore
where StoreName LIKE '%s%';

SELECT * FROM StoreList
INNER JOIN techEnquiry ON
StoreList.pkStore = techEnquiry.pkStore
where StoreName LIKE '%s%';

-Rogier Doekes

[marko_one]

Thanks for the reply Rogier,

you spotted my 'deliberate' mistake I see, I have in fact got

SELECT * FROM StoreList
INNER JOIN techEnquiry ON
StoreList.pkStore = techEnquiry.pkStore
where StoreName LIKE '*s*';

It was a typing error on my part. It still doesn't work with the above query (you need to use '*' in jet 4 instead of '%').

The strange thing is that when I try this in the database itself using sql view of the query it works and returns back the expected values, but when run through ASP code I get no results back.

Is there some sort of quirk when using Jet 4 wher I have to alter the structure of the query when using Joiins?

[rdoekes]

could you show your asp code where you connect to the database, execute the query and close the connection.

Maybe the problem is there.

[marko_one]

Ok I have changed direction slightly in that I tried it using a stored query, but still got no records returned, Here's the Code :

dbDriver = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
dbPath = server.mappath("db") & "\"
strConn = dbDriver & dbPath & "StoreTech.mdb"
Set DbConn = Server.CreateObject("ADODB.Connection")
DbConn.open strConn

Set rs = Server.CreateObject("ADODB.RECORDSET")
sql = "uspStoreSearch '*s*'"

rs.Open sql, DbConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Dim Recrd

Do While Not rs.EOF
Recrd = rs("StoreName")
Response.Write("StoreName = " & Recrd & "<br>")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
DbConn.Close
Set DbConn = Nothing


And Here's The Access Query :

PARAMETERS Item Text ( 255 );
SELECT *
FROM StoreList INNER JOIN TechEnquiry ON StoreList.pkStore = TechEnquiry.pkStore
WHERE (((StoreList.StoreName) Like [Item]));





When I run this query in Access, and put *s* in the prompt box I get the expected results, but whn ran from the ASP page, I get no results.

Thanks for reading,

Marko.

[rdoekes]

Well Marko,

This is strange. I ran your code on my server and records were returned without a problem.

What version of MDAC do you use? That's the only thing I could think of.

-Rogier Doekes

[marko_one]

I have no Idea what is going on here, I was convinced my coding was right and you have just confirmed this.

I am using MDAC version 2.7 RTM

I have a windows XP machine with the .NET framework installed because I am tinkering around with some ASP.NET programming - I figured this possibly could cause me some problem (although I doubt it) - I also have windows 2000 installed on the same machine (Dual Boot) and so I rebooted into win 2k which has MDAC 2.5 installed and I got the same problem there - no records returned.

I am really stuck here and I really need to complete this task. I may have to use another approach??

[Ribeyed]

hi,

following this post, can i make a suggestion for the sql statement? can you try this:


SELECT * FROM StoreList
WHERE StoreList.pkStore = techEnquiry.pkStore AND
StoreName LIKE '*s*';



Let me know the results

[rdoekes]

Could the dual boot throw you a curve ball, since you have two MDAC version running? What if you install MDAC 2.7 on your WIN 2K partition, which is what I am currently running together with the .net framework on my WIN 2K server.

-Rogier

[marko_one]

Ribeyed,
I tried your suggestion, but got the following error :

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

It is expecting to be given a value for StoreList.pkStore and StoreName, as when I run this Query in MS Access, that is what it asks for?

I can understand the logic in the Query and by rights it should work, but it doen't seem to?

[marko_one]

Rogier,

I have now tried it out on several machines but get no records back.

You say that you tried it and it worked for you - any chance you could send me the code/db?

I have now installed MDAC 2.7 in win 2k, but no joy I'm afraid.
This is getting to be a real pain in the backside. Why can't everybody use SQL server, life would be so much simpler!

[Ribeyed]

Hi,


SELECT * FROM StoreList
WHERE StoreList.pkStore = techEnquiry.pkStore AND
StoreName LIKE '%s%'"


This should work

[Ribeyed]

HI,
sorry never answered this:


It is expecting to be given a value for StoreList.pkStore and StoreName, as when I run this Query in MS Access, that is what it asks for?


Not for StoreList.pkStore but yes for storename which means the error received can only be for storename because that is the only parameter you are inputing.

[marko_one]

Hi Ribeyed,
Even with the altered code (% and no semicolon) this still gives me the same error??

[Ribeyed]

Hi,
is it ASP classic or ASP.NET you are using?

[marko_one]

It's Classic ASP

[rdoekes]

Marko,

Attached my code. The asp file comes from this directory \inetpub\wwwroot\ and the db from \inetpub\wwwroot\db\

This works fine for me.

Hope this helps.

-Rogier