Pass stored procedure return recordset to dataset?

[slybitz]

How can I take my stored procedure, execute it, and put the recordset from the stored procedure into a dataset? I tried the below way but I am having no luck. I'm passing in my procedures parameters via another form (fAppResearch). I ultimately want to use this created dataset as a crystal report source. Any help would be greatly appreciated. Thanks!!

        Dim dsDataSet As New DataSet()
        Dim _cmdSqlCommand As New SqlCommand()
        Dim _adpAdapter As New SqlDataAdapter()
        Dim rpt As New rResearch() 'my crystal report

        With _cmdSqlCommand
            .Connection = New SqlClient.SqlConnection("Server=blah blah blah connection info;")
            'Declare command type
            .CommandType = CommandType.StoredProcedure

            'Declare what procedure to execute
            .CommandText = "sPAP_ResearchReport"

            'Define parameter name used in actual procedure and what size/type it is
            .Parameters.Add(New SqlClient.SqlParameter("@i_postcompanyid", Data.SqlDbType.Char, 3))
            'Set procedure's parameter value
            .Parameters.Item("@i_postcompanyid").Value = fAppResearch.cmbCompany.SelectedValue

            'Define parameter name #2 used in actual procedure and what size/type it is
            .Parameters.Add(New SqlClient.SqlParameter("@i_BatchNo", Data.SqlDbType.VarChar, 20))
            'Set procedure's parameter #2 value
            .Parameters.Item("@i_BatchNo").Value = fAppResearch.txtBatchNo.Text
        End With

        ' Configure Adapter to use newly created command object and fill the dataset.
        _adpAdapter.SelectCommand = _cmdSqlCommand
        _adpAdapter.Fill(dsDataSet)
        rpt.SetDataSource(dsDataSet)
        CrystalReportViewer1.ReportSource = rpt

 

[jebenson]

Have you successfully executed the stored proc in some other environment, such as Query Analyser? Have you verified that there are data that match the parameters you are passing? Have you verified that the SqlCommand's parameters are being assigned correctly?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[ThatRickGuy]

That looks reasonable to me also. Double check your dsDataSet object and make sure it is populated. See if there is a table in the .tables collection and make sure it has rows.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[bboffin]

It looks pretty much correct but the way you have set up the parameters is unnecessarily complex and can be simplified. This reduces the chance of making an error.

Try below if using .NET Framework 1.1

            .Parameters.Add("@i_postcompanyid", fAppResearch.cmbCompany.SelectedValue)

            .Parameters.Add("@i_BatchNo", fAppResearch.txtBatchNo.Text)

or this if using .NET Framework 2.0

            .Parameters.AddWithValue("@i_postcompanyid", fAppResearch.cmbCompany.SelectedValue)

            .Parameters.AddWithValue("@i_BatchNo", fAppResearch.txtBatchNo.Text)

You should also surround the Fill with a Try..Catch block so you can see any errors.

    Try
        _adpAdapter.Fill(dsDataSet)
    Catch ex As Exception
        MessageBox.Show("Error: " & ex.message)
    End Try

Bob Boffin

 

[slybitz]

Weird. I'm not sure what the deal is. The table in the .tables collection has rows. Running the proc in query analyzer I get a record set. I eventually just used a different method and got it to work but it still frustrates me that I couldn't get that method to work. I could have quite possibly not been passing it to my cystal report correctly. Thanks for all of your help.