Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 29th, 2005, 08:05 AM
Authorized User
 
Join Date: Jan 2005
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem joining tables using vfpoledb in c#

hi everybody out there, i'm facing a problem trying to joining dbf tables. this is my scenario:
i'm using oledb provider for visual fox pro 9.0 to access dbf tables in a free table directory. i have two tables i like to join, the resumed tables are:

table m_solic fields: fech, cod_est, cod_est_rc.
table c_est_solic fields : cod_est, estado.

and i want to make a query with this result:

table (query) solic: fech, cod_est, desc_est, cod_est_rc, desc_est.

so when i'm configuring the oledbdataadapter i wrote directly the following sql sentence in the wizard:

SELECT m_solic.*, c_est_solic.ESTADO, c_est_solic_1.ESTADO
FROM (m_solic INNER JOIN c_est_solic ON m_solic.COD_EST = c_est_solic.COD_EST) LEFT JOIN c_est_solic AS c_est_solic_1 ON m_solic.COD_EST_RC = c_est_solic_1.COD_EST

which apparently is right!!! but the wizard give errors in select sintax, and unrecognized phrase or keyword.
i use the query builder in the oledbdataadapter configuration wizard and it builds a similar statement but it doesn't work too giving the same errors.
any idea whats happening in here???? thanks in advance!!!!
 
Old September 27th, 2005, 11:42 AM
Registered User
 
Join Date: Sep 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If these are datasets, try using the merge method to merge the 2 tables into 1 datset and then execute your select statement.

 
Old April 1st, 2006, 03:10 AM
Registered User
 
Join Date: Feb 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use DataAdapter to fill both tables in DataSet. Create a relation between them at runtime –

   Dim CON As String = "<Connection String>"

   Dim sqlm_solic As String = "Select fech, cod_est, cod_est_rc from m_solic"
   Dim DAm_solic As SqlDataAdapter = Nothing
   Dim sqlc_est_solic As String = "Select cod_est, estado from c_est_solic"
   Dim DAc_est_solic As SqlDataAdapter = Nothing
   Dim DS As DataSet = Nothing
   Try
      Dim connection As New SqlConnection(CON)
      DAm_solic = New SqlDataAdapter(sqlm_solic, connection)
      DS = New DataSet
      DAm_solic.Fill(DS,"m_solic")
      DAc_est_solic = New SqlDataAdapter(sqlm_solic, connection)
      DAc_est_solic.Fill(DS,"c_est_solic")

      DS.Relations.Add("myRel",DS.Tables["m_solic"].Columns ["cod_est"], _
      DS.Tables["c_est_solic"].Columns["cod_est"])

      connection.Close()
   Catch ex As Exception
      MsgBox("Error")
      Me.Close()
      Return
   End Try

I would suggest using an ORM framework (I use DevForce Express) for quickly establishing relations.





Similar Threads
Thread Thread Starter Forum Replies Last Post
joining tables pit_bait SQL Language 2 September 28th, 2007 07:29 AM
Joining Three Tables Alexpizzoferro Classic ASP Databases 5 September 1st, 2006 04:08 PM
joining 3 tables koskalgr MySQL 5 March 24th, 2006 09:52 AM
urgent: What is the syntax for JOINing 3 tables chipset VB Databases Basics 1 December 16th, 2004 06:11 AM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.