passing a date parameter to a PostgreSQL function from VB.NET

[my_lou]

ok, this is aggrevating. i have the following PostgreSQL function:

 

CREATE OR REPLACE FUNCTION public."MANIPULATEORDERDATAINFO_FUNCTION"(int4, int4, float4, int4, text, int4, text, text, date, text)

RETURNS bpchar AS

'DECLARE

argsItemID ALIAS FOR $1;

argsCopiesOrdered ALIAS FOR $2;

argsCost ALIAS FOR $3;

argsStatusID ALIAS FOR $4;

argsComments ALIAS FOR $5;

argsSupplierID ALIAS FOR $6;

argsRefNum ALIAS FOR $7;

argsInvNum ALIAS FOR $8;

argsDateOrdered ALIAS FOR $9;

argsActionSelector ALIAS FOR $10;

varIncomingOrderID Integer;

BEGIN

IF argsActionSelector = \'EDITITEM\' THEN

SELECT IncomingOrderID

INTO varIncomingOrderID

FROM ORDERITEM

WHERE OrderItemID = argsItemID;

 

UPDATE ORDERITEM

SET CopiesOrdered = argsCopiesOrdered,

Cost = argsCost,

OrderItemStatusID = argsStatusID,

Comments = argsComments

WHERE OrderItemID = argsItemID;

 

UPDATE INCOMINGORDER

SET SupplierID = argsSupplierID,

ReferenceNumber = argsRefNum,

InvoiceNumber = argsInvNum,

DateOrdered = argsDateOrdered...............

...................................................

 

 

which i know works fine because when i call it from CygWin it works just fine. The following is my CygWin output:

 

=> select public."MANIPULATEORDERDATAINFO_FUNCTION"(421, 34, 3.4, 1, 'tes

t', 10, '44', '44', '2004-12-09', 'EDITITEM');

MANIPULATEORDERDATAINFO_FUNCTION

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

 

(1 row)

 

 

However when i try to call it from VB.NET i get an error:

 

'ERROR [HY000] ERROR: function public.ManipulateOrderDataInfo_Function(integer, integer, real, integer, "unknown", integer, "unknown", "unknown", date, "unknown") does not exist'

 

Here is my VB.NET code:

 

 

pgFunctionName = "SELECT public." & Chr(34) & "ManipulateOrderDataInfo_Function" & Chr(34) & "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"

commonConnection = New OdbcConnection

commonConnection.ConnectionString = DBConnectString

 

Try

commonConnection.Open()

Catch Ex As Exception

MsgBox("Failed to connect to PostgreSQL database from CommonFunctions.vb/ManipulateOrderItemInfo.")

End Try 'End Try

 

commonOdbcFunctionCommand = New OdbcCommand

commonOdbcFunctionCommand.Connection = commonConnection

commonOdbcFunctionCommand.CommandType = CommandType.StoredProcedure

commonOdbcFunctionCommand.CommandText = pgFunctionName

 

inputItemID = New OdbcParameter

With inputItemID

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Int

.ParameterName = ""

.Value = argsItemID

End With

 

inputCopiesOrdered = New OdbcParameter

With inputCopiesOrdered

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Int

.ParameterName = ""

.Value = argsCopiesOrdered

End With

 

inputCost = New OdbcParameter

With inputCost

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Real

.ParameterName = ""

.Value = argsCost

End With

 

inputStatusID = New OdbcParameter

With inputStatusID

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Int

.ParameterName = ""

.Value = argsStatusID

End With

 

inputComments = New OdbcParameter

With inputComments

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Text

.ParameterName = ""

.Value = argsComments

End With

 

inputSupplierID = New OdbcParameter

With inputSupplierID

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Int

.ParameterName = ""

.Value = argsSupplierID

End With

 

inputRefNum = New OdbcParameter

With inputRefNum

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Text

.ParameterName = ""

.Value = argsRefNum

End With

 

inputInvNum = New OdbcParameter

With inputInvNum

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Text

.ParameterName = ""

.Value = argsInvNum

End With

 

inputDateOrdered = New OdbcParameter

With inputDateOrdered

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Date

.ParameterName = ""

.Value = argsDateOrdered

End With

 

inputActionSelector = New OdbcParameter

With inputActionSelector

.Direction = ParameterDirection.Input

.OdbcType = OdbcType.Text

.ParameterName = ""

.Value = argsActionSelector

End With

 

commonOdbcFunctionCommand.Parameters.Add(inputItemID)

commonOdbcFunctionCommand.Parameters.Add(inputCopiesOrdered)

commonOdbcFunctionCommand.Parameters.Add(inputCost)

commonOdbcFunctionCommand.Parameters.Add(inputStatusID)

commonOdbcFunctionCommand.Parameters.Add(inputComments)

commonOdbcFunctionCommand.Parameters.Add(inputSupplierID)

commonOdbcFunctionCommand.Parameters.Add(inputRefNum)

commonOdbcFunctionCommand.Parameters.Add(inputInvNum)

commonOdbcFunctionCommand.Parameters.Add(inputDateOrdered)

commonOdbcFunctionCommand.Parameters.Add(inputActionSelector)

 

Try

commonOdbcFunctionReader = commonOdbcFunctionCommand.ExecuteReader(CommandBehavior.Default)

Catch Ex As Exception

MsgBox(Ex.Message)

End Try

 

I am pretty sure that the problem is caused by the DATE parameter inputDateOrdered. I just can't figure out how to pass a date parameter to the PostgreSQL function.

 

Any ideas?

 

Thanks much.