Accessing Output Parameter Value

  • Thread starter Thread starter Christopher Weaver
  • Start date Start date
C

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
 
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.
 
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
 
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
 
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.
Click to expand...
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;
Click to expand...
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,

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 said:
You're using InterBase, right?
Click to expand...
How did you know?
As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.
Click to expand...
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;
Click to expand...
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.
Click to expand...
 
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?
Click to expand...

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.Data.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.


Octavio Hernandez said:
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 said:
You're using InterBase, right?
Click to expand...
How did you know?
As Nicholas says, in CommandText you only need to have the NAME of the
stored procedure.
Click to expand...
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;
Click to expand...
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.
Click to expand...
Click to expand...
 
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 said:
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET provider
supplied by Borland?
Click to expand...

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.Data.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.


Octavio Hernandez said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
¿Are you using the Odbc provider? ¿Why don't you use the ADO.NET
provider supplied by Borland?
Click to expand...

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.Data.Common.BdpType.Int32);
CreateNewTask.Parameters["NewTaskID"].Direction =
System.Data.ParameterDirection.Output;

Thanks,

Chris.


Octavio Hernandez said:
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" <[email protected]> escribió en el mensaje
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top