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:
SQL Stored Procedure:
My problem is when the stored procedure uses more than one table and is connected using an inner join.
The CrystalReportsViewer fails as there is more than one table name.
How can I overcome this? Any help would be greatly appreciated.
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.