Accessing Output Parameter Value

[Christopher Weaver]

I'm having trouble accessing the value of an output parameter of a stored
procedure. The SP looks like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NEW_TASK
RETURNS (
"uidTask" INTEGER)
AS
begin
INSERT INTO "tblTasks" ( "Description", "AddDate", "DueDate", "Status")
VALUES('New task', 'now', 'now', 'Open');

SELECT MAX("uidTask")
FROM "tblTasks"
INTO :"uidTask";

UPDATE "tblTasks"
SET "AssignToSeq" = - :"uidTask"
WHERE "uidTask" = :"uidTask";

suspend;
end
^

SET TERM ; ^

GRANT SELECT,INSERT,UPDATE ON "tblTasks" TO PROCEDURE SP_NEW_TASK;

GRANT EXECUTE ON PROCEDURE SP_NEW_TASK TO SYSDBA;

And I'm running it like this:

CreateNewTask.Connection = odbcIB;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "EXECUTE PROCEDURE \"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID", OdbcType.Int);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

CreateNewTask.Connection.Open();
CreateNewTask.ExecuteNonQuery();
CreateNewTask.Connection.Close();

Any ideas on this? I can't find even a single example of accessing the
value of an output parameter when running a stored procedure within MSDN.
Pointing me toward one in C# would be great!

Thanks

[Nicholas Paldino [.NET/C# MVP]]

Christopher,

First, you should set the command text of the command to just the name
of the stored procedure. You don't need to do anything else in that regard.

The other thing that you want to do is set the Direction to
ParameterDirection.ReturnValue. Once you do this and run the stored
procedure, you should be able to access the parameter's value through the
Value property on the parameter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Christopher Weaver" <we*****@nospamverizon.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

I'm having trouble accessing the value of an output parameter of a stored
procedure. The SP looks like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NEW_TASK
RETURNS (
"uidTask" INTEGER)
AS
begin
INSERT INTO "tblTasks" ( "Description", "AddDate", "DueDate", "Status")
VALUES('New task', 'now', 'now', 'Open');

SELECT MAX("uidTask")
FROM "tblTasks"
INTO :"uidTask";

UPDATE "tblTasks"
SET "AssignToSeq" = - :"uidTask"
WHERE "uidTask" = :"uidTask";

suspend;
end
^

SET TERM ; ^

GRANT SELECT,INSERT,UPDATE ON "tblTasks" TO PROCEDURE SP_NEW_TASK;

GRANT EXECUTE ON PROCEDURE SP_NEW_TASK TO SYSDBA;

And I'm running it like this:

CreateNewTask.Connection = odbcIB;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "EXECUTE PROCEDURE \"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID", OdbcType.Int);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

CreateNewTask.Connection.Open();
CreateNewTask.ExecuteNonQuery();
CreateNewTask.Connection.Close();

Any ideas on this? I can't find even a single example of accessing the
value of an output parameter when running a stored procedure within MSDN.
Pointing me toward one in C# would be great!

Thanks

[Octavio Hernandez]

Hi, Christopher,

For obtaining the value of an output parameter, you can use the same syntax
that for setting it:

cmd.Parameters["Input"].Value = 25;
cmd.ExecuteNonQuery();
int result = (int) cmd.Parameters["Output"].Value;

In your case, this should work:

int taskID = (int) CreateNewTask.Parameters["NewTaskID"];

You're using InterBase, right?

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:%2****************@tk2msftngp13.phx.gbl...

I'm having trouble accessing the value of an output parameter of a stored
procedure. The SP looks like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NEW_TASK
RETURNS (
"uidTask" INTEGER)
AS
begin
INSERT INTO "tblTasks" ( "Description", "AddDate", "DueDate", "Status")
VALUES('New task', 'now', 'now', 'Open');

SELECT MAX("uidTask")
FROM "tblTasks"
INTO :"uidTask";

UPDATE "tblTasks"
SET "AssignToSeq" = - :"uidTask"
WHERE "uidTask" = :"uidTask";

suspend;
end
^

SET TERM ; ^

GRANT SELECT,INSERT,UPDATE ON "tblTasks" TO PROCEDURE SP_NEW_TASK;

GRANT EXECUTE ON PROCEDURE SP_NEW_TASK TO SYSDBA;

And I'm running it like this:

CreateNewTask.Connection = odbcIB;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "EXECUTE PROCEDURE \"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID", OdbcType.Int);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

CreateNewTask.Connection.Open();
CreateNewTask.ExecuteNonQuery();
CreateNewTask.Connection.Close();

Any ideas on this? I can't find even a single example of accessing the
value of an output parameter when running a stored procedure within MSDN.
Pointing me toward one in C# would be great!

Thanks

[Octavio Hernandez]

Sorry, I missed '.Value' in my last post:

int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;

As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.

Regards - Octavio

[Christopher Weaver]

> You're using InterBase, right?
How did you know?

As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure. That's never worked for me. I think the problem is in the odbc driver that
comes with InterBase.
int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;

This compiles but throws "Specified cast is not valid." What's more, this
line:

DataType = CreateNewTask.Parameters["NewTaskID"].OdbcType.ToString();

returns "Int" in my catch message:

catch (Exception ex)
{
MessText = ex.Message + "\n" + ParamValue + "\n" + DataType;
MessageBox.Show(MessText);
}

ParamValue comes from

ParamValue = CreateNewTask.Parameters["NewTaskID"].Value.ToString();

And it's always null. I'm concerned about this last point. Seems it
shouldn't be null. The back end is creating a new record each time.

Thanks for your help. Yours and Nicholas. Any more ideas would be great.

[Octavio Hernandez]

Christopher,

I knew it was InterBase for the SET TERM statement.
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:Om**************@TK2MSFTNGP12.phx.gbl...

You're using InterBase, right?

How did you know?

As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.

That's never worked for me. I think the problem is in the odbc driver
that comes with InterBase.

int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;

This compiles but throws "Specified cast is not valid." What's more, this
line:

DataType = CreateNewTask.Parameters["NewTaskID"].OdbcType.ToString();

returns "Int" in my catch message:

catch (Exception ex)
{
MessText = ex.Message + "\n" + ParamValue + "\n" + DataType;
MessageBox.Show(MessText);
}

ParamValue comes from

ParamValue = CreateNewTask.Parameters["NewTaskID"].Value.ToString();

And it's always null. I'm concerned about this last point. Seems it
shouldn't be null. The back end is creating a new record each time.

Thanks for your help. Yours and Nicholas. Any more ideas would be great.

[Christopher Weaver]

> ¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider

supplied by Borland?
Voila!

Doesn't say much for the odbc driver, does it. I will be rewriting my
entire app to removed all uses of the odbc driver to avoid any other
problems that haven't surfaced yet.

It turns out like this: (with some exception catching left out)

BdpConnection Conn = GetBdpConnection();
BdpCommand CreateNewTask = new BdpCommand();
CreateNewTask.Connection = Conn;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "\"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID",Borland.D ata.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.
"Octavio Hernandez" <do****@danysoft.com> wrote in message
news:Oj*************@TK2MSFTNGP12.phx.gbl... Christopher,

I knew it was InterBase for the SET TERM statement.
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:Om**************@TK2MSFTNGP12.phx.gbl...

You're using InterBase, right?

How did you know?

As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.

That's never worked for me. I think the problem is in the odbc driver
that comes with InterBase.

int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;

This compiles but throws "Specified cast is not valid." What's more,
this line:

DataType = CreateNewTask.Parameters["NewTaskID"].OdbcType.ToString();

returns "Int" in my catch message:

catch (Exception ex)
{
MessText = ex.Message + "\n" + ParamValue + "\n" + DataType;
MessageBox.Show(MessText);
}

ParamValue comes from

ParamValue = CreateNewTask.Parameters["NewTaskID"].Value.ToString();

And it's always null. I'm concerned about this last point. Seems it
shouldn't be null. The back end is creating a new record each time.

Thanks for your help. Yours and Nicholas. Any more ideas would be
great.

[Octavio Hernandez]

Christopher,

If you're using Borland Delphi 2005 I seriously recommend you to use the
BDP.NET Provider... I think it's the BEST way with difference to access
InterBase from .NET applications. I've tried it and it works quite well.
Also know of several customers using it without problems.

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:OA****************@TK2MSFTNGP10.phx.gbl...

¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?

Voila!

Doesn't say much for the odbc driver, does it. I will be rewriting my
entire app to removed all uses of the odbc driver to avoid any other
problems that haven't surfaced yet.

It turns out like this: (with some exception catching left out)

BdpConnection Conn = GetBdpConnection();
BdpCommand CreateNewTask = new BdpCommand();
CreateNewTask.Connection = Conn;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "\"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID",Borland.D ata.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.
"Octavio Hernandez" <do****@danysoft.com> wrote in message
news:Oj*************@TK2MSFTNGP12.phx.gbl...

Christopher,

I knew it was InterBase for the SET TERM statement.
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:Om**************@TK2MSFTNGP12.phx.gbl...

You're using InterBase, right?
How did you know?

As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.
That's never worked for me. I think the problem is in the odbc driver
that comes with InterBase.

int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;
This compiles but throws "Specified cast is not valid." What's more,
this line:

DataType = CreateNewTask.Parameters["NewTaskID"].OdbcType.ToString();

returns "Int" in my catch message:

catch (Exception ex)
{
MessText = ex.Message + "\n" + ParamValue + "\n" + DataType;
MessageBox.Show(MessText);
}

ParamValue comes from

ParamValue = CreateNewTask.Parameters["NewTaskID"].Value.ToString();

And it's always null. I'm concerned about this last point. Seems it
shouldn't be null. The back end is creating a new record each time.

Thanks for your help. Yours and Nicholas. Any more ideas would be
great.

[Christopher Weaver]

I am unfortunately writing in VS 2003. It's not all that bad really, but
I've been with Delphi since D1 and taught it up through D6. But my client
wanted VS so I'm learning VS.

Thanks again.
"Octavio Hernandez" <do****@danysoft.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Christopher,

If you're using Borland Delphi 2005 I seriously recommend you to use the
BDP.NET Provider... I think it's the BEST way with difference to access
InterBase from .NET applications. I've tried it and it works quite well.
Also know of several customers using it without problems.

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:OA****************@TK2MSFTNGP10.phx.gbl...

¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET
provider supplied by Borland?

Voila!

Doesn't say much for the odbc driver, does it. I will be rewriting my
entire app to removed all uses of the odbc driver to avoid any other
problems that haven't surfaced yet.

It turns out like this: (with some exception catching left out)

BdpConnection Conn = GetBdpConnection();
BdpCommand CreateNewTask = new BdpCommand();
CreateNewTask.Connection = Conn;
CreateNewTask.CommandType = CommandType.StoredProcedure;
CreateNewTask.CommandText = "\"SP_NEW_TASK\"";
CreateNewTask.Parameters.Add("NewTaskID",Borland.D ata.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.
"Octavio Hernandez" <do****@danysoft.com> wrote in message
news:Oj*************@TK2MSFTNGP12.phx.gbl...

Christopher,

I knew it was InterBase for the SET TERM statement.
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET
provider supplied by Borland?

Regards - Octavio

"Christopher Weaver" <we*****@nospamverizon.net> escribió en el mensaje
news:Om**************@TK2MSFTNGP12.phx.gbl...
> You're using InterBase, right?
How did you know?

>As Nicholas says, in CommandText you only need to have the NAME of the
>stored procedure.
That's never worked for me. I think the problem is in the odbc driver
that comes with InterBase.

>int taskID = (int) CreateNewTask.Parameters["NewTaskID"].Value;
This compiles but throws "Specified cast is not valid." What's more,
this line:

DataType = CreateNewTask.Parameters["NewTaskID"].OdbcType.ToString();

returns "Int" in my catch message:

catch (Exception ex)
{
MessText = ex.Message + "\n" + ParamValue + "\n" + DataType;
MessageBox.Show(MessText);
}

ParamValue comes from

ParamValue = CreateNewTask.Parameters["NewTaskID"].Value.ToString();

And it's always null. I'm concerned about this last point. Seems it
shouldn't be null. The back end is creating a new record each time.

Thanks for your help. Yours and Nicholas. Any more ideas would be
great.