lirving@coffs.com.au
|
| Posted: 03/02/2002, 12:07 AM |
|
Using the tasks example i have a page which only displays finished tasks using the statement - status_id=3 - in the of the form properties, SQL, where section. This works fine.
I have another form which I want to display only unfinished tasks. I thought I could use - not status_id=3 - but it does not work. see the error below
not status_id=3
Error Type:
Microsoft JET Database Engine (0x80004005)
The specified field 'status_id' could refer to more than one table listed in the FROM clause of your SQL statement.
/etcstaff/Common.asp, line 33
these variations all give the error below
not tasks.status_id=3
not ((tasks.status_id)=3
not ((tasks!status_id)=3
(((tasks.status_id)<3 Or (tasks.status_id)>3))
Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/etcstaff/Common.asp, line 33
I am using codecharge 2.0.1 ASP no temples
Any idea what I need to do here?
Wayne
|
|
|
 |
tnorris
|
| Posted: 03/02/2002, 12:50 AM |
|
Use <> instead of not
|
|
|
|
lirving@coffs.com.au
|
| Posted: 03/02/2002, 4:33 AM |
|
still get the same errors using <>
I found that if I hid the field the form would work.
next found if i did not link the field to to status table ie. just showed the number. the form would work so it must be the call to the status table that confuses the database server.
So I made the field dislpsy from a list instaed of the table and it works.
I is not the best solution as it leaves one page that may need have its HTML updated, the rest use the table.
|
|
|
|
lirving@coffs.com.au
|
| Posted: 03/02/2002, 4:41 AM |
|
The site is at http://etc.coffs.com/shome/tmlistview.asp the page that works from the table is http://etc.coffs.com/shome/tmKnowledgeBase.asp
|
|
|
|
Ron Borkent
|
| Posted: 03/05/2002, 3:39 PM |
|
Have a look at the error that is returned. It states that the field status_id could refer to more then one table in your from section. The problem isn't the way you contructed the status_id part but the fact that the field status_id is present in more then one table in your from statement.
In this case you have to use aliasses or write the full statement.
For example:
select table_a.status_id, table_b.employee_id from table_a, table_b where table_a.status_id <> 3
The rule of thumb here is: if a field name exists in more then one table you plan to use in your select statement write the full fieldnames i.e. tablename.fieldname.
This way the database doesn't get confused when asked to do a where clause with a fieldname that exists in more then one table. The best way ofcourse is to try and device a system that avoids double fieldnames all together. For example:
tabel customer (status_id is used to give information on the actuality of the customer) fields:
cust_customer_id
cust_status_id
table orders(custommer is used to point to the custummuor id in table customer and status_id could say something about order status) Fields
ord_customer_id
ord_status_id
I think you'll get the point.
Ron
|
|
|
|
|
