How to get data from 2 different databses?

[Maddog]

Hi all,

i hope someone can post a solution to my problem, i am a beginner and
work with
visual web developer express edition.

what i am currently working on is an interactive web-page that
displays project information data.
The problem i am having is as follows:
I have 2 databases, one contains the PROJECT information, the other is
the CRM database.
I want to display the contacts name of an project into a textbox,
but the problem is the projects DB only contains the contacts ID
number, stored by project No.
The CRM database stores the contacts name by ID number.

to sum it up, what i need to do is Query the contacts name from CRM
DB, with contact ID and project No
as parameters. The contact ID is stored under the project No in the
PROJECT DB.
The project No is entered by the user in a textbox, with a submit
button.

so its >Get project number,(entered by user in a text box) Then get
contact's ID from PROJECT DB,
Then get the name from CRM DB with the ID AND project No.

And at last it must display this contacts name in a text box.

ALSO> whats a pain is that the field of PROJECT DB which contains ID
data is DIFFERENT than
the field from the CRM DB ( one is string the other integer)


any ideas would surely help,
thanks in advance.
Tommy

 

[Mark Rae]

The problem i am having is as follows:
I have 2 databases, one contains the PROJECT information, the other is
the CRM database.

No problem.

ALSO> whats a pain is that the field of PROJECT DB which contains ID
data is DIFFERENT than the field from the CRM DB ( one is string the other
integer)

No problem.

However, you've forgotten to tell us the one absolutely vital piece of
information we need in order to help you...

What database is it?

 

[Aidy]

Assuming it is SQL server you refer to other databases like this;

dbname..tablename

So from Database A you can

SELECT field FROM DatabaseB..SomeTable WHERE ....

 

[Maddog]

No problem.


No problem.

However, you've forgotten to tell us the one absolutely vital piece of
information we need in order to help you...

What database is it?

--http://www.markrae.net

ow...
now let me see the PROJECT databse is an Acces DB
and the CRM DB is an SQL database.
these are all connected in my Database explorer.
ive also tried to do it with datasets/tableadapters but i cant get it
to work.

was that what you were missing?
thanks in advance

 

[Mark Rae]

ow...
now let me see the PROJECT databse is an Acces DB
and the CRM DB is an SQL database.
these are all connected in my Database explorer.
ive also tried to do it with datasets/tableadapters but i cant get it
to work.

was that what you were missing?

Yes - makes a bit of a difference, don't you think...? For all we knew, you
could have been using Oracle and MySql etc...

Anyway, you have several options.

Firstly, does your PROJECT database *have* to stay as a Jet database? (N.B.
Jet is the RDBMS which Access and other apps use, usually having a .mdb
extension.)

If it doesn't, just import it into SQL Server and write some queries with
some joins...

If it does have to stay as a Jet database, then you have two main choices:

1) create linked tables in your PROJECT database which point to your CRM
database
http://office.microsoft.com/en-us/access/HA102004941033.aspx

2) create a linked server in your CRM database which points to your PROJECT
database
http://msdn2.microsoft.com/en-us/library/ms190479.aspx

 

[Maddog]

Yes - makes a bit of a difference, don't you think...? For all we knew, you
could have been using Oracle and MySql etc...

Anyway, you have several options.

Firstly, does your PROJECT database *have* to stay as a Jet database? (N.B.
Jet is the RDBMS which Access and other apps use, usually having a .mdb
extension.)

If it doesn't, just import it into SQL Server and write some queries with
some joins...

If it does have to stay as a Jet database, then you have two main choices:

1) create linked tables in your PROJECT database which point to your CRM
databasehttp://office.microsoft.com/en-us/access/HA102004941033.aspx

2) create a linked server in your CRM database which points to your PROJECT
databasehttp://msdn2.microsoft.com/en-us/library/ms190479.aspx

--http://www.markrae.net

ok,

Ive seen the links, but i dont think they apply for me,
what i really want to do is construct this inside the Dataset page
with TableAdapters.(in visual web developer)
the project databse i can edit, but not the CRM DB (read only).
Ive tried to set this up with the datasets but u just can't construct
a query there which
is dependant on a Query of another DB..

 

[sloan]

I'll give you a generic example:

You have Employees in one db (DB1) and Depts in a second db (DB2).


Create a strong dataset. OrgDS

Two tables.
Employee
Dept


OrgDS empDS = new OrgDS();
//populate it from DB1, just the Employee table

OrgDS deptDS = new OrgDS();
//populate the Dept table from DB2

OrgDS combinedDS = empDS.Merge ( //some overload here ) ;


That's a basic template. You can clean it up some, after you figure out the
tricks.

Personally, I've merged different tables.
different rows in the same table ( Like fulltime employees on one db, but
partime emps in a second db, but NO overlap).

If you have some columns from DB1, and some columns from DB2 of the same
table, that's where .Merge becomes very very tricky and doesn't work the way
you want it to.

...