|
I am sorry if this is a redundant question. I have searched messages in the SQL and Visual Basic .net message boards and have not seen complete code example for setting up connections before executing a query which joins two(or more) tables and each table exists in a different database but on the same server.
I am aware of the naming convention ServerName.DatabaseName.TableName.Column
I am aware of populating datasets in vb.net as exemplified below
-------------- Begin populate dataset example -----------------------
Dim DataSetForReport As New DataSet
Dim BlueConnection As String
Dim BlueSQLConn As SqlConnection
Dim BlueQuery As String
Dim i, TotalRowsReturned As Integer
BlueConnection= "uid=test;password=snow;dtabase=DataBaseBlue; server=Spock"
BlueSQLConn = New SqlConnection(BlueConnection)
BlueSQLConn .Open()
BlueQuery = "Select Table1.ColumnA, Table2.ColumnB from Table1, Table2 where ColumnA=ColumnB"
Dim SqlDataAdapterForPopulateDataTable As New SqlDataAdapter(BlueQuery, BlueConnection)
i = SqlDataAdapterForPopulateDataTable.Fill(DataSetForReport, "TableOfData")
SqlDataAdapterForPopulateDataTable.Dispose()
SQLConn.Close()
If i > 0 Then
'query returned at least one row
TotalRowsReturned = i
End If
-------------- End populate dataset example -----------------------
Goal: to be able to change BlueQuery variable to refer to two different databases - DataBaseBlue and DataBaseRed like something below...
BlueQuery = "Select DatabaseBlue.Table1.ColumnA, DatabaseRed.Table2.ColumnB from DatabaseBlue.Table1, DatabaseRed.Table2
where DatabaseBlue.Table1.ColumnA=DatabaseRed.Table2.ColumnB"
If I create another connection like
RedConnection = "uid=Red; password=Car; dtabase=DataBaseRed; server=Spock"
and use it in an addition SQLConnection declaration
Dim RedSQLConn As SqlConnection
RedSQLConn = New SqlConnection(RedConnection)
RedSQLConn.Open()
How do I enable/pass SqlDataAdapterForPopulateDataTable variable two different connections? Or how do I accomplish the "Goal" in another way?
Thanks for any suggestions.
tanya
|
|
|
|
|
You can't change the query to work on two different databases at the same time.
In theory, you can probably get data from two different databases into the same DataSet using two seperate connections and queries. Just make sure the data ends up in two different DataTables in the DataSet. You should be able to put together a DataRelation object that joins the two tables, do your work on it, then use two the two different DataAdapters and their connections to write those two tables back to the databases if you've made any changes.
Sorry, I don't have any examples. I've never done it myself. You can find the docs on DataRelation here[^].
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Dave,
Thanks for taking the time to share your thoughts. I was trying to avoid two separate datatables but if that is the only way, then that is the only way.
I know there are good/great technical people at Microsoft. However, I would think this is a common enough situation that a method/tool/wrapper would have been added to be able to handle a single query referencing two or more tables in two different SQL Server databases. My two cents.....
tanya
|
|
|
|
|
tanya foster wrote: I would think this is a common enough situation that a method/tool/wrapper would have been added to be able to handle a single query referencing two or more tables in two different SQL Server databases.
It may be possible to do with a stored procedure, but not with any code on the client side that I know of. You might want to post the question in the SQL Forum[^]. There's a guy in there, Colin Angus Mackay, who knows FAR more about SQL Server than I do.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Hi,
I have the task to read excel spreadsheets with vb.net. Amongth other details the contain checkboxes, from which I need the value.
Has anyone got an idea how I should read these checkboxes?
|
|
|
|
|
use the excel object library..connect to the worksheet u need,n then u should be able to do it
|
|
|
|
|
Hi folks!
I am trying to create an Excel-like table layout.
Currently, I am using TableLayoutPanel. However, this control has its limitation, like I can't draw thicker border in certain cell.
Is there better way to create this Excel-like table layout?
Many many thanks in advanced!!!
www.thesemplice.com
|
|
|
|
|
Is this a web app or Windows ?
I'd start with the gridview, it has a lot of stuff built in already
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
This is a Windows form using Visual Basic 2005.
Isn't GridView for data that is pulled from a database?
My data doesn't come from a database.
Thanks!
www.thesemplice.com
|
|
|
|
|
No, a grid view knows how to show data from any source.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
I tried using GridView. It doesn't seem like my purpose.
I am using the TableLayoutPanel is for LAYOUT purpose. Inside the TableLayoutPanel, I put some controls.
Also, GridView draw thicker border in certain cell, right?
It seems like GridView is more to data-presentation while what I am trying to achieve is Layout Presentation.
Thanks Christian for your guide so far!
www.thesemplice.com
|
|
|
|
|
Hello Friends,
I am new to Visual Basic 6.0. I want to learn it in a simple manner.
I have form where there is 5 text boxes namely
Code
First name
last Name
Date Of Birth
Address
The database is access and the table is being deigned in similar way .
Now i need to add, edit delete data to the database.The date of Birth will be VB calendar.
The code will appear automatically when i click on the add button.
In the next part I wil keep a provision to show the data in grid and then take print out in Crytsal Report 10.
please help
|
|
|
|
|
ranabanerjee wrote: I am new to Visual Basic 6.0. I want to learn it in a simple manner.
Why?? It's a dead language. I highly suggest switching to VB.NET. You can get a free version of it here[^].
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
|
I recommend buying a book or learning to use google ( probably both ). You're asking Dave to write your program for you, when it's very simple, and widely documented. These forums are for help, not for people to write your code for you, or even to explain exactly how to write it all.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
What Dave said. VB6 is unsupported, and it's also rubbish.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
|
What's what ? I don't understand the question. Dave is the person who answered you before me ( he's got even faster since he got that cool icon... )
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
Christian Graus wrote: he's got even faster since he got that cool icon...
Your just jealous you can't have one! It sucks to be a CP Staff Member, doesn't it?! 
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
*grin* I was thinking of suggesting a sort of merged icon to Chris, a staff member crossed with MVP type thing. But then I released, there are more MVPs than staff, so my icon is already more exclusive.....
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
Christian Graus wrote: But then I released, there are more MVPs than staff, so my icon is already more exclusive.....
OK, now I'm jealous! But, our icon is  er.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Trash
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
c the basic thing is I need a help .. as i said .. can u give the source code in Vb.net
|
|
|
|
|
Before I waste my time clicking on the Google link, what part of this list are you having a problem with??
1. The database is access and the table is being deigned in similar way.
2. Now i need to add, edit delete data to the database.
3. The date of Birth will be VB calendar.
4. The code will appear automatically when i click on the add button.
5. In the next part I wil keep a provision to show the data in grid
6. and then take print out in Crytsal Report 10.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
I think you're looking for rentacoder.com.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
