Solved

3 tables, one dataset.  Can it be done?

Posted on 2003-11-14
37
370 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
  • 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
 

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 500 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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
 

Expert Comment

by:JAB79
ID: 9756469
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now