PgSqlDataSource UPDATE Command not working with stored proc

PgSqlDataSource UPDATE Command not working with stored proc

Postby postgresmohan » Thu 24 Sep 2009 03:31

the function that I have in the postgres database has the following signature...
-------------------
CREATE OR REPLACE FUNCTION su_employee(empname character varying, empcode character varying, empaddress character varying, employeeid integer)
RETURNS integer AS

------------------

I am using pgsqldatasource, by setting
------------
UpdateCommand="su_employee" UpdateCommandType="StoredProcedure"
-------------

but when I go to edit the row, it comes up with a weird error.

-----------------
function su_employee(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Devart.Data.PostgreSql.PgSqlException: function su_employee(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist

----------------

Observe..Observe... my function had only 4 parameters... WHY DOES THE ERROR MESSAGE SHOW that MY FUCTION has 8 parameters... I don't understand...!!!


-------
helper...
_____




CREATE OR REPLACE FUNCTION su_employee(empname character varying, empcode character varying, empaddress character varying, employeeid integer)
RETURNS integer AS
$BODY$

UPDATE employee set employeename=$1,employeecode=$2,employeeaddress=$3 WHERE employeeid=$4;

SELECT $4 as updatedemployeeid;

$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
-------------------



DataSourceID="PgSqlDataSource1" DataKeyNames="employeeid">

























Text='<%# HttpUtility.HtmlEncode(Convert.ToString(Eval("employeename"))) %>' /> Text='<%# HttpUtility.HtmlEncode(Convert.ToString(Eval("employeecode"))) %>' />



HoverCssClass="popupHover"
PopupControlID="PopupMenu"
PopupPosition="Left"
TargetControlID="Panel9"
PopDelay="25" />














Text='<%# Bind("employeeid") %>' Width="100" />

Text='<%# Bind("employeename") %>' Width="100" />

Text='<%# Bind("employeecode") %>' Width="150" />

Text='<%# Bind("employeeaddress") %>' Width="40" />



TargetControlID="Panel9"
PopupControlID="PopupMenu"
HoverCssClass="popupHover"
PopupPosition="Right"
OffsetX="10" />



CausesValidation="True" CommandName="Update" Text="Update" />


CausesValidation="False" CommandName="Cancel" Text="Cancel" />












------------------------------

ConnectionString="<%$ ConnectionStrings:TestHigherConnectionString %>"
DeleteCommand="sd_employee" DeleteCommandType="StoredProcedure"
InsertCommand="si_employee" InsertCommandType="StoredProcedure"
OldValuesParameterFormatString="Original_{0}"
ProviderName="<%$ ConnectionStrings:TestHigherConnectionString.ProviderName %>"
SelectCommand="sp_employee" SelectCommandType="StoredProcedure"
UpdateCommand="su_employee" UpdateCommandType="StoredProcedure"
CancelSelectOnNullParameter="False">




















postgresmohan
 
Posts: 4
Joined: Thu 24 Sep 2009 00:38

Solved it!

Postby postgresmohan » Thu 24 Sep 2009 22:45

PgSqlDataSource automatically understands what parameters need to be sent for the UPDATE Command, because I have used "Bind()", which is a two way data communicator.

When the UPDATE command is issued, the PgSqlDataSource automatically includes the primary key field as the original_{0}, so you may have to remove the primary key field by yourself....and how will you remove it??? Look at the following code.

Code: Select all
Protected Sub PgSqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles PgSqlDataSource1.Updating
        Dim indexParam As Integer = 0
        Dim isFound As Boolean = False

        'search for the problem additional parameter which is added automatically by VS
        For Each xP As Devart.Data.PostgreSql.PgSqlParameter In e.Command.Parameters
            'check if this is the trouble extra parameter
            If xP.ParameterName = "employeeid" Then
                'this is the problem parameter, set to found and exit the loop
                isFound = True

                Exit For
            End If
            'the param wasnt found so increment the index counter
            indexParam += 1
        Next

        'if the parameter was found, remove it from the parameters collection
        If isFound = True Then
            e.Command.Parameters.Remove(e.Command.Parameters(indexParam))

        End If
    End Sub


I may not have explained this very well. You are welcome to ask me any questions related to this.

Thank You.
postgresmohan
 
Posts: 4
Joined: Thu 24 Sep 2009 00:38

Postby Shalex » Mon 28 Sep 2009 14:54

Please give us the following information:
1) the exact text of the error you are getting at the moment;
2) the full call stack;
3) the version of your dotConnect for PostgreSQL. You can see it in the Tools | PostgreSQL | About menu of Visual Studio;
4) please send us ( http://www.devart.com/company/contact.html ) a small test project with the DDL/DML script to reproduce the problem. We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL