combining data from 2 database tables to convert numberID to text? ASP/VB

Report · Jul 06, 2006

Hi.

I have the following two database tables:

Orders Table

orderID
customerID
collectionregion (numerical)
deliveryregion (numerical)

Regions Table

regionID
regionname

On my ASP page I have a recordset (rsOrders) which displays the order
details.

When this is displayed, the collectionregion and deliveryregion fields
display as a numerical value, as they should.
This numerical value ties in with the regionID field from the regions table,
to indicate what region the collection/delivery is in.

I would like to be able to display the region name as opposed to the number.

I created a third recordset on the page, which goes like this:
SELECT * from tblRegions WHERE regionID = regionvar

regionvar 1 rsOrders.Fields.Item("deliveryregion")

Now, this works great when I DON'T have the rsOrders information in a Repeat
Region, but when I add the repeat region, it simply displays the region name
for the first record for every record. Why would it not work in the repeat
region?

What can I do to resolve this?
Any help offered would be greatly appreciated. Thanks.
Regards
Nath.

Report · Jul 06, 2006

Hi,

Ok, thinking out loud has helped a little! My Repeat Region is for the
rsOrders recordset so simply dropping a value from a second recordset into
this existing repeat region won't cause the second recordset to repeat which
is why I'm seeing the first records values in every record!

Still leaves me with the problem though.

The thing that's confusing me is that both the collectionregion and
deliveryregion fields in the Orders table hold an ID value that ties in with
the Regions table primary key field (regionID) however because they are not,
themselves, called regionID I can't seem to do an Inner Join in a database
query like I normally would if there was just one field that tied in with
the regionID.

Would appreciate some guidance. Much appreciated. Thanks.
Regards
Nath.

"Nathon Jones" <sales@NOSHPAMtradmusic.com> wrote in message
news:e8ikhj$o8l$1@forums.macromedia.com...
> Hi.
>
> I have the following two database tables:
>
> Orders Table
>
> orderID
> customerID
> collectionregion (numerical)
> deliveryregion (numerical)
>
> Regions Table
>
> regionID
> regionname
>
> On my ASP page I have a recordset (rsOrders) which displays the order
> details.
>
> When this is displayed, the collectionregion and deliveryregion fields
> display as a numerical value, as they should.
> This numerical value ties in with the regionID field from the regions
> table, to indicate what region the collection/delivery is in.
>
> I would like to be able to display the region name as opposed to the
> number.
>
> I created a third recordset on the page, which goes like this:
> SELECT * from tblRegions WHERE regionID = regionvar
>
> regionvar 1 rsOrders.Fields.Item("deliveryregion")
>
> Now, this works great when I DON'T have the rsOrders information in a
> Repeat Region, but when I add the repeat region, it simply displays the
> region name for the first record for every record. Why would it not work
> in the repeat region?
>
> What can I do to resolve this?
> Any help offered would be greatly appreciated. Thanks.
> Regards
> Nath.
>

Report · Jul 06, 2006

"Nathon Jones" <sales@NOSHPAMtradmusic.com> wrote in message
news:e8il4e$otb$1@forums.macromedia.com...
> Hi,
> The thing that's confusing me is that both the collectionregion and
> deliveryregion fields in the Orders table hold an ID value that ties in
> with the Regions table primary key field (regionID) however because they
> are not, themselves, called regionID I can't seem to do an Inner Join in a
> database query like I normally would if there was just one field that tied
> in with the regionID.

Sure you can.

SELECT O.OrderID, O.CustomerID, C.RegionName AS CollectionRegion,
D.RegionName AS DeliveryRegion
FROM Orders O
INNER JOIN Regions C ON O.CollectionRegion=C.RegionID
INNER JOIN Regions D ON O.DeliveryRegion=D.RegionID

I suggest you take a couple tutorials:
www.sqlcourse.com
www.sqlcourse2.com