Problem with dataadapter and stored procedure...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
Hi,

I am trying to create a datadapter using a stored procedure, however my stored procedure requires a value @project passed to it, I would normally acheive this like so:

Code: 
Dim myCmd As New OleDb.OleDbCommand("spQryLogin")
            myCmd.CommandType = CommandType.StoredProcedure
            myCmd.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = MRCSData.Instance.ProjectName

However I cant seem to find a way to pass this value to my stored procedure when creating a dataadapter, below is the code that I am using:

Code: 
Dim strReportName As String

        Dim myDB As New DBAccess
        Dim myReader As OleDb.OleDbDataReader = Nothing

        If myDB.Connect("MRCS") = False Then
            Exit Sub
        Else
            Dim myDA As New OleDb.OleDbDataAdapter("spQryComments", myDB.myConn)
            Create DataSet, fill it and view in data grid
            Dim myDS As New DataSet("SP")
            myDA.Fill(myDS, "SP")

            Pass the reportname to string variable 
            strReportName = "rptCommIndividual"

            Get the Report Location
            Dim strReportPath As String = Application.StartupPath & "\" & strReportName & ".rpt"

            Check file exists
            If Not IO.File.Exists(strReportPath) Then
                Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))
            End If

            Assign the datasource and set the properties for Report viewer
            Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
            rptDocument.Load(strReportPath)

            rptDocument.SetDataSource(myDS.Tables(0))
            rptViewer.ShowRefreshButton = False
            rptViewer.ShowCloseButton = False
            rptViewer.ShowGroupTreeButton = False
            rptViewer.ReportSource = rptDocument

            myDB.DisConnect()
        End If

Could someone please help me out.

Thanks in advance

Simon
 
You should be able to do something similar to the following.
Code: 
Dim strReportName As String

        Dim myDB As New DBAccess
        Dim myReader As OleDb.OleDbDataReader = Nothing

        If myDB.Connect("MRCS") = False Then
            Exit Sub
        Else
            Dim myDA As New OleDb.OleDbDataAdapter("spQryComments", myDB.myConn)


try adding this line
myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = MRCSData.Instance.ProjectName

            Create DataSet, fill it and view in data grid
            Dim myDS As New DataSet("SP")
            myDA.Fill(myDS, "SP")

            Pass the reportname to string variable 
            strReportName = "rptCommIndividual"

            Get the Report Location
            Dim strReportPath As String = Application.StartupPath & "\" & strReportName & ".rpt"

            Check file exists
            If Not IO.File.Exists(strReportPath) Then
                Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))
            End If

            Assign the datasource and set the properties for Report viewer
            Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
            rptDocument.Load(strReportPath)

            rptDocument.SetDataSource(myDS.Tables(0))
            rptViewer.ShowRefreshButton = False
            rptViewer.ShowCloseButton = False
            rptViewer.ShowGroupTreeButton = False
            rptViewer.ReportSource = rptDocument

            myDB.DisConnect()
        End If
 
Thanks for your help but unfortunately it gives me the following error:

Procedure spQryComments expects parameter @projName, which was not supplied.

On this line:

Dim myDA As New OleDb.OleDbDataAdapter("spQryComments", myDB.myConn)
Create DataSet, fill it and view in data grid
myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = MRCSData.Instance.ProjectName
Dim myDS As New DataSet("SP")
myDA.Fill(myDS, "SP")

Do you have any other ideas, I have been searching on the internet but cant seem to find anything.

Thanks

Simon
 
Hi,
You need to tell the DA that the CommandType is StoredProcedure, viz.,
Code: 
[B]myDA.SelectCommand.CommandType = CommandType.StoredProcedure[/B]
myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = MRCSData.Instance.ProjectName
Dim myDS As New DataSet("SP")
myDA.Fill(myDS, "SP")
I prefer to create and set up the OleDBCommand and then assign it to the SelectCommand of the DA.
 
Back
Top