?
Solved

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

Posted on 2012-09-04
2
Medium Priority
?
1,775 Views
Last Modified: 2012-09-04
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
Comment
Question by:deathshead75
  • 2
2 Comments
 

Accepted Solution

by:
deathshead75 earned 0 total points
ID: 38366002
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
 

Author Closing Comment

by:deathshead75
ID: 38366003
Go me :-/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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