Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do you get VB.Net Dataset Object to recognize multiple tables?

Posted on 2009-05-12
3
Medium Priority
?
762 Views
Last Modified: 2013-11-07
New to VB.Net and trying to read two tables from the same database into the same Dataset object.  I've included a small section of the code below which is not working (it's failing on the first Session statement).  The Visual Studio debugger says that the field "Entry Education Level" is not in the table; however, there is no doublt that it is. I've checked the database for spelling errors and the table is there.  Also,  with the debugging window available, I can see that the Dataset object appears to be confused because when it reports on the number of fields (columns) in this table (Client Education) it says 29 which is actually the number of fields in the Clients table.  I'm sure I'm not correctly initializing some variables.  Any help greatly appreciated.
Dim connection As New Data.OleDb.OleDbConnection _
         ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=e:\webdatabase.mdb")
        connection.Open()
        Dim SelectString As String
        SelectString = "select * from Clients where [First Name] = '1' and [Last Name] = '2'"
        Dim SelectCommand As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(SelectString)
        Dim Adapter As New Data.OleDb.OleDbDataAdapter()
        Dim Dataset1 As New Data.DataSet()
        Adapter.SelectCommand = SelectCommand
        SelectCommand.CommandType = Data.CommandType.Text
        SelectCommand.Connection = connection
        Adapter.Fill(Dataset1, "Clients")
 
        SelectString = "select * from [Client Education] where ClientID = '377'"
        Adapter.Fill(Dataset1, "Client Education")
        If (Dataset1.Tables("Client Education").Rows.Count <> 0) Then
            Session("Education") = Dataset1.Tables("Client Education").Rows(0).Item("Entry Education Level")
            Session("Highgrade") = Dataset1.Tables("Client Education").Rows(0).Item("Highest Grade Completed")
            Session("College") = Dataset1.Tables("Client Education").Rows(0).Item("Current College")
            Session("Pasteducation") = Dataset1.Tables("Client Education").Rows(0).Item("Past Education Activity")
        End If

Open in new window

0
Comment
Question by:ejefferson213
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1500 total points
ID: 24366700
can you try this:
Dim connection As New Data.OleDb.OleDbConnection _
         ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=e:\webdatabase.mdb")
        connection.Open()
        Dim SelectString As String
        SelectString = "select * from Clients where [First Name] = '1' and [Last Name] = '2'; select * from [Client Education] where ClientID = '377'"
        Dim SelectCommand As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(SelectString)
        Dim Adapter As New Data.OleDb.OleDbDataAdapter()
        Dim Dataset1 As New Data.DataSet()
        Adapter.SelectCommand = SelectCommand
        SelectCommand.CommandType = Data.CommandType.Text
        SelectCommand.Connection = connection
        Adapter.Fill(Dataset1)
 
        If (Dataset1.Tables(1).Rows.Count <> 0) Then
            Session("Education") = Dataset1.Tables(1).Rows(0).Item("Entry Education Level")
            Session("Highgrade") = Dataset1.Tables(1).Rows(0).Item("Highest Grade Completed")
            Session("College") = Dataset1.Tables(1).Rows(0).Item("Current College")
            Session("Pasteducation") = Dataset1.Tables(1).Rows(0).Item("Past Education Activity")
        End If

Open in new window

0
 

Author Comment

by:ejefferson213
ID: 24366793
Got the following error message: Characters found after end of SQL statement.  As I'm interfacing with an Access database, I'm suspecting that having these statements together is confusing Access?!?!?!?!
0
 

Author Closing Comment

by:ejefferson213
ID: 31580621
After repeated tries, I found the solution.  It would appear that once you declare the command object and give it the query string, it is retained (the query string) until you reset it.  By that I mean I had changed the query string but never called on       oledbcommandobject.CommandText = SelectString.    Once I did that the code worked fine.   Thanks for taking the time to try and help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Suggested Courses

877 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