parameter stored procedure results

[impulse24]

Hi,

I have created an app where the user enters a project number, and a stored procedure on a sql server 2000 box is called, and results are returned. If I just run the stored procedure through Server explorer, and enter some parameters results are displayed in the output window. I cant figure out why the code I have created does not display any results. Below is the code:

dbCommand = New OdbcCommand
Dim prmSchoolName As OdbcParameter = New OdbcParameter("@sSchoolName", OdbcType.VarChar, 40, ParameterDirection.Output)
Dim prmReturnVal As OdbcParameter = New OdbcParameter("@RETURN_VALUE", OdbcType.Int, 4, ParameterDirection.ReturnValue)

With dbCommand
.Connection = dbConn
.CommandType = CommandType.StoredProcedure
.CommandText = "spSelectTeacher"
.Parameters.Add(prmReturnVal)
.Parameters.Add("@sRegion", OdbcType.Char).Value = "C"
.Parameters.Add("@iTeacherId", OdbcType.Int).Value = 950603
.Parameters.Add("@iFacultyId", OdbcType.Int, 4, ParameterDirection.Output)
.Parameters.Add(prmSchoolName)
End With
dbCommand.ExecuteReader()


Console.WriteLine(prmReturnVal.Value)

I am never getting a return value. I just need the results of the facultyid and school name. Please help

 

[PankajBanga]

First of all why aren't you using SqlClient for accessing SQL Server? It's optimized for accessing data from SQL Server.

The code below shows how to access output parameter. It makes use of SqlClient.

Imports System.Data.SqlClient

Dim arrParam As SqlParameter
Dim DA As New SqlDataAdapter
Dim DS As New DataSet

'Initialize parameter.
arrParam = New SqlParameter("@PlanID", SqlDbType.Int)
arrParam.Direction = ParameterDirection.Output

Try
DA.SelectCommand = New SqlCommand
With DA.SelectCommand
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "spGetPlanID"
.Parameters.Clear()
.Parameters.Add(arrParam)
End With

conn.Open()

DA.SelectCommand.ExecuteReader()

MessageBox.Show(arrParam.Value)

Catch ex As Exception
Throw ex
Finally
conn.Close()
End Try

http://home.iprimus.com.au/pankajbanga