ASP.NET with sql 2k

Posted on 2006-11-24
Medium Priority
Last Modified: 2010-04-06
I have a sql server stored procedure which returns a resultset and also has out params. I want to execute this SP from .Net. But I have a choice of using ExecuteReader method or ExecuteNonQuery method. If I execute with ExecuteReader method I get resultset only. If I execute with ExecuteNonQuery method, I get out param values.
Does anyone know how to execute SP only once and get resultset and out param values?

Your help as before is much appreciated.

Question by:Wayne29
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 43

Expert Comment

ID: 18006846
Hi Wayne29,

You can get the output parameters, however you can only do so after you have closed the datareader!

Tim Cottee
LVL 43

Expert Comment

ID: 18006855

If you really need them first, consider making the stored procedure return mutiple resultsets, using

Select @OutputParam1,@OutputParam2

Select ...... your existing statement

You can then use read the datareader for the first resultset to get the "output" parameters and then use datareader.nextresult() to advance the datareader to the second resultset for the other records.


Author Comment

ID: 18006859

Hi Tom,

Thanks for reply.

My Question is, I should be able to get RESULTSET (interms of reader / dataset anything is OK) along with OUT values from SP.

If possible, can i have pseudo code / code snippet.

LVL 43

Accepted Solution

TimCottee earned 2000 total points
ID: 18006869

        Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("A Connection String")
        Dim sc As SqlClient.SqlCommand = New SqlClient.SqlCommand
        Dim dr As SqlClient.SqlDataReader
        Dim result As String
        With sc
            .Connection = cn
            .CommandText = "Test"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("@Value", SqlDbType.Int).Direction = ParameterDirection.Output
            dr = .ExecuteReader()
            With dr
                While .Read
                    result &= .Item(1)
                End While
            End With
        End With

This snippet shows when the output parameter gets returned, you can see when you run it (replacing the connectionstring, sp name, parameter etc for your situation). That the msgbox comes up empty until the dataread has been closed.

As this was a quick snippet in vb.net, you may need to replace the msgbox calls or debug it to see this.


Author Comment

ID: 18006874
Thanks Tim. You are correct.

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question