Query two databases in asp and asp.net

Posted on 2009-04-16
Medium Priority
Last Modified: 2012-06-27

I have two databases, DB1 and DB2
I need to make a query out of both see below statement sample of what i want to do:

SELECT FirstName "of DB2", LastName"of DB2",  FirstName "of DB1", LastName"of DB1"
FROM Mailing List "of DB2",  Mailing List "of DB1"
WHERE FirstName "of DB2" = FirstName "of DB1" ;

What do i need to do and how must my query look like in order to let the query work.

How do i need to create my connections for asp code and asp.net code as i need to make the query work in both languages?

Question by:eaweb
  • 4
  • 2
  • 2
  • +1
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 24160957
Like this -
SELECT DB2.FirstName , DB2.LastName,  DB1.FirstName , DB1.LastName
FROM DB2..[Mailing List] DB2
INNER JOIN DB1..[Mailing List] DB1 ON DB2.FirstName  = DB1.FirstName;

Open in new window


Author Comment

ID: 24161358
is DB2..[Mailing List] syntax ok with ".." or has it to be "."

and how must i setup my connection strings in asp and asp.net to let the query work?
LVL 31

Expert Comment

by:Wayne Barron
ID: 24161890
are you saying the you have
2 Tables (In 1 database)
2 Database's

If it is 1 database with 2 tables. then

"select db2.firstname, db2.lastname, db1.firstname, db1.lastname from mailing list where db2.firstname = db2.firstname"

ALSO...  your Database Name     mailing list...
Needs to be:                mailinglist
1 word,,, not 2 words.

Other then that, if it is 2 database's, then you will need to have a crash couse in database work.
As you will only need 1 database, not 2.

Good Luck
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 15

Expert Comment

ID: 24162193
If it is two databases in sql server then you need to create a link server and make the query.
LVL 25

Expert Comment

ID: 24165416
"is DB2..[Mailing List] syntax ok with ".." or has it to be ".""

The syntax is perfectly fine for a database on a SQL Server backend. The square brackets are used when table/object names have embedded spaces. The ".." is just to ignore the owner of the table, you could also specify DB2.dbo.[Mailing List]

Just so we are all clear here, what is the type of the database being used? Access / SQL Server??

Carrzkiss -> Your sample SQL statement will not work on any backend and spaces in tablenames are perfectly permissible..

spprivate -> A linked server may be required only if dealing with two databases in two different servers.
LVL 31

Expert Comment

by:Wayne Barron
ID: 24165955
Did you not read what I wrote?
I stated: >>>  If it is 1 database with 2 tables. then
And my SQL Statement. (I did leave out the extra tables in the statement)
But, I was doing a example of IF it was a single DB.

I went into a post a few months back, the Author posted the question almost identical as this one.
And wanted to know how to write it up.
I sent him over information on a Double DB Statement.
He got aggrivated because I Could not read him mind....
He had a single Database with 2 tables, not 2 Database's as he had originally wrote up.

Every body makes mistakes, and I was not sure if "eaweb" had done the same thing
So that is why I stated: 1 or 2 Database's in my post.

And sorry for the missed up SQL Statement that I wrote.
We all make mistakes right? Right.
(Some of us just do not want to admit it)

And spaces in table names are fine, BUT single names are better.
And YES I do KNOW that you can use spaced names.

Have a good one.
LVL 25

Expert Comment

ID: 24166342
Carrzkiss ->

"Needs to be:                mailinglist" "And spaces in table names are fine, BUT single names are better."

Its just that "Need to be" hints at something being a requirement and not a recommendation, was just clarifying that spaces were permissible.. I agree using a single name without spaces is preferable, but is not mandatoryas your original post seems to suggest.

"Did you not read what I wrote? I stated: >>>  If it is 1 database with 2 tables."

I did, and the statement refers to a table 'mailing' which is aliased as 'list', refers to missing aliases db2 and db1 in the select list and checks for a condition which will always be true (db2.firstname = db2.firstname) if the aliasing were to be corrected.

"He had a single Database with 2 tables, not 2 Database's as he had originally wrote up."

The question and the sample SQL statement in the question makes it pretty clear to me that there are two different dbs with similarly named tables "mailing list".

"We all make mistakes right? Right. (Some of us just do not want to admit it)"
I do make mistakes and I'm humble enough to accept my mistakes when I notice it or it is pointed out to me.
My post was not intended to be a personal attack on you, just pointed out that selected elements of the post were incorrect.


Author Comment

ID: 24167466
hi guys,

i understand your issues, here some info:
i am using mssql, my two databases are on one server and i have a query page in asp and asp.net.

how many connections do i need to to setup in asp and in asp.net to make it work and how must i do that (setup the connections) to let the query work in both pages?
LVL 25

Expert Comment

ID: 24170181
"how many connections do i need"

Just one connection will suffice.. The query in my first post (24160957) can be executed against this single connection

"how must i do that (setup the connections)"

What library are you using for ASP (ado?) and ASP.NET (ADO.NET?)? Depending on the library used to establish the connection, you can choose an appropriate connection string from the link - http://www.connectionstrings.com/

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question