Passing paramter to sub report
Hi,
I'm no expert in RDLC code, but it seems to me that this SQL statement in your subreport won't work:
sSQL = "Select * from tblTransDet where JobNo like
'@JobNo%'"
Because you have the parameter in quotes it will read it literally. So if your parameter is JobNo 265,
the query will look for any Job No that starts with @JobNo (literally) instead of any Job No that starts with 265.
You need to edit the SQL statement. You could do something like this:
sSQL = "Select * from tblTransDet where LEFT(JobNo, LEN(@JobNo)) = @JobNo
This assumes that JobNo is a string. If it's something else you'd need to do the conversion for the comparison.
Cheers,
MartinaMartina White
March 29th, 2012 3:48am
I got a RDLC Report with one sub report. But i fail to passing the parameter to the sub report. Error: Data retrieval fail.
Code snap:
Imports Microsoft.Reporting.WinForms
Imports System.Data.SqlClient
Public Class frmTracking
Dim sSQL As String
Dim sConn As SqlConnection
Dim ds As DataSet = New DataSet
Dim da As SqlDataAdapter
Dim sConnStr As String
Sub CreateDataSet()
sSQL = ""
sSQL = "Select * from tableOrder
sConnStr = sDBS_ConnectionString
sConn = New SqlConnection(sConnStr)
Try
sConn.Open()
da = New SqlDataAdapter(sSQL, sConnStr)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Fill(ds, "dsOrder")
Catch ex As Exception
MsgBox(ex.ToString)
MsgBox(ex.Message)
Finally
sConn.Close()
End Try
End Sub
Sub ReportBinding()
Try
rptJobTracking.Visible = True
rptJobTracking.Refresh()
rptJobTracking.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local
rptJobTracking.LocalReport.ReportPath = sRpt_Directory & "rptJobTracking.rdlc"
rptJobTracking.LocalReport.DataSources.Clear()
AddHandler rptJobTracking.LocalReport.SubreportProcessing, AddressOf SubreportProcessingEvent
rptJobTracking.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("dsOrder", ds.Tables(0)))
rptJobTracking.DocumentMapCollapsed = True
rptJobTracking.LocalReport.EnableExternalImages = True
rptJobTracking.Refresh()
rptJobTracking.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)
rptJobTracking.Refresh()
'Me.WindowState = FormWindowState.Maximized
rptJobTracking.BringToFront()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Sub SubreportProcessingEvent(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
sConnStr = sDBS_ConnectionString
sConn = New SqlConnection(sConnStr)
'MsgBox(e.Parameters("MasterJobNo").Values(0))
sSQL = "Select * from tblTransDet where JobNo like '@JobNo%'"
Try
sConn.Open()
da = New SqlDataAdapter(sSQL, sConnStr)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Fill(ds, "dsTrackDet")
Catch ex As Exception
MsgBox(ex.ToString)
MsgBox(ex.Message)
Finally
sConn.Close()
End Try
End Sub
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2012 12:32pm
Hi,
I'm no expert in RDLC code, but it seems to me that this SQL statement in your subreport won't work:
sSQL = "Select * from tblTransDet where JobNo like
'@JobNo%'"
Because you have the parameter in quotes it will read it literally. So if your parameter is JobNo 265,
the query will look for any Job No that starts with @JobNo (literally) instead of any Job No that starts with 265.
You need to edit the SQL statement. You could do something like this:
sSQL = "Select * from tblTransDet where LEFT(JobNo, LEN(@JobNo)) = @JobNo
This assumes that JobNo is a string. If it's something else you'd need to do the conversion for the comparison.
Cheers,
MartinaMartina White
April 8th, 2012 9:11pm