pgsqlparameter date type problem

[serkan]

Table and Function

Code: Select all

CREATE TABLE users
(
  id serial NOT NULL,
  name varchar(50),
  surname varchar(50),
  Tarih date
) 
WITHOUT OIDS;
ALTER TABLE users OWNER TO postgres;

CREATE TYPE spUser_Type AS
(
id int4
);


CREATE OR REPLACE FUNCTION spUser(_Name varchar(50),_SurName varchar(50),_Tarih date)
 RETURNS SETOF spUser_Type AS $$
Declare
r spUser_Type%rowtype; 
BEGIN
	INSERT INTO Users
             (Name, SurName,Tarih)
	VALUES     (_Name,_SurName,_Tarih);
for r in select Max(ID) from Users loop
return next r;
end loop;
return;

END;
$$ LANGUAGE PLPGSQL;

VB Code

Code: Select all

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connection As New PgSqlConnection("Host=localhost;Port=5432;User=postgres;Password=XXXX;Database=xxxx;protocol=2;")
        Dim newParameters(2) As CoreLab.PostgreSql.PgSqlParameter
        Dim command As PgSqlCommand
        command = connection.CreateCommand
        command.CommandText = "spuser"
        connection.Open()
        newParameters(0) = New PgSqlParameter
        newParameters(0).ParameterName = "@Name"
        newParameters(0).PgSqlType = PgSqlType.VarChar
        newParameters(0).Value = "Valuename"

        newParameters(1) = New PgSqlParameter
        newParameters(1).ParameterName = "@Surname"
        newParameters(1).PgSqlType = PgSqlType.VarChar
        newParameters(1).Value = "ValueSurname"
        newParameters(2) = New PgSqlParameter

        newParameters(2).ParameterName = "@Tarih"
        newParameters(2).PgSqlType = PgSqlType.Date
        newParameters(2).Value = Date.Now.ToShortDateString
        Dim i As Integer
        For Each parameter As PgSqlParameter In newParameters
            command.Parameters.Add(newParameters(i))
            i += 1
        Next
        command.CommandType = CommandType.StoredProcedure
        command.ExecuteNonQuery()
    End Sub

Error :

Code: Select all

An unhandled exception of type 'CoreLab.PostgreSql.PgSqlException' occurred in corelab.postgresql.dll

Additional information: ERROR:  syntax error at or near ":" at character 22

I have tried version 2.0.2.0 and 2.10.3.0
but this error occured

I have changed column named Tarih type to timestamp

but same error occured again

[Yuri]

Thank you for your sample.
We reproduced your problem and fixed it. This fix will be included in the next PostgreSQLDirect .NET build.