Assign value to a variable using output value from SQL stored procedure on ODBC cmd

[iaw]

Hi,

I'm trying to learn how to assign a value to a variable in vb.net (VS2010) using the return from a stored procedure.

I can easily run and pass values to my stored procedures, but I can't find a clear and concise example on how to assign a returned value.

create procedure test
(
@X int,
@Y int OUTPUT
)
as

begin

@y = @x + 1
end

In VB.Net

public sub test()
dim cnn_ODBCLogin as Odbc.OdbcConnection
dim str_ODBCCommand as Odbc.OdbcCommand

cnn_ODBCLogin = New Odbc.OdbcConnection(ConnString)
cnn_ODBCLogin.Open()

str_ODBCCommand.Connection = cnn_ODBCLogin
str_ODBCCommand.CommandText = "text '1'"
str_ODBCCommandType = CommandType.StoreProcedure
str_ODBCCommand.ExecuteNonQuery

...

This all works fine, I've added a record insert into the stored procedure to ensure it runs (it does), but I have no idea how to assign the output value of y to a variable in the VB.Net procedure. Can someone explain how to do this?

Thanks,

iaw

 

[cjard]

Please use CODE tags in future when posting code

'why are your variable names preceeded with str? it's not a string?
str_ODBCCommand.Connection = cnn_ODBCLogin
str_ODBCCommand.CommandText = "test"
str_ODBCCommandType = CommandType.StoreProcedure
str_ODBCCommandType = CommandType.StoreProcedure
[COLOR="magenta"]str_ODBCCommand.Parameters.AddWithValue("@x", 1)
str_ODBCCommand.Parameters.AddWithValue("@y", 0) 'dummy value
str_ODBCCommand.Parameters("@y").Direction = ParameterDIrection.Output [/COLOR]
str_ODBCCommand.ExecuteNonQuery

[COLOR="magenta"]MessageBox.Show("output is " & str_ODBCCommand.Parameters("@y").Value)[/COLOR]