SQL output info

hi there.

I basically want, whatever output SQL gives, to be placed in a paramater in Vb.NET

is it possible?

i have a storeproc in SQL which does a query based on the ID (which is the input param sent from Vb.NET), after that, the result given in SQL, i wish for that value (result) to be given back to VB.NET in a variable.

how do i do that?

this is what i have for now:

Code:

---

        Dim mySQLCommand1 As New SqlClient.SqlCommand("getGeneralOwnerID", sqlconn)
        sqlconn.Open()

        mySQLCommand.CommandText = "getGeneralOwnerID"
        mySQLCommand.CommandType = CommandType.StoredProcedure
        Dim genid As New SqlClient.SqlParameter("@mainID", SqlDbType.Int)

        genid.Value = CInt(lblID.Text)
        genid.Direction = ParameterDirection.Input
        mySQLCommand.Parameters.Add(genid)
        sqlconn.Close()

---

in SQL, what would the syntax be for telling it to OUTPUT the result back to VB.NET?

Is it just 1 value you want to return? If so you can do it along these lines....

VB Code:

...
objParam = objCommand.Parameters.Add("ReturnValue", SqlDbType.Int)
objParam.Direction = ParameterDirection.ReturnValue
...
objCommand.ExecuteNonQuery()
intBlah = objCommand.Parameters("ReturnValue").Value

Code:

---

SQL
-- return your value at end of proc
return @myValue

---

Or as i prefer to use the returnValue for error checking you can use OUTPUT parameters instead like this

VB Code:

objParam = objCommand.Parameters.Add("@player_id", SqlDbType.Int, 0)
objParam.Direction = ParameterDirection.Output
objCommand.ExecuteNonQuery()
lngPLayerID = objCommand.Parameters("@player_id").Value

Code:

---

SQL
-- declare parameter in stored proc header
@player_id int OUTPUT
-- and then something like
select @player_id = @@identity
--or
set @player_id = 3

---

Hope that helps you out.