[RESOLVED] How to combine data from multiple tables

[jre1229]

I am used to creating a data set with data from one table, for instance:

Dim da As New OdbcDataAdapter("SELECT WORKORDER.WONUM, WORKORDER.DESCRIPTION, WORKORDER.WORKTYPE, WORKORDER.SUPERVISOR, WORKORDER.REPORTEDBY FROM DB1.WORKORDER WHERE WORKORDER.WONUM=?", conn)

Dim ds As New DataSet

da.SelectCommand.Parameters.Add("@WONUM", OdbcType.VarChar, 1000).Value = Me.workOrderNumberTB.Text.ToString

da.Fill(ds, "WORKORDER")

Now I am needing to add a second table. For instance, my second table "EQUIPMENT" has a field called "DESCRIPTION" that I would like to add to my dataset. The "WORKORDER" table has a field called "EQNUM" which is what the "EQUIPMENT" table uses as the primary key. Can anyone help me on how to combine these into one dataset?

[Tom Sawyer]

Just put a join into your select statement

Dim da As New OdbcDataAdapter("SELECT WORKORDER.WONUM, WORKORDER.DESCRIPTION, WORKORDER.WORKTYPE, WORKORDER.SUPERVISOR, WORKORDER.REPORTEDBY, EQUIPMENT.DESCRIPTION FROM DB1.WORKORDER JOIN DB1.EQUIPMENT ON DB1.EQUIPMENT.EQNUM = DB1.WORKORDER.EQNUM WHERE WORKORDER.WONUM=?", conn)

[jre1229]

Thanks. Here is what I am trying to get to work.

Dim da As New OdbcDataAdapter(" SELECT WORKORDER.WONUM, WORKORDER.DESCRIPTION, WORKORDER.WORKTYPE, WORKORDER.SUPERVISOR, WORKORDER.REPORTEDBY, WORKORDER.STATUS, EQUIPMENT.DESCRIPTION FROM DB1.EQUIPMENT EQUIPMENT INNER JOIN DB1.WORKORDER WORKORDER ON (EQUIPMENT.EQNUM=WORKORDER.EQNUM) AND (EQUIPMENT.SITEID=WORKORDER.SITEID) WHERE WORKORDER.WONUM=?", conn)
Dim ds As New DataSet
da.SelectCommand.Parameters.Add("@WONUM", OdbcType.VarChar, 1000).Value = Me.workOrderNumberTB.Text.ToString
da.Fill(ds, "WORKORDER")
Dim dt As DataTable = ds.Tables("WORKORDER")
If dt.Rows.Count = 0 Then
MessageBox.Show("Sorry, no matches for that product number. Please try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Me.assetDescriptionLBL.DataBindings.Add(New System.Windows.Forms.Binding("Text", ds, "WORKORDER.DESCRIPTION"))
Exit Sub
End If

For my databinding, I am trying to get the description field that is in the "EQUIPMENT" table, but I am getting the one that is in the "WORKORDER" table. If I change "WORKORDER" to "EQUIPMENT" on my databinding it gives me an error.

[BrianS]

Can you pull col 7 instead of the fieldname?

I know I can with oracle, not sure about odbc.

What is the exact error you get?

[jre1229]

Can you pull col 7 instead of the fieldname?

I know I can with oracle, not sure about odbc.

What is the exact error you get?

Im not sure I know what you mean by pulling col 7 or how you would do that. The way I posted is the only way I have ever done it. The error I get when I change the binding to EQUIPMENT.DESCRIPTION is "Child list for field EQUIPMENT cannot be created."

[jre1229]

I tried simply putting "7" in the place of EQUIPMENT.DESCRIPTION and it gave an error that said "Cannot bind to the property or column 7 on the DataSource.
Parameter name: dataMember"

[jre1229]

I believe I may have found the solution. I put a "1" behind "DESCRIPTION" on my binding and it worked. I guess it renames the fields with numbers behind them when there are other fields with the same name. Thanks for the help guys.

[Tom Sawyer]

I believe I may have found the solution. I put a "1" behind "DESCRIPTION" on my binding and it worked. I guess it renames the fields with numbers behind them when there are other fields with the same name. Thanks for the help guys.

Ya, if you have two columns with the same name, it'll get the first one.

One solution to avoid having to worry about this is to not use generic names like "Description" for the columns in your database and put some flag on them to show the table they're from. For instance, in your WorkOrder table, name the columns WO_Description and WO_Status instead and in your Equipment table, have EQ_Description.

That way, you'll never have to worry about aliasing columns and will always know which table the field comes from.

[BrianS]

I only really use ODP (Oracle) with VB, and with it I can reference the returned value by either field name or column number, by the order it was retrieved.

Just didn't know if that option was available for you via ODBC.

Im not sure I know what you mean by pulling col 7 or how you would do that. The way I posted is the only way I have ever done it. The error I get when I change the binding to EQUIPMENT.DESCRIPTION is "Child list for field EQUIPMENT cannot be created."