• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1839
  • Last Modified:

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
0
deathshead75
Asked:
deathshead75
  • 2
1 Solution
 
deathshead75Author Commented:
I've solved this myself. I moved the code out of the Form_Open event and onto a button which had to be pushed, so I got more meaningful information about the error.

I basically just had to temporarily turn off enforced constraints on the dataset. Not that there were any constraints actually *in* the dataset, but this seems to have done the trick....
0
 
deathshead75Author Commented:
Go me :-/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now