Join two table from different database. URGENT!

Hi,

anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

Sub Page_Load(Source as Object, E as EventArgs)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestidinner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

.....................

The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.

Ann123

In SQL Server, you can refer to tables in a different database inside the SQL using the appropriate syntax.

DatabaseName.Owner.TableName

For instance:

MyOtherDatabase.dbo.Guest

This presumes that the user specified has rights to both the default database in the connection as well as the second database.

|||

thanks so much douglas for your reply.

I know what you mean. In fact, i've done that in asp and its working well. However, i don't know how to do the syntax inasp.NET since i have to do 2 different connection and join them together in select statement using the inner join.

I ever try below syntax, but still got error.

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnectionSM2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinsqlCommandSM2.SM2 .dbo.Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

TheSM2 is my Database andDepartment is refer to the table in SM2 Db.

Anybody know how to do the correct syntax??

thnks

Ann123

|||

Inside your SQL statement, you cannot reference you sqlCommandSM2, because that is a VB.NET variable, and the SQL Server getting the command will not know what that is. A simplified SQL statement using two databases is (presuming SM2 is the name of your other database):

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.Department onSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

|||

hi douglas.. thanks once again for your reply.

i've done that but the problem now is,how to bindthe 2 sqlcommand when it executed since it connected to different sqlconnection and sqlcommand.

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.DepartmentonSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

.....

The sqlCommand2 is refer to sqlconnection1 whereas the second sqlCommandSM is refer to sqlconnection2 (connection for the SM2).

i've tried to do like this (below), so that it execute the same connection concurrently but got error.

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

Dim employee2 As SqlDataReader =sqlCommandSM2.ExecuteReader()

How should i do the syntax ?

thanks.

Ann123