Multiple Recordset from two tables

[Max]

I am new in the .asp world. I have two different tables in my database (Items
& Categories) I would like to know if it is possible to retrieve a few columns
from one table and a few more from the other and combine the results to the
same asp page on the browser.

I can be more specific if needed.

Thank you,

Max

[Rama Ramachandran]

Use a SQL JOIN statement to join the two tables on some field that is common
(maybe Category ID).

The syntax will be

Select Table1.Field1, Table1.Field2, ... Table2.Field1, Table2.Field2...
FROM Table1 INNER JOIN Table2 ON
Table1.CommonField = Table2.CommonField

You will then get a recordset with all the fields you requested, where the
common fields are equal among the two tables. You can treat this recordset
like a recordset you fetch from a single table, and dump its contents on
to the browser using Response.Write







"Max" <maxthemenace@hotmail.com> wrote:
>
>I am new in the .asp world. I have two different tables in my database (Items
>& Categories) I would like to know if it is possible to retrieve a few columns
>from one table and a few more from the other and combine the results to
the
>same asp page on the browser.
>
>I can be more specific if needed.
>
>Thank you,
>
>Max

[Max]

Thank you for your reply

Ok i got it to work the way you told me. thank you.

One more question.

I have made a Add .asp page so i can add records to the database. Well is
there anyway that one field on the form can be saved to two different tables
in my database. Lets say I want the "First Name" Paul to be added to the
column "name" in the "Items" table, and also saved on the column "name" in
the "categories" table ?

Do you understand what I mean.

Thank you for your help
Icq: 41680239

Max

[KAJ]

hi max
if i have understood you problem correctly, what you need
is a JOIN query
____________________________________________________
e.g.
select Items.itemname, Categories.categoryname from
Items,Categories where Items.Ino=Categories.Ino
____________________________________________________
You can execute this query to get a recordset by
____________________________________________________
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "DRIVER={SQL Server};
SERVER=ServerName;
UID=UserID;
pwd=password;
DATABASE=dbname"

Dim SqlX
SqlX = "select Items.itemname,
Categories.categoryname
from Items,Categories where Items.Ino=Categories.Ino"

Dim RSX
Set RSX = Con.Execute(SqlX)
____________________________________________________
Now RSX(0) is the itemname from Items table
and RSX(1) is the categoryname from Categories table
____________________________________________________





"Max" <maxthemenace@hotmail.com> wrote:
>
>I am new in the .asp world. I have two different tables in my database (Items
>& Categories) I would like to know if it is possible to retrieve a few columns
>from one table and a few more from the other and combine the results to
the
>same asp page on the browser.
>
>I can be more specific if needed.
>
>Thank you,
>
>Max

[Shawn]

Max,
I don't know of a simple way to do that in one statement. What I would
recommend is to create a stored procedure (Query Def if you are using Access)
that contains two insert statements. You can then call your stored procedure
from your ASP code. The procedure would look something like this:

create procedure PostData (@fname as varchar(30)) as
begin tran --Ensures that if one of the inserts fails, both do
insert into items (name) values (@fname)
insert into categories (name) values (@fname)
commit tran
go

When you call the procedure from asp, just do something like this:
cnnDB.execute "PostData 'Joe'"


"Max" <maxthemenace@hotmail.com> wrote:
>
>Thank you for your reply
>
>Ok i got it to work the way you told me. thank you.
>
>One more question.
>
>I have made a Add .asp page so i can add records to the database. Well is
>there anyway that one field on the form can be saved to two different tables
>in my database. Lets say I want the "First Name" Paul to be added to the
>column "name" in the "Items" table, and also saved on the column "name"
in
>the "categories" table ?
>
>Do you understand what I mean.
>
>Thank you for your help
>Icq: 41680239
>
>Max
>