2. ASPRunnerPro General questions
  3. Trouble getting SQL query to work
This topic is locked

Trouble getting SQL query to work

 		11/3/2006 12:51:25
ASPRunnerPro General questions
E
 EngAdven author

I'm struggling to get any SQL querys to work and could do with some help.

I'm new to ASP Runner so I think I'm probably missing something. However it does seem to build OK but there is always a page error.

Here's my query
select a.ContactID,

a.ProductID,

b.ProductName

From CONTACT_PRODUCT a, PRODUCTS b

where a.ProductID=b.ProductID
I'm assuming if I put this against one table it will display information from both tables listed in the one table's list.

Any help would be appreciated.

Thanks
C
 		chris 11/3/2006

I'm struggling to get any SQL querys to work and could do with some help.

I'm new to ASP Runner so I think I'm probably missing something. However it does seem to build OK but there is always a page error.

Here's my query
select a.ContactID,

a.ProductID,

b.ProductName

From CONTACT_PRODUCT a, PRODUCTS b

where a.ProductID=b.ProductID
I'm assuming if I put this against one table it will display information from both tables listed in the one table's list.

Any help would be appreciated.

Thanks



Do a search on using 'inner join' may resolve your issue.

More info and examples:

http://www.xlinesoft.com/asprunner/docs/us...sql_queries.htm

Hope that helps,

Chris.
Sergey Kornilov admin 11/4/2006

If the same field name appears in bth tables use alias:

select a.ContactID,

a.ProductIDas aProductID,

b.ProductName

From CONTACT_PRODUCT a, PRODUCTS b

where a.ProductID=b.ProductID
E
 		EngAdven author 11/5/2006

Thanks for that but I've tried a few combinations with aliases and inner joins with no success.

I actually have three tables

  1. All the companies
  2. ProductIDs for each companyID
  3. the name of each product against its ProductID

    I've set 1 as the master for 2 and 2 as the master for 3
    The error is (slightly different SQL to above)

    (missing operator) in query expression '(a.ProductID=b.ProductID and a.contactID=c.contactID) and (=9257)'.

    which looks at though it's not handling the companyID feed properly from the first company list page. companyID should equal 9257.

    If I remove the relationship between the tables 1 and 2 the page displays but with a recordset error.
    I've produced similar page report using DB to HTML express in the past but using the same SQL with aliases does not work in ASPRunner

    I hope this extra information can help identify what i should be doing.
Sergey Kornilov admin 11/6/2006

Gary,
without seeing actual tables structure I cannot tell you what exactly is wrong.
I would recommend to build a query in MS Access that pulls data from all three tables and use this query as a datasource in ASPRunnerPro.
E
 		EngAdven author 11/7/2006

Still no joy I'm afraid.

I always get the error

(missing operator) in query expression '(=9257)'

where 9257 is the masterkey passed from the previous page.

I think I'll settle for something more simple for the time being.

Thanks for you help though.
J
 		Jane 11/7/2006

Gary,
please zip and send generated ASP files to [email=support@xlinesoft.com]support@xlinesoft.com[/email] along with project file and your sample database for investigation.

I'll find what's wrong with your project running it on my test box.