Child list for field .. .cannot be created in joined tables

[tombihn]

My original query for data from the database was:


Dim SQL_RecentWorkOrders As String =
"Select * from workorder WHERE date>#"
+ dtToday.AddYears(-YearsToGoBack).ToString
+ "# ORDER BY worknum DESC"

The updated query is as follows:

Dim SQL_RecentWorkOrders As String =
"SELECT * FROM workorder
INNER JOIN customer ON workorder.custid = customer.custid
WHERE workorder.date>#"
+ dtToday.AddYears(-YearsToGoBack).ToString
+ "# ORDER BY worknum DESC"

The rest of the code is as as follows:

daAccess = New OleDbDataAdapter(SQL_RecentWorkOrders, cnAccess)
daAccess.Fill(dsAccess, "WorkOrders")
Debug.Print(dsAccess.Tables(0).Rows.Count.ToString)

'Bind Work Orders
'WorkOrderNumberCbo.DataBindings.Add("Text", dsAccess.Tables(0), "worknum")

WorkOrderNumberCbo.DataSource = dsAccess.Tables("WorkOrders").DefaultView
WorkOrderNumberCbo.DisplayMember = "workorder.worknum"
WorkOrderNumberCbo.ValueMember = "workorder.worknum"

CustIDTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.custid")
LastNameTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.operlast")
FirstNameTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.operfirst")
DescriptionTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.description")
AcresTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.acres")
GridSizeSelection.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.grid")
cmWorkOrder = CType(Me.BindingContext(dsAccess.Tables(0)), CurrencyManager)

...I added the table identifier, "workorder." in the data member string after receiving the following error: "Cannot bind to the property or column custid on the DataSource.Parameter name: dataMember".

If I do the following in the immediate window, I get the results shown

?dsAccess.Tables(0).Columns(0).ColumnName.ToString
"worknum"
?dsAccess.Tables(0).Columns(1).ColumnName.ToString
"workorder.custid"

I'm not interested in updating the data. Other than setting up aliases for each field, how can i reference the tablename.fieldname in the DataMember string parameter for DataBindings.Add? Is there an escape character?

 

[Menthos]

SELECT * is pretty much considered bad practice - you should really be denoting the individual fields you need to retrieve, and that might resolve your issue at the same time. You'd need to add aliases for your two tables as you have ambiguous column names.

Personally I'd probably change the select to use aliases, e.g :

SELECT a.Field1 as field1, a.Field2 as field2 FROM table1 a INNER JOIN table2 b ON a.field = b.field ... etc.

You shouldn't need to access the schema.fieldname when referencing the datatable.

Might be worth a try

 

[tombihn]

Menthos,
Thanks for the advice. I also wonder.. would it be better practice to pull in the two tables seperately into the dataset then setup a reference within the dataset? Is that very difficult?

Thanks again

 

[Menthos]

It's certainly possible but really shouldn't be necessary judging by your requirements - a database server is generally going to be the most efficient route at delivering the recordset you need.