|
 |
asp_databases thread: Reference 2 fields of same name from 2 different tables
Message #1 by "John Mason" <john@w...> on Wed, 17 Jan 2001 04:22:21 -0000
|
|
SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
Is it possible to assign a.REFV_ID to a variant? eg. When I attempt to do
the following -
id1 = objRS("a.REFV_ID")
id2 = objRS("b.REFV_ID")
name1 = objRS("a.REFV_NAME")
name2 = objRS("b.REFV_NAME")
I'm getting the following error message -
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
Can anyone help out there!?!?
Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 17 Jan 2001 13:35:27 +0100
|
|
You already put an alias on the table by using "AS a". You can do the same
with the fields, like this:
SELECT a.REFV_NAME AS a_REFV_NAME, a.REFV_ID, b.REFV_NAME AS b_REFV_NAME,
b.REFV_ID
FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
Now you can access the a.REFV_NAME and b.REFV_NAME with their aliases, like
this:
name1 = objRS("a_REFV_NAME")
name2 = objRS("b_REFV_NAME")
HtH
Imar
At 04:22 AM 1/17/2001 +0000, you wrote:
>SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
>FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
>
>Is it possible to assign a.REFV_ID to a variant? eg. When I attempt to do
>the following -
>
>id1 = objRS("a.REFV_ID")
>id2 = objRS("b.REFV_ID")
>name1 = objRS("a.REFV_NAME")
>name2 = objRS("b.REFV_NAME")
>
>I'm getting the following error message -
>
>ADODB.Recordset error '800a0cc1'
>
>Item cannot be found in the collection corresponding to the requested name
>or ordinal.
>
>Can anyone help out there!?!?
Message #3 by "Wally Burfine" <oopconsultant@h...> on Wed, 17 Jan 2001 14:13:30 -0000
|
|
Try printing out the field names to determine what they are being called, or
you can use the ordinal position of the field to set the variables.
1. for i=0 to objRS.fields.count-1
response.write "Field " & i & " name: " & objRS(i).name & "<br>")
next
or
2.
id1 = objRS(0)
id2 = objRS(2)
name1 = objRS(1)
name2 = objRS(3)
>From: "John Mason" <john@w...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Reference 2 fields of same name from 2 different
>tables
>Date: Wed, 17 Jan 2001 04:22:21 -0000
>
>SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
>FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
>
>Is it possible to assign a.REFV_ID to a variant? eg. When I attempt to do
>the following -
>
>id1 = objRS("a.REFV_ID")
>id2 = objRS("b.REFV_ID")
>name1 = objRS("a.REFV_NAME")
>name2 = objRS("b.REFV_NAME")
>
>I'm getting the following error message -
>
>ADODB.Recordset error '800a0cc1'
>
>Item cannot be found in the collection corresponding to the requested name
>or ordinal.
>
>Can anyone help out there!?!?
>
Message #4 by ygimburg@e... on Wed, 17 Jan 2001 09:57:50 -0600
|
|
SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
You don't need to use AS for table name alias.
Statement will look like:
SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
FROM rmr_mdr_ref_Values a, rmr_mdr_ref_values b....
-----Original Message-----
From: John Mason [mailto:john@w...]
Sent: Tuesday, January 16, 2001 10:22 PM
To: ASP Databases
Subject: [asp_databases] Reference 2 fields of same name from 2
different tables
SELECT a.REFV_NAME, a.REFV_ID, b.REFV_NAME, b.REFV_ID
FROM rmr_mdr_ref_Values AS a, rmr_mdr_ref_values AS b....
Is it possible to assign a.REFV_ID to a variant? eg. When I attempt to do
the following -
id1 = objRS("a.REFV_ID")
id2 = objRS("b.REFV_ID")
name1 = objRS("a.REFV_NAME")
name2 = objRS("b.REFV_NAME")
I'm getting the following error message -
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
Can anyone help out there!?!?
|
|
|
View Cart
