Reading Return Val from SP Called from VB.NET?

[Justin Cooke]

Hi All, I'm calling a SQL2000 SP from my VB.NET app with the following code: Dim sqlCmd As SqlCommand sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn) sqlCmd.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlCmd) adapter.Fill(tableReturned) This SP performs a SELECT that causes the tableReturned (a DataTable) to be filled. The SP also returns an INT (0 or 1, depending on whether an error occurred). The SP code is basically this (much simplified for this question): CREATE PROCEDURE dbo.JHU_SAS_IDOC_Process AS BEGIN SELECT * FROM JHU_SAS_IDOC RETURN 1 -- or 0, depending on an error condition END My question is: How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table. Any ideas? Thanks! Justin

[Colin Angus Mackay]

Justin Cooke wrote:

How do I read that return value in the app? I think I could read it using the SqlCommand.ExecuteScalar() func, but that wouldn't work to fill my table.

And it wouldn't work to get the value because ExecuteScalar just retrieves the value in the first column of the first row in the result set and discards anything else. You need to add a SqlParameter[^] to your SqlCommand object to get the ReturnValue. For example:

Dim sqlCmd As SqlCommand
sqlCmd = New SqlCommand("JHU_SAS_IDOC_Process", conn)
sqlCmd.CommandType = CommandType.StoredProcedure
Dim parameter As New SqlParameter("ReturnValue", SqlDbType.Int, 4)
parameter.Direction = ParameterDirection.ReturnValue
sqlCmd.Parameters.Add(parameter)

After you've got the data you can then check the value of the parameter with parameter.Value DISCLAIMER: I typed that off the top of my head, and I'm also not a VB.NET developer, so there may be errors

---

Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos