Solved

ASP.NET with sql 2k

Posted on 2006-11-24
5
211 Views
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.

Regards
Wayne
0
Comment
Question by:Wayne29
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 18006846
Hi Wayne29,

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

Tim Cottee
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 18006855
Wayne29,

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.
       

Tim
0
 

Author Comment

by:Wayne29
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.

Thanks
Wayne
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 18006869
Wayne29,

        Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("A Connection String")
        cn.Open()
        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()
            MsgBox(.Parameters("@Value").Value)
            With dr
                While .Read
                    result &= .Item(1)
                End While
                MsgBox(sc.Parameters("@Value").Value)
                .Close()
                MsgBox(sc.Parameters("@Value").Value)
            End With
        End With
        MsgBox(result)

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.

Tim
0
 

Author Comment

by:Wayne29
ID: 18006874
Thanks Tim. You are correct.
Wayne
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to count occurrences of each item in an array.

789 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