CoreLab.PostgreSql.PgSqlException

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
asaxena
Posts: 2
Joined: Wed 01 Feb 2006 14:49

CoreLab.PostgreSql.PgSqlException

Post by asaxena » Wed 01 Feb 2006 15:01

I working with PostgreSQL and ASP.NET. I've created a stored procedure in PostgreSQL and am trying to call it through my code, but its returning an error that the function is not found.

Anyone have an example of calling a stored procedure in PostgreSQL from ASP.NET(1.1 C#), or can help me figure out what I'm doing wrong?...

I'm using "PostgreSQLDirect .NET Standard for .NET Framework 1.x (version 2.40.13)"

Here is the Stored procedure I created.

Code: Select all

CREATE OR REPLACE FUNCTION "CMRC_SP_SetNewOrder"()
  RETURNS void AS
begin
SELECT COUNT( DISTINCT CMRC_Orders.OrderID ) AS scount, CMRC_OrderStatus.StatusName, CMRC_OrderStatus.StatusID FROM CMRC_Orders INNER JOIN CMRC_OrderStatus ON CMRC_Orders.OrderStatus = CMRC_OrderStatus.StatusID LEFT OUTER JOIN CMRC_OrderDetails ON CMRC_Orders.OrderID = CMRC_OrderDetails.OrderID WHERE ( CMRC_Orders.OrderStatus = 1 ) GROUP BY CMRC_OrderStatus.StatusName, CMRC_OrderStatus.StatusID;
End;
  LANGUAGE 'plpgsql';

Here is the code (ASP.NET 1.1 C#)

Code: Select all

public  PgSqlDataReader  setNewOrder()
{
    // Create Instance of Connection and Command Object
    PgSqlConnection pgSqlConnection1 = new PgSqlConnection( ConfigurationSettings.AppSettings["ConnectionString"] );

    pgSqlConnection1.Open ();

    CoreLab.PostgreSql.PgSqlCommand Cmd = new CoreLab.PostgreSql.PgSqlCommand( "CMRC_SP_SetNewOrder", pgSqlConnection1 );

    // Mark the Command as a SPROC
    Cmd.CommandType = CommandType.StoredProcedure;

    // Return the datareader result
    return Cmd.ExecuteReader( CommandBehavior.CloseConnection );
}
Here is the error that I'm getting:
CoreLab.PostgreSql.PgSqlException: function cmrc_sp_setneworder() does not exist...

Any help or direction would be a great help...
thanks

SecureGen
Devart Team
Posts: 133
Joined: Thu 08 Sep 2005 06:27

Post by SecureGen » Thu 02 Feb 2006 07:36

The error message tells that procedure can not be found. You need to check the following issues:
- Did you create the procedure in the default scheme(public)?
If no then you should use the procedure name like "SchemaName.ProcedureName" in PgSqlCommand.CommandText, or set the Initial Schema parameter in the connection string.

Post Reply