One dataAdapter and two tables. Does it work?

  • Thread starter Thread starter Theo
  • Start date Start date
T

Theo

Hi all,

I am using a dataapater, a dataset and an select sql command to retrieve
data from two related tables using the following code:

Dim da As New OleDb.OleDbDataAdapter("SELECT Materials.*,
Material_Functions.fName FROM Material_Functions INNER JOIN Materials ON
Material_Functions.ID = Materials.Function;", conn)
Dim ds As New DataSet("DSMaterials")
da.Fill(ds, "Materials")

You cannot see it in the code but table 'Materials' and table
'Material_Functions' both have a field called 'fName'.

For simple (one table) sql commands I had no problem binding textboxes to
fields in the dataset using the following code:

(Assume sql command is "Select * From Materials;")

TextBox.DataBindings.Add("Text", ds, "Materials.fName")

But this doesn't work any more. I checked the output window during run-time
and all the fields are placed under one table (Materials) which is logical.
I also noticed that the 'fname' field coming from the 'Material_Functions'
table is named 'Material_Functions.fName', which is also logical.

The question is when it comes to data binding what to I put in the third
argument???
'Materials.Material_Functions.fName' doesn't work.

I know that I can use a second dataadapter to load the second table
separately and set the relationship in the dataset manually, but I would
prefer to do it in just one dataadapter (don't ask me why, because I don't
know that either).

So how do I work around it with just one dataAdapter? And if I should use 2
of them what is the advantage of that over the single DA?

Thanks in advance

Theo
 
Have you tried 'Material_Functions.fName'?

One work-around is to give the second fName column an
alias like 'fName2'. So in your SQL,
replace "Material_Functions.fName"
with "Material_Functions.fName 'fName2'".

No experience with two data adapters, so no comment on
that.

By the way, I would also put an alias after each table
name to reduce the total # of characters in the SQL and
thus simplify it.
 
Back
Top