I'm attempting my first stored procedure.

PROCEDURE "GetCountryName" (IN "intCountryID" INTEGER, OUT "strName" VARCHAR COLLATE UNI)
BEGIN
 DECLARE crCountry CURSOR FOR stmtQuery;
 DECLARE strResult VARCHAR DEFAULT '';
 PREPARE stmtQuery FROM 'SELECT Name FROM Countries WHERE CountryID = ?';
 OPEN crCountry USING intCountryID;
 IF (ROWCOUNT(crCountry) > 0) THEN
   FETCH FIRST FROM crCountry (Name) INTO strResult;
 END IF;
 CLOSE crCountry;                                  
 SET strName = strResult;
END

It works when I test it in ElevateDB Manager, but doesn't seem to be receiving the first
input parameter when I try to access it from VS2005.

' Create database connection
Dim MyConnection As New EDBConnection(ConnectionString)
' Open connection
MyConnection.Open()
' Create command and point at stored procedure
Dim MyCommand As New EDBCommand("GetCountryName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
' Input parameter
Dim intParamNumber As Integer
intParamNumber = MyCommand.Parameters.Add(New EDBParameter("intCountryID", DbType.Int32))
MyCommand.Parameters(intParamNumber).Value = 2
MyCommand.Parameters(intParamNumber).Direction = ParameterDirection.Input
' Output parameter
intParamNumber = MyCommand.Parameters.Add(New EDBParameter("strName", DbType.String))
MyCommand.Parameters(intParamNumber).Direction = ParameterDirection.Output
' Call the stored procedure
MyCommand.ExecuteNonQuery()
' Get output parameter
Dim strResult As String = Convert.ToString(MyCommand.Parameters(intParamNumber).Value)

Any suggestions?

I found the answer in another thread. It looks like you call MyCommand.Prepare() instead
of creating and configuring the parameters yourself. Then it's just a matter of filling in
the value of any input parameters.

Ya beat me to it.

Glad you found the reference in the newsgroup.

Lance



"Hilary Blokker" <hblokker@sigmaassessmentsystems.com> wrote in message
news:544C1C36-2E5E-4A41-B9D4-1FED008CB02A@news.elevatesoft.com...
>I found the answer in another thread. It looks like you call
>MyCommand.Prepare() instead
> of creating and configuring the parameters yourself. Then it's just a
> matter of filling in
> the value of any input parameters.
>

Hilary,

<< I found the answer in another thread. It looks like you call
MyCommand.Prepare() instead of creating and configuring the parameters
yourself. Then it's just a matter of filling in the value of any input
parameters. >>

Yep, sorry for the confusion.  Stored procedures and scripts always rely on
the source procedure or script to get the parameter names, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com