Select data from two different table

ct · October 31st, 2006, 01:25 PM

how i want to select the data from two different table from ms access and display it in VB.Net...for example..
select id from table A and title from table B...and display it in one form in VB.Net. anyone can help?

BrianWren · October 31st, 2006, 07:15 PM

Your SQL should be:

Code:

SELECT A.ID, B.TITLE
FROM   [table A] AS A, 
        [table B] AS B
WHERE  A.[Some Field] = B.[Some Field]

You might need to drop the word âASâ from the FROM clause; different DBs handle that better than others.

ct · November 1st, 2006, 04:46 AM

but in vb.net the language is different. i don't know how to code and retrieve the data from ms access and code it in vb.net. can u show me simple example?

BrianWren · November 1st, 2006, 12:48 PM

I haven't done musch with this, but I have done enough to feel confident of this answer:

In the toolbar there are several Data-oriented controls. Data adapters, data grids, etc.

In help, there are tutorials on how to connect to data.

Follow one of these tutorials, then adapt the results to your specific need (using SQL similar to what I posted before.

When you add the controls (etc.) to your project from the toolbar, the code to create them is added to your project in a collapsed region. Expand that region to see what VB.NET did to implement your dragging and dropping.

Bob Bedell · November 1st, 2006, 09:51 PM

Here's the basic of working with a DataSet populated from an Access db, though as always there are a number of ways to do this sort of stuff:

Code:

Imports System.Data.OleDb

Module Module1

   Sub Main()
      Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & _
           "C:\Northwind.mdb;"

      Dim cn As New OleDbConnection(strConn)
      Dim da As OleDbDataAdapter = New OleDbDataAdapter
      Dim ds As DataSet
      Dim row As DataRow

      da.TableMappings.Add("Table", "Customers")
      cn.Open()
      Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers", cn)
      cmd.CommandType = CommandType.Text
      da.SelectCommand = cmd
      ds = New DataSet("Customers")
      da.Fill(ds)

      ' Dump dataset contents to Console window
      Dim oRow As DataRow
      Dim strRecord As String

      For Each oRow In ds.Tables("Customers").Rows
         strRecord = "Customer Id: " & oRow("CustomerId").ToString()
         strRecord = strRecord & "  Company Name: "
         strRecord = strRecord & oRow("CompanyName").ToString()
         Console.WriteLine(strRecord)
      Next
      cn.Close()
   End Sub

End Module

Just point the connection string toa copy of the Northwind database.

HTH,

Bob

tej_birje · August 31st, 2007, 02:14 AM

Hello,
Can u help me on, How to create DataView by executing Join Queries on DataSet (Not on Database)
Tej.

tej_birje · August 31st, 2007, 02:17 AM

Hello,
Can u help me on, How to create DataSet by executing Join queries on DataSet ( Not on DataBase )
Tej.

tej_birje · August 31st, 2007, 02:20 AM

Sorry,
I want to correct my last Question.
Can u help me on, How to create DataView by executing Join queries on DataSet ( Not on DataBase )
Tej.

planoie · August 31st, 2007, 08:23 PM

To my knowledge, you can't do this directly in a dataset. You can establish row filters on a dataview in order to restrict the data table results. Also, you can establish relationships between data tables in a data set and use the data table functionality to get child rows based on relationships.

-Peter