I am trying to send a table as a parameter in a postgresql function, and in visual basic I get the following error:
"There is no assignment of object type System.Data.DataTable to a native type of a known managed provider."
this is the postgresql code:
CREATE OR REPLACE FUNCTION public.sp_adm_artefacto(
V_SERIE character varying DEFAULT NULL::character varying,
V_TIPO_ARTEFACTO integer DEFAULT NULL::integer,
V_MODELO character varying DEFAULT NULL::character varying,
V_MARCA integer DEFAULT NULL::integer,
V_DETALLE TYPE_DETALLE_AC DEFAULT NULL,
v_usuario integer DEFAULT NULL::integer,
V_ID_ARTEFACTO integer DEFAULT NULL::integer,
v_tipo_operacion character DEFAULT NULL::bpchar)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
VL_ID_ARTEFACTO INTEGER;
BEGIN
IF V_TIPO_OPERACION = 'I' THEN
INSERT INTO DETALLE_AC(ID_DETALLE_AC,ID_ARTEFACTO,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,
USUARIO_CREACION,FECHA_CREACION)
SELECT 1,1,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE;
END IF;
END;
$BODY$;
this is the code in vb:
Sub ADMINISTRAR_ARTEFACTO(ByVal P As E_ARTEFACTO)
Dim CONECTION As New OdbcConnection
Try
CONECTION.ConnectionString = ConfigurationManager.ConnectionStrings("ALURHE_DBConnectionString_64").ConnectionString + ";pwd=2007211050"
Dim COMMAND As OdbcCommand = New OdbcCommand("{call SP_ADM_ARTEFACTO (?,?,?,?,?,?,?,?)}", CONECTION)
With COMMAND
.Parameters.AddWithValue("V_SERIE", P.P_SERIE)
.Parameters.AddWithValue("V_TIPO_ARTEFACTO", P.P_TIPO_ARTEFACTO)
.Parameters.AddWithValue("V_MODELO", P.P_MODELO)
.Parameters.AddWithValue("V_MARCA", P.P_MARCA)
.Parameters.AddWithValue("V_DETALLE", P.P_DETALLE)
.Parameters.AddWithValue("V_USUARIO", P.P_USUARIO)
.Parameters.AddWithValue("V_ID_ARTEFACTO", If(P.P_TIPO_OPERACION = "M", P.P_ID_ARTEFACTO, DBNull.Value))
.Parameters.AddWithValue("V_TIPO_OPERACION", P.P_TIPO_OPERACION)
End With
'If P.P_TIPO_OPERACION = "M" Then
' COMMAND.Parameters.AddWithValue("@ID_PRODUCTO", P.P_ID_PRODUCTO)
'End If
CONECTION.Open()
COMMAND.ExecuteNonQuery()
CONECTION.Close()
Catch ex As Exception
If (CONECTION.State = ConnectionState.Open) Then
CONECTION.Close()
End If
Throw ex
End Try
End Sub
I tried to try calling only the function as follows:
SELECT sp_adm_artefacto('1321321',1,'F-14',1,(1,10,5,50),1,NULL,'I')
and I get the error:
ERROR: no existe la relación «v_detalle»
LINE 3: ...NTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE
^
QUERY: INSERT INTO DETALLE_AC(ID_DETALLE_AC,ID_ARTEFACTO,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,
USUARIO_CREACION,FECHA_CREACION)
SELECT 1,1,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE
CONTEXT: función PL/pgSQL sp_adm_artefacto(character varying,integer,character varying,integer,type_detalle_ac,integer,integer,character) en la línea 9 en sentencia SQL
SQL state: 42P01