Problems with multiple tables in ADO.NET

[Ian]

I have been having a number of problems trying to do
some joined queries (i.e. involving more than one table
in an SQL statement) in ADO.NET/VB.NET, using an Access XP
database running through the OleDb classes.

As an example, the following code falls over on the second
of the two fill statements (i.e. the last but one line.)

Dim cn As OleDbConnection, com1 As OleDbCommand = Nothing,
com2 As OleDbCommand = Nothing
Dim ad1, ad2 As OleDbDataAdapter, ds As DataSet
Dim sSQL1 As String, sSQL2 As String

sSQL1 = "SELECT collection_supplier_item_iKey_model,
collection_supplier_item_iKey_fabric,
collection_supplier_item_iKey_size FROM INPUT_ARTICLES"
sSQL2 = "SELECT model, fabric, size FROM INPUT_PRICES"
ds = New DataSet()

cn = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=DataRepository.mdb")

com1 = New OleDbCommand(sSQL1, cn)
ad1 = New OleDbDataAdapter()
ad1.SelectCommand = com1
ad1.TableMappings.Add("Table", "INPUT_ARTICLES")
com2 = New OleDbCommand(sSQL2, cn)
ad2 = New OleDbDataAdapter()
ad2.SelectCommand = com2
ad2.TableMappings.Add("Table", "INPUT_PRICES")

cn.Open()
ad1.Fill(ds)
ad2.Fill(ds)
cn.Close()

The error message that results is :
Unhandled Exception: System.Data.OleDb.OleDbException:
Unspecified error: E_FAIL(0x80004005)

I.e it doesnt actually give any clue as to what the
problem is. Having played around with this problem for a
while, including using a data reader to try to access
these two tables by means of a single joined SQL
statement, and each time running across the same error
message, which doesnt actually say what the problem is, I
am quite mystified. Any ideas what may be causing this?

Thanks

Ian

 

[William \(Bill\) Vaughn]

Are you using Try/Catch exception handling to dump the Exception.Tostring?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Ron Allen]

Ian,
You are mapping the same table name (Table) to two different entries.
You can possibly map Table1 to the second or just use the overload of Fill
that allows you to specify the DataTable name to fill (i.e. ad2.Fill(ds,
"INPUT_PRICES") ).

Ron Allen

 

[Jerry]

Ian,

Use a Try/Catch structure around your code and display the contents of "ex".
You may wish to note that it should not be necessary to open a connection
for a properly defined DataAdapter object. Your code should look like this:

Try
ad1.Fill(ds)
ad2.Fill(ds)
Catch ex As Exception
'Then show the contents of ex in a message box here.
End Try

Also you will have to establish parent/child relationships in your dataset
in order to deal with the data in a related way. You will then use the
getchildrecords method.
Another way to do this is to use the SQL Join command to join the results
together in one table.

Good Luck