sql joins in VB

[cj]

If I have a dataconnection open to a sql server database, can I execute
a sql command containing a join between several tables in that database?

What if I have tables in different databases that I want to join, is
this possible?

 

[rowe_newsgroups]

If I have a dataconnection open to a sql server database, can I execute

a sql command containing a join between several tables in that database?

Yes, just enter it as normal.

What if I have tables in different databases that I want to join, is
this possible?

If you can do it with standard SQL statements then yes. For example
Sql Server supports querying multiple databases, but I don't think you
can join two dbs from different providers. In other words, you should
be able to join two Sql Server databases, but probably not an Oracle
and Sql Server database. If you use strongly typed datasets, you can
use a datarelation object to join two business objects, which sort of
meets your requirement.

Thanks,

Seth Rowe

 

[Cor Ligthert[MVP]]

cj,

To fill a datatable with that is not any problem as long as your SQL
procedure returns one resultset. (You can do it even with more datatables to
one dataset, but then you have to use the mappings).

However don't expect any commandbuilder or generator will do for you the
Update/Delete/Insert commands for you.

Cor

 

[cj]

I guess what perplexes me about joining tables from different sql
databases, even if both databases are sql databases on the same sql
server, is I believe both databases would have to have a sqlconnection
and isn't the sqlcommand given only one connection
(mysqlcommand.connection = ...) that it will work for? So how could a
sql query that pulls from multiple databases work?

I know I can write a sql string to do this in sql query analyzer and it
works but I can't see how it would work from VB. Sometimes it might be
necessary.

Here's an example from sql query analyzer

update allanis
set fullname=master.fullname,
street = master.street,
city = master.city,
state = master.state,
zip = master.zip
from amyexcel...allanis$ allanis
inner join trs.dbo.master master
on allanis.btn = master.btn

heck in this query one of the tables is actually an excel spreadsheet
that is added as a linked server.

 

[Charles Wang[MSFT]]

Hi,
Per my test, it is also fine to use SqlCommand to execute the T-SQL
statement with linked server. You may refer to the following code:
Dim cn as New
SqlConnection("server=sha-chlwang-2k3\\wow;database=Northwind2k5;Integrated
Security=SSPI")
Dim cmd As New SqlCommand
Dim nRet As Integer
cmd.Connection = cn
cmd.CommandText = "update [master] set
fullname=allanis.fullname,street = allanis.street,city = allanis.city,state
= allanis.state,zip = allanis.zip from amyexcel...master$ [master] inner
join northwind2k5.dbo.allanis on allanis.id = [master].id"
cn.Open()
nRet = cmd.ExecuteNonQuery()
MessageBox.Show(nRet.ToString())
cn.Close()

The above VB code is converted from C#. If there is anything mistake,
please feel free to point out. Hope this helps. If you have any other
questions or concerns, please feel free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Eternal Snow]

if you need to join datas from different providers, there are 2 ways i
found:
1 add all but one to the one (must be SqlServer Enterprise). As i know,
SqlServer 2005 can add other's database like a mirror, even from DB2.
2 join them by code (must use VB9.0 / .net 3.