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

Posted on 2012-09-04
Last Modified: 2012-09-04

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).




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)

    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.



Question by:deathshead75

    Accepted Solution

    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....

    Author Closing Comment

    Go me :-/

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now