How to get data from 2 different databses?

Discussion in 'ASP .Net' started by Maddog, May 10, 2007.

  1. Maddog

    Maddog Guest

    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
     
    Maddog, May 10, 2007
    #1
    1. Advertising

  2. Maddog

    Mark Rae Guest

    "Maddog" <> wrote in message
    news:...

    > 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?

    --
    http://www.markrae.net
     
    Mark Rae, May 10, 2007
    #2
    1. Advertising

  3. Maddog

    Aidy Guest

    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" <> wrote in message
    news:...
    > 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
    >
     
    Aidy, May 10, 2007
    #3
  4. Maddog

    Maddog Guest

    On 10 mei, 11:35, "Mark Rae" <> wrote:
    > "Maddog" <> wrote in message
    >
    > news:...
    >
    > > 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?
    >
    > --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
     
    Maddog, May 10, 2007
    #4
  5. Maddog

    Mark Rae Guest

    "Maddog" <> wrote in message
    news:...

    >> 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?


    > 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


    --
    http://www.markrae.net
     
    Mark Rae, May 10, 2007
    #5
  6. Maddog

    Maddog Guest

    On 10 mei, 12:14, "Mark Rae" <> wrote:
    > "Maddog" <> wrote in message
    >
    > news:...
    >
    > >> 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?

    > > 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
    > 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..
     
    Maddog, May 10, 2007
    #6
  7. Maddog

    sloan Guest

    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.

    ...







    "Maddog" <> wrote in message
    news:...
    > 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
    >
     
    sloan, May 10, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. AD
    Replies:
    1
    Views:
    1,056
    Francis Shanahan
    Aug 23, 2003
  2. Tao Wang
    Replies:
    4
    Views:
    334
    Tao Wang
    Nov 9, 2005
  3. chrisdude911

    Using databses with html

    chrisdude911, Jun 17, 2006, in forum: Ruby
    Replies:
    7
    Views:
    107
    chrisdude911
    Jun 24, 2006
  4. chrisdude911

    Using databses with html

    chrisdude911, Jun 17, 2006, in forum: Ruby
    Replies:
    0
    Views:
    67
    chrisdude911
    Jun 17, 2006
  5. chrisdude911

    Using databses with html

    chrisdude911, Jun 17, 2006, in forum: Ruby
    Replies:
    0
    Views:
    75
    chrisdude911
    Jun 17, 2006
Loading...

Share This Page