Problem with dataadapter and stored procedure...

[lidds]

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:

 

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 can't 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:

 

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

[PlausiblyDamp]

You should be able to do something similar to the following.

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

[lidds]

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 can't seem to find anything.

 

Thanks

 

Simon

[IUnknown]

Hi,

You need to tell the DA that the CommandType is StoredProcedure, viz.,

[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.