Question Query Multiple tables for a crystal report

[Garjahan]

Hello,
I am a newb here and somewhat new to .net.

The scenario:

I have multiple sql tables, I would like to create a crystal report based on JobNum. The data will come from a Job Table and a run table. It needs to query on a parameter for the report.
here is the code "

code :

Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String

connectionString = "Data Source=sqlexpress;Initial Catalog=RH;user = xxxx;password = xxxxx"
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = "SELECT JobNum, Location; SELECT JobNum, Run, FROM tblRun where JobNum = @JobNum"

Dim JobNum As Integer = Form1.intJob 'comes from the main form

Dim dscmd As New SqlDataAdapter(sql, cnn)
Dim ds As New DataSet
dscmd.Fill(ds, "Run")
MsgBox(ds.Tables(1).Rows.Count)
cnn.Close()

Dim objRpt As New ReportDocument
objRpt.SetDataSource(ds.Tables(1))
rptViewer.ReportSource = objRpt
rptViewer.Refresh()

end code

it breaks on the
dscmd.Fill(ds, "Run")
looking for the scalar variable.

If someone could point me in the right direction I would greatly appreciate it.

Thanks

 

[jmcilhinney]

Your SQL code contains a @JobNum parameter but you haven't set its value anywhere.

dscmd.SelectCommand.Parameters.AddWithValue("@JobNum", JobNum)

For more info on ADO.NET parameters, follow the Blog link in my signature and check out my post on the subject.