Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data Adapter & Stored Procedures with Crystal Reports

Status
Not open for further replies.

Bluejay07

Programmer
Mar 9, 2007
780
CA
Hello,

I am working with VS 2005 and SQL Server 2005.

I am using a data adapter's selectcommand to connect to a stored procedure. I then fill a dataset and then set a crystal report data source with the dataset.

This works great if I name the dataset table name the exact same name at the source table and I am only using one table. Here is my code:

Code:
strConn = rptCONN.ConnectionString
CONN = New SqlConnection(strConn)
CONN.Open()

m_da = New SqlDataAdapter
m_da.SelectCommand = New SqlCommand

With m_da.SelectCommand
   .Connection = rptCONN
   .CommandType = CommandType.StoredProcedure
   .CommandText = "rptCustomer"

   .Parameters.Add("RefNum", SqlDbType.VarChar, 50)
   .Parameters.Item("RefNum").Value = Trim(g_strStartRef)
End With

m_DataSet = New DataSet
m_da.Fill(m_DataSet, "cusform"

If m_DataSet.Tables("cusform").Rows.Count = 0 Then
   MessageBox.Show(...)
Else
   Dim rpt As New ModelWorksheet
   rpt.SetDataSource(m_DataSet)
   CRViewer.ReportSource = rpt
End If

SQL Stored Procedure:
Code:
SELECT * from cusform WHERE...

My problem is when the stored procedure uses more than one table and is connected using an inner join.

Code:
FROM cusform INNER JOIN cusarts ON cusform.RefNum = cusarts.REF_NO

The CrystalReportsViewer fails as there is more than one table name.

How can I overcome this? Any help would be greatly appreciated.
 
In your Crystal Report design, did you link the tables? Use the Database Wizard and look at your link. I just added 2 tables to a crystal report design and it displays the report just fine. looks like it would work in vb.
 
Thanks for the reply PRPhx.

In Crystal Reports Design, if the data sources tab is clicked, then I right click and "Add New Data Source" which brings up the Data Source Configuration Wizard. I select Database, Select the connection string, then selected the two tables and linked them together with the Reference Number Field.
 
Is the stored procedure returning the results of 2 queries? If not then you'll only have one table returned. If you have any functions or formulas in the multitable query the problem could be an invalid/unnamed column.

Try putting the part of the code in an error trap to see what exception is getting thrown. Something like this:

Code:
Try
   Dim rpt As New ModelWorksheet
   rpt.SetDataSource(m_DataSet)
   CRViewer.ReportSource = rpt

Catch ex As DataSourceException
   messagebox.show(ex.message)
   console.writeline(ex.stacktrace)
Catch ex As EngineException
   messagebox.show(ex.message)
   console.writeline(ex.stacktrace)
Catch ex As Exception
   messagebox.show(ex.message)
   console.writeline(ex.stacktrace)
End Try

If you get the last trap you may have to put in a breakpoint and dig through a few inner exceptions.

 
The stored procedure returns the result of one query which links two tables.

There should not be any invalid columns as the query was formed by creating a view and selecting the appropriate columns from each table.

With that being said, I have saved the view and my stored procedure now references the view. Since a view represents a single call, similar to a query on one table, I am able to reference a view instead of a query to two tables. This works just as well.

Thanks for the replies.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top