How do I bind a Visual Basic 2010 RDLC Report to multiple SQL Server Tables?

Hello!

This is going to seem like a really dumb question - well, it seems so to me, anyway.

I've written a little app in VB 2010 that I'd like to just drop a couple of reports into - nothing fancy, basically just lists. The problem is that the main 'body' table contains foreign key values for Finish (colour) and Product, going back to source tables (tblProduct, tblFinish). For reporting purposes, clearly "FinishID" is going to be of limited use - I need to show FinishName from tblFinish.

Tables look like this, and are housed on a SQL Server (2008, r2).

tblCuttingListBody
------------------
CLBodyID
ProductID
FinishID
Quantity
etc

tblFinish
---------
FinishID
FinishName

tblProduct
----------
ProductID
ProductName

I've got all of these tables in a dataset, with relationships established. The tables in the dataset are DataTables with a related TableAdapter for each data table.

I've also created a TableAdapter/DataTable for the results of a stored proc that I wrote in the SQL Server Database I'm connecting to; sp_CuttingListReport. I figured it would make sense to create the data for the report there, then just point the report at this datatable. The SP needs a parameter passing to it (@CLID - the cutting list ID). This is tested; it works in SQL, and, if I go into the Designer for my DataSet XSD, I can right-click the data table, hit 'Preview Data', pass the parameter & get the correct rows of data back.

OK, so I now go create a Microsoft Report Item: call it CuttingListReport.rdlc. I go up to report data to add a new dataset, give it a name; ReportingFrustration, choose my 'main' dataset as the datasource, and then select 'sp_CuttingListReport' as the Available Dataset. I then see the correct fields in the report data tab. I go to the toolbox, add a table, and add a few fields to it from the dataset.

I create a new windows form, add the ReportViewer control to it, and choose the report I've just created as the report to show. As I do that, the Dataset, binding source and table adapter all appear at the bottom of the design window. Do a quick check of the code, and see:

Public Class Form2

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'IXI_CLDataSet.sp_CuttingListReport' table. You can move, or remove it, as needed.
        Me.sp_CuttingListReportTableAdapter.Fill(Me.IXI_CLDataSet.sp_CuttingListReport, 1)

        Me.ReportViewer1.RefreshReport()
    End Sub
End Class



There's an error with this; I need to pass the @CLID parameter to the Stored Proc, which I'm just going to hard-code to 1 for now, so:

Me.sp_CuttingListReportTableAdapter.Fill(Me.IXI_CLDataSet.sp_CuttingListReport, 1)

Change the start-up form to be the report viewer, build solution, start debug…… and, no data in the report viewer. Checking the output window, the last line of code (which, incidentally, doesn't make this crash!) is

A first chance exception of type 'System.Data.ConstraintException' occurred in System.Data.dll

I'm completely baffled by this - can anyone help? I'm not bothered if using a SQL stored proc is something I shouldn't have done, if there's a way to do this in the app, I will do.

Help!!!

Thanks

Lee