join recordset with null field causing errors

[aonefun]

The initial value of form fields on this page are populated by the following recordset containing a join:

SELECT *
FROM customers_cus, orders_ord
WHERE orders_ord.customerid_ord=customers_cus.customerid_cus AND company_cus='" & Request.Form("companymenu") &"'

Whenever the database field orders_ord.customerid_ord is not null, the form fields on the page preview fine. However, if orders_ord.customerid_ord is null, I receive the following error message:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

How do I make it that orders_ord.customerid_ord is able to be null?

[Newsgroup_User · Accepted solution]

First, abandon your outdated join syntax. Next, use an outer join instead.
If you join two tables on an inner join, then no records with a NULL in
either join column will be returned.

Also, don't use SELECT *. And you're vulnerable to SQL injection.

SELECT <columns>
FROM customers_cus
LEFT OUTER JOIN orders_ord ON
orders_ord.customerid_ord=customers_cus.customerid_cus
WHERE company_cus='" & Replace(Request.Form("companymenu"),"'","''") &"'

The replace above doesn't offer complete protection, but it is at a minimum
what you should use to protect yourself.


"aonefun" <webforumsuser@macromedia.com> wrote in message
news:enjb11$3mt$1@forums.macromedia.com...
> The initial value of form fields on this page are populated by the
> following
> recordset containing a join:
>
> SELECT *
> FROM customers_cus, orders_ord
> WHERE orders_ord.customerid_ord=customers_cus.customerid_cus AND
> company_cus='" & Request.Form("companymenu") &"'
>
> Whenever the database field orders_ord.customerid_ord is not null, the
> form
> fields on the page preview fine. However, if orders_ord.customerid_ord is
> null,
> I receive the following error message:
>
> ADODB.Field error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted.
> Requested
> operation requires a current record.
>
> How do I make it that orders_ord.customerid_ord is able to be null?
>