Retrieving multiple values from stored procedure: parameter and recordset

Hello,

The problem below appears to be a very common problem, but despite having tried every solution I can find offered online, the solution continues to elude me. Any tips you could offer would bemuchappreciated.

Problem: I'm successfully retrieving a recordset from a SQL Server 2000 stored procedure, but don't know how to retrieve an Output Parameter as well.

Components:

a simple Web Form with a Text Box (txtOutput) and a GridView (dgOutput). (Visual Web Developer Express 2005)
a simple stored procedure which - when executed in the Studio Output window - returns the correct output.Running [dbo].[usp_Test] ( @.Region = <NULL>, @.StartDate = <NULL> ).

State Total
-------- ----
ACT 27
NSW 26
NT 6
QLD 20
SA 44
TAS 17
VIC 28
WA 48
No rows affected.
(8 row(s) returned)
@.StartDate = 27/11/2006
@.RETURN_VALUE = 0
Finished running [dbo].[usp_Test].
the code behind the form, to call the stored proc and bind the grid to the output of the stored proc. 'Already declared and populated strConn and strSelect
Dim conn As New SqlConnection(strConn)
Dim storedProc As New SqlCommand
With storedProc
.CommandType = CommandType.StoredProcedure
.CommandText = strSelect
.Connection = conn
End With
Dim returnParam As SqlParameter
returnParam = New SqlParameter("@.StartDate", SqlDbType.DateTime, 30)
returnParam.Direction = ParameterDirection.Output
storedProc.Parameters.Add(returnParam)

Dim dr As SqlClient.SqlDataReader
conn.Open()
dr = storedProc.ExecuteReader
dgOutput.DataSource = dr
dgOutput.DataBind()
dr.Close()
conn.Close()

Situation:

the GridView successfully displays the 8-row recordsetI don't know how to display the Output Parameter ("@.StartDate") in the Text Box. Without success I've tried variations on:
txtOutput.Text = storedProc.Parameters("@.StartDate").Value.ToStringtxtOutput.Text = returnParam.Value.ToStringusing dr.NextResultIf possible, I would like to be able to display the 'number of row(s) returned', which is already being returned (as printed above).

Any assistance would be sincerely appreciated - it's been a long day of trying. Thank you for your time.

- Sarah

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

Hi Dinakar,

Thank you for looking at my problem Unfortunately the same problem is still happening.

The stored proc is already returning the output parameter with the correct value of27/11/2006(as shown in the output in my first post), but any time I try to retrieve it, it seems to have a null value.

In my VB code, I've changed my parameter creation to match your suggested code.

When I use the new DateTime variable as you suggest, and set it tostoredProc.Parameters("@.StartDate").Value, it's value is set to 1/01/0001 12:00:00 AM .

Any suggestions?

Thank you again for your time.

|||Can you post your stored proc code?|||

CREATE PROCEDURE usp_Test(
@.Region AS VARCHAR(8) = NULL, --Optional
@.StartDate DATETIME OUTPUT
)
AS

BEGIN
SET @.StartDate =DATEADD(Week, DATEDIFF(Week,0,GETDATE()), 0) --Monday of current week

SELECT Loc.LocationState AS State,
COUNT(Appt.ApartmentID) AS Total
FROM Apartment AS Appt
LEFT JOIN ReportLocation AS Loc ON Appt.LocationID = Loc.LocationID
WHERE (( Appt.InsertedOn >= @.StartDate) AND
(Appt.InsertedOn <= GETDATE()) AND
((@.Region IS NULL) OR (Loc.LocationState LIKE @.Region)) AND
(Appt.isBreak = 0) AND
(Appt.isBlock = 0) AND
(Appt.isCancelled = 0)
)
GROUP BY Loc.LocationState
ORDER BY Loc.LocationState
END
GO

|||Is there anything else it might be? I'm happy to post any of my code, which might expose the cause.|||

Allright, after a long long time, I wrote up a quick windows app. I used a datagridview and called a stored proc which returns a result set through a SELECT statement. The stored proc also has an OUTPUT parameter. I was able to retrieve both.

alter proc dbo.test_dk @.fileidint, @.rowcountint output as begin select fileid, orderid, creationdatefrom servicefilewhere fileid = @.fileidselect @.rowcount =@.@.rowcountend

And the code for the windows app is:

Dim objconAs SqlConnection objcon =New SqlConnection("server=...")Try If objcon.State = 0Then objcon.Open()Dim dsAs New DataSetDim daAs SqlDataAdapter =New SqlDataAdapter da.SelectCommand =New SqlCommand da.SelectCommand.CommandType = CommandType.StoredProcedure da.SelectCommand.CommandText ="test_dk"Dim sqlparamAs New SqlParameter("@.fileid", SqlDbType.Int) sqlparam.Value = 7035833Dim sqlparam2As New SqlParameter("@.rowcount", SqlDbType.Int) sqlparam2.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(sqlparam) da.SelectCommand.Parameters.Add(sqlparam2) da.SelectCommand.Connection = objcon da.Fill(ds,"servicefile") DataGridView1.DataSource = ds DataGridView1.DataMember ="servicefile" TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").ValueCatch excAs Exception MsgBox(exc)Finally If objcon.State = ConnectionState.OpenThen objcon.Close()End If'objCon.Dispose()End Try

|||

That worked beautifully - you're a champion, Dinakar! Some day they'll sing songs about you.

Thank you very much for your time and help.

|||You are welcome.|||

ndinakar:

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

AS

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

I'm trying to follow your example but on this line..

Your line

TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").Value

My line

lbl_ERROR.Text = da.SelectCommand.Parameters("@.RETURNError").Value;

I get a build error.. Error 1 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

sql