Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

3 tables, one dataset.  Can it be done?

Posted on 2003-11-14
37
Medium Priority
?
379 Views
Last Modified: 2012-05-04
I'm trying to display data from three different tables now in one datagrid, using one dataset.  I can't get it to work with three tables, only two.  I think it has something to do with setting the datasource.  I don't know how to set that for multiple tables.  Here's the code:

Sub BindGrid()
                   Dim objDataSet As DataSet = New DataSet
                   Dim myConnection As New SqlConnection(ConnectionString)
                   Dim myConnection2 As New SqlConnection(ConnectionString2)
                   Dim Adapter As SqlDataAdapter = New SqlDataAdapter
                     
   
                   Adapter.SelectCommand = _
                       New SqlCommand(SelectCommand1, myConnection)
                       myConnection.Open()
                      Adapter.Fill(objDataSet,"accounting")
   
                      Adapter.SelectCommand = _
                       New SqlCommand(SelectCommand2, myConnection)
                       Adapter.Fill(objDataSet,"comments")
   
                   Adapter.SelectCommand = New SqlCommand(SelectCommand3, myConnection2)
                   Adapter.Fill(objDataSet,"aafifimast")
   
                   DataGrid1.DataSource = objDataSet.Tables("accounting")
                   DataGrid1.Databind()
          End Sub


And here is my connection strings for all 3 tables:

Dim ConnectionString As String = "server=myserver;uid=uid;PWD=pwd;database=default;"
Dim SelectCommand1 As String = "SELECT *, DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY daysold ASC"
Dim SelectCommand2 As String = "SELECT * FROM Comments"
Dim ConnectionString2 As String = "server=myserver;uid=uid;PWD=pwd;database=default;"
Dim SelectCommand3 As String = "Select * From aafifimast"


I have another connection string because the third table I'm trying to read data from is in a different database than the other two tables.
Help!


0
Comment
Question by:jay-are
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 14
  • 5
  • +1
37 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9748540
I can only give you general advice on this:

1) in order for this to work with one dataset, you need to find some way to combine all three sources into one dataset, array, or some such

2) the best way to do that is to create a sql statement that will draw from all three tables

3) the second step is only possible if you can link the tables somehow.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9748545
Sorry, for point 3) I meant to say:

3) the second step is only possible if you can link the databases somehow.

FtB
0
 

Author Comment

by:jay-are
ID: 9748841
Well the accounting table has the Ref.No which is the same as DealNo in aafifimast.  I could link them like that.  Only problem I see is that the Ref.No contains deal numbers that have the letters after them for adjustments where aafifimast does not.

Ideas?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jay-are
ID: 9748872
Oh I see in my other question you gave me the idea already!

Let me try to fool around with this select statement for a bit.

Thanks!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9748885
Before we even get to that point, there is a bigger issue:

Two of your tables reside in one database while the third resides in another.

Before you can construct a sql statement that can join the three tables to yield the desired dataset, you have to figure out a way to link the two databases. Can you speak to your Network Engineer about that?

FtB

0
 

Author Comment

by:jay-are
ID: 9748956
Network Engineer?  hahaha!  Sorry, let me explain the situation here.  I work for an auto dealer in Atlanta that is trying to start from the ground up on some intranet web reporting.

So they hire me to do the web design and some .net stuff.  I know html fine and had experience in programming before but I'm new to .net.  Even knewer to sql.

So its me, my boss(design concept) and basically a net admin who is learning as we go.
I have access to the sql server and I've seen "Link Servers".
Is this something I can fumble my way through?  :)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9748979
Ah--you are on SQL Server? I thought that you were using MySql?

FtB
0
 

Author Comment

by:jay-are
ID: 9748986
MS Sql

Sql Server 7

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9749003
Okay, hang on. I am going to see if I can get Anthony to join this thread--he is a good deal better at this stuff than I am....

FtB
0
 

Author Comment

by:jay-are
ID: 9749010
Thanks!
0
 

Author Comment

by:jay-are
ID: 9749128
FtB

What about something like this?

http://www.vb-helper.com/howto_ado_2db_join.html
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9749261
That is the idea! Once you can create a sql select from all three tables, you will be in a good way.

I would recommend that you create a test page so that you don't mess up any good code in progress.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9749666
The link you posted is for MS Access and not MS SQL Server.

Here are the steps to create a linked server on MS SQL Server (I am using SQL 2K, so it may be slightly different on SQL 7) from Enterprise Manager (you can also use a couple of extended stored procedures (sp_addlinkedserver and sp_addlinkedsrvlogin) to accomplish the same task.  

Select one Server as your "primary" server and the other server will be the "remote" server you wish to link to.  From you "primary" server

1. Right click on the Linked Servers object (below Security)
2. Select "New Linked Server..."
3. In the "Linked Server Properties - New Linked Server" dialog box and under the General tab, enter the name for the "remote" server in "Linked server:"
4. For Server type, select the SQL Server option
5. Under the Security tab, select the "Be made using this security context" option.
6. In the "Remote login:" and "With password" enter the appropriate login name and password for the "remote" server.
7.  Select OK and you have linked the "primary" server to the "remote" server, but not vice versa.  In other words your (one and only ) connection should only be to the "primary" server.

Once you have that setup correctly, go to SQL Analyzer and create the correct Select statement using both servers.  You can refer to the remote server with the following syntax (don't forget to alias it) from the primary server:

From linkedserver.databasename.databaseowner.tablename tablealias

For example:
From remote.db1.dbo.table1 tablealias

When you have the appropriate Select statement with the correct results in SQL Analyzer, than copy and paste to your code or for best performance and security encapsulate in a stored procedure.  Again, remember you now only need one connection string.

Let me know if you need more help,
Anthony
P.S. Let me know if you need me to fly to Atlanta <g>
0
 

Author Comment

by:jay-are
ID: 9749894
Anthony, thanks for this!

After talking to my boss he decided to just have all the tables under one database as its easier to get this working that way.  I will definately keep a copy of this for future reference cause I'm sure this will come up again with 3 servers on their way in!

So FtB, there are still points to be had!

I moved the two tables (Accounting, Comments) to the database that has 'aafifimast' in it.  Here are my select statements as of now.  It's producing multiple records:

Dim ConnectionString As String = "server=myserver;uid=uid;PWD=pwd;database=default;"
Dim SelectCommand1 As String = "SELECT *, DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY daysold ASC"
Dim SelectCommand2 As String = "SELECT * FROM Comments"
Dim SelectCommand3 As String = "Select Distinct * From aafifimast Inner JOIN Accounting on DealNo = [Ref.No]"

Never did this before I joined on that third select.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9749983
Moving all tables to one DB is going to make your life so much easier!

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9749991
So now, is the issue how to create multiple joins?


FtB
0
 

Author Comment

by:jay-are
ID: 9750013
Yes, life should be easier this way!

I'm guessing the issue is now with multiple joins for the 3 tables.

I thought I had it right but its doing multiple records now and I can't include fields from aafifimast in my datagrid.  So I'm doing something wrong.

You can still refer to the code up top, I just altered it so they all use the same connection string and what not.
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 2000 total points
ID: 9750092
I don't really know your data and I can't test from here, but it might look something like this (just add the fields you need to the select line):

SELECT *, DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold
FROM Accounting
    INNER Join Comments on Control# = ControlNo
    INNER Join aafifimast on DealNo = [Ref.No]
WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY daysold ASC

FtB
0
 

Author Comment

by:jay-are
ID: 9750469
Sorry for the delay.  Here's what I have now:

Dim SelectCommand1 As String = "SELECT *, aafifimast.[Deal.Date], DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo INNER Join aafifimast on DealNo = [Ref.No] WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY daysold ASC"

It's still repeating records.  Two records for every customer number.

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9750491
Okay, so the good news is that you are getting records from all tables, but the bad news is that you are getting duplicate information...


So as you look at each pair of duplicates, are all of the fields exactly the same in each pair?

BTW, are you doing checking this on your page or in your query analyzer? If the former, do the latter--life will be easier.

FtB


0
 

Author Comment

by:jay-are
ID: 9750516
Yeah I've been testing on the page.

I just added Select Distinct * etc. and it's producing one record correctly.

Did it in the sql analyzer and it works the same there!
Thanks!!
0
 

Author Comment

by:jay-are
ID: 9750544
Oops, its excluding and deal#'s that have a letter after them now.  It's showing 40 records and I'm pretty sure it should be 54.

How did this happen?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9750564
@Anthony:

This probably should have at least been a split, so please visit:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20798492.html

FtB
0
 

Author Comment

by:jay-are
ID: 9750579
Yes, sorry I should give him credit also for including the server join!

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9750634
That was not necessary, you did all the work.  All I wanted was a trip to Atlanta <g>

But I don't like open questions, so I will go and leave a comment...

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9750663
I am guessing that you are trying to compare the 5 digit code to the six digit code!

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9750678
Something like:

Dim SelectCommand1 As String = "SELECT *, aafifimast.[Deal.Date], DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo INNER Join aafifimast on Left(DealNo,5) = Left(Ref.No,5) WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY daysold ASC"
0
 

Author Comment

by:jay-are
ID: 9750679
Doh!
round and round we go...

Guess I can't link up those two tables if that info is different...SIGH
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9750701
Try mucking around with my last bit of code--many DBMS's support the use of the left function.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9750738
>>many DBMS's support the use of the left function.<<
Including T-SQL

Anthony
0
 

Author Comment

by:jay-are
ID: 9750753
Well that works actually, well I think its close enough!  It's up to 48 records which is probably right.  I'm sure some have disappeared off that schedule since the last time I looked at it!

Thanks so much for your help!  

Anthony, if you wanna come to Atlanta then bring it.  You can help with this mountain of a project and I'll buy you a beer.  :)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9751004
>>I'll buy you a beer<<
You have convinced me. Atlanta has to be better than this neck of the woods (DFW)

Anthony
0
 

Expert Comment

by:JAB79
ID: 9756243
I don't want to digress from the orginal thread, but I have been following this Q&A session with great interest as I am trying to take two tables from a database and place them within a dataset.  I'm sure this is very easy, but try as I might, I can't get it to work.  My code is as follows:

Sub Page_Load(Sender As Object, E As EventArgs)

If Not IsPostBack Then

  Dim Connect As OleDBConnection = New OleDBConnection
  Dim Adapter As OleDBDataAdapter = New OleDBDataAdapter
  Dim WhatsOnDS As DataSet = New DataSet
  Dim ConnectString, SelectStatement1, SelectStatement2 As String

  SelectStatement1 = "SELECT * FROM Events ORDER BY Date"
  SelectStatement2 = "SELECT * FROM News ORDER BY Date"
  ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\inetpub\wwwroot\youth\db\youthdb.mdb;"
  Connect.ConnectionString = ConnectString
  Adapter.SelectCommand = New OleDbCommand(SelectStatement1, Connect)
  Adapter.SelectCommand.Connection.Open
  Adapter.Fill(WhatsOnDS, "Events")
  Adapter.SelectCommand = New OleDbCommand(SelectStatement2, Connect)
  Adapter.Fill(WhatsOnDS, "News")
  EventsGrid.DataSource = WhatsOnDS.Tables("Events")
  Page.DataBind

  If WhatsOnDS.Tables("Events").Rows.Count < 1 Then
    Results.Text = "Unfortunately there are currently no events listed. If you you" & _
      " are an organisation and would like to submit one, please <a href='Contact-Us.htm'>" & _
      " contact us</a>."
    EventsPanel.Visible = False
  Else
    Results.Text = "Not sure what events are taking place? Then look no further!" & _
      " <p>The events listed below have all been entered by registered youth organisations" & _
      " and are open to everyone. If you are involved with an organisation and want to" & _
      " advertise a forth coming event, please <a href='Contact-Us.htm'>contact us</a>.<p>"
  End If

End If

End Sub

I get an error when I try and use the second Adapter.Fill statement;

Adapter.Fill(WhatsOnDS, "News")

which is:

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Any ideas?

Cheers,

James
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9756313
@JAB79--

One ordinarily places such requests in a new thread, perhaps including a link to this one for context. Otherwise, you are "piggybacking" questions on someone else's thread.

Having said that, create a sql statement that will join your two tables (is there some common field that will serve as a link) and pass the result to your Adaptor.

FtB
0
 

Expert Comment

by:JAB79
ID: 9756413
Sorry for "piggybacking" - am new here.  I'll try what you suggested but ideally I'd like two separate tables not the one.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9756430
>>I am trying to take two tables from a database and place them within a dataset<<

>>ideally I'd like two separate tables not the one. <<
There is a direct contradiction here.

But post in a new thread and we can discuss further.

Anthony

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 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