Stored Procedure Date Parameter Conversion

[ckpelton]

Hi everyone,

I'm having some trouble and I was unable to find any answers after hours of
searching. I'm calling an oracle stored procedure that validates a part code,
passing in parameter values and it returns some values based on the
parameters passed in. The code below runs without error, however when running
the code in Access I get different results from when I execute the stored
procedure in SQL Nav. The Access results are incorrect and I believe it is
because of the date conversion between Access and Oracle. Oracle requires a
format of '30-May-2009' and Access seems to be converting it to 5/30/2009
before passing.

My question is, is there any way to pass the date parameter without Access
converting the date to their standard format? I've tried formatting, changing
the parameter type within Access and passing it as a string. Nothing seems to
be working. Any suggestions or insight would be greatly appreciated.

My code is below, along with the results and then the desired results are
posted below that.

Access VBA:

Option Compare Database
Sub stored_procedure()
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim agreement_number_in, service_date_in, part_code_in, dealer_part_cost_in,
freon_yn_in, quantity_in, _
markup_amount_out, markup_multiplier_out, fixed_dollar_amount_out,
max_dollar_amount_out, error_code_out, _
error_message_out As String
Dim PR As New ADODB.Parameter

Set objConn = New ADODB.Connection
objConn.Open "dsn=db", "user", "pass"

Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn

agreement_number_in = 2671939
service_date_in = "30-May-2009"
part_code_in = "asdfaasdf"
dealer_part_cost_in = 45.54
freon_yn_in = "'N'"
quantity_in = 1

'Set up and execute a call to the server to execute the stored procedure and
get the result.
objCmd.CommandText = "part_markup_proc"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Refresh

objCmd.Parameters("agreement_number_in") = agreement_number_in
objCmd.Parameters("service_date_in") = service_date_in
objCmd.Parameters("part_code_in") = part_code_in
objCmd.Parameters("dealer_part_cost_in") = dealer_part_cost_in
objCmd.Parameters("freon_yn_in") = freon_yn_in
objCmd.Parameters("quantity_in") = quantity_in

objCmd.Execute , , adExecuteNoRecords

'Get the procedures return code from the command object.
markup_amount_out = objCmd.Parameters("markup_amount_out")
markup_multiplier_out = objCmd.Parameters("markup_multiplier_out")
fixed_dollar_amount_out = objCmd.Parameters("fixed_dollar_amount_out")
max_dollar_amount_out = objCmd.Parameters("max_dollar_amount_out")
error_code_out = objCmd.Parameters("error_code_out")
If objCmd.Parameters("error_message_out") <> Null Then
error_message_out = objCmd.Parameters("error_message_out")
End If

Dim p As ADODB.Parameter
For Each p In objCmd.Parameters
Debug.Print p.Name & ": " & p.Value
Next

End Sub

=====================================================

Results:

AGREEMENT_NUMBER_IN: 2671939
SERVICE_DATE_IN: 5/30/2009
PART_CODE_IN: asdfaasdf
DEALER_PART_COST_IN: 45.54
FREON_YN_IN: 'N'
QUANTITY_IN: 1
MARKUP_AMOUNT_OUT: 90
MARKUP_MULTIPLIER_OUT: 2
FIXED_DOLLAR_AMOUNT_OUT: 0
MAX_DOLLAR_AMOUNT_OUT: 99999
ERROR_CODE_OUT:
ERROR_MESSAGE_OUT:

=====================================================

SQL Nav procedure call:

DECLARE
markup_amount_out NUMBER;
markup_multiplier_out NUMBER;
fixed_dollar_amount_out NUMBER;
max_dollar_amount_out NUMBER;
error_code_out NUMBER;
error_message_out VARCHAR2 (512);
BEGIN
part_markup_proc (2671939,
'08-May-2009',
'DMC6549',
40.79,
'N',
1,
markup_amount_out,
markup_multiplier_out,
fixed_dollar_amount_out,
max_dollar_amount_out,
error_code_out,
error_message_out
);
DBMS_OUTPUT.put_line ('Markup Amount: ' || TO_CHAR (markup_amount_out));
DBMS_OUTPUT.put_line ( 'Markup Multiplier: '
|| TO_CHAR (markup_multiplier_out)
);
DBMS_OUTPUT.put_line ( 'Fixed Dollar Amt: '
|| TO_CHAR (fixed_dollar_amount_out)
);
DBMS_OUTPUT.put_line ('Max Dolalr Amt: ' || TO_CHAR (max_dollar_amount_out)
);
END;

=====================================================

Correct results desired in Access:

Markup Amount: 10
Markup Multiplier: 1
Fixed Dollar Amt: 0
Max Dolalr Amt: 10

 

[AccessVandal via AccessMonster.com]

Not sure it works, you can try to format the date something like..

1. service_date_in = "#" & "30-May-2009" & "#"
or
2. service_date_in = "#30-May-2009#"
or
3. objCmd.Parameters("service_date_in") = "#" & service_date_in & "#"

or maybe just format the output with "Format(Whatever,"dd-mmm-yyyy")"

I don't know your Oracle table for the field is like for it's datatype, so
it's just a stab in the dark.

You might also want to try out by setting your OS Regional Setting in the
controlpanel to this format "dd/mmm/yyyy".

HTH

Hi everyone,

I'm having some trouble and I was unable to find any answers after hours of
searching. I'm calling an oracle stored procedure that validates a part code,
passing in parameter values and it returns some values based on the
parameters passed in. The code below runs without error, however when running
the code in Access I get different results from when I execute the stored
procedure in SQL Nav. The Access results are incorrect and I believe it is
because of the date conversion between Access and Oracle. Oracle requires a
format of '30-May-2009' and Access seems to be converting it to 5/30/2009
before passing.

snip..

 

[ckpelton via AccessMonster.com]

Thanks for the quick reply, I tried formatting yesterday evening to no avail,
so I did as you suggested and changed the OS Regional Setting to the proper
date format. Great idea, it never crossed my mind to do this!

This corrected the format, but it's still producing the same result. The
stored procedure is expecting a date value for this parameter and in Oracle
the date fields need single quotes around them. It's not allowing me to put
single quotes around the date in Access and pass it via the parameter. I
tried changing the parameter type to varchar so Access would accept the
quotes around the date string, but I'm getting an inconsistent data types
error when it tries to execute.

Not sure it works, you can try to format the date something like..

1. service_date_in = "#" & "30-May-2009" & "#"
or
2. service_date_in = "#30-May-2009#"
or
3. objCmd.Parameters("service_date_in") = "#" & service_date_in & "#"

or maybe just format the output with "Format(Whatever,"dd-mmm-yyyy")"

I don't know your Oracle table for the field is like for it's datatype, so
it's just a stab in the dark.

You might also want to try out by setting your OS Regional Setting in the
controlpanel to this format "dd/mmm/yyyy".

HTH

Hi everyone,

[quoted text clipped - 7 lines]

format of '30-May-2009' and Access seems to be converting it to 5/30/2009
before passing.

snip..

 

[AccessVandal via AccessMonster.com]

String should be fine, an alternative suggestion would be using Oracle
function like "to_date('SomeDate','DD-MON-YYYY HH24:MI:SS')" or to_char().

1. service_date_in = "to_date('30-May-2009','DD-MON-YYYY HH24:MI:SS')â€

or

2. service_date_in = "to_Char('30-May-2009')"

or

3. objCmd.Parameters("service_date_in") = "to_date('30-May-2009','DD-MON-YYYY
HH24:MI:SS')"

or

4. objCmd.Parameters("service_date_in") = "to_char('30-May-2009')"

or

5 . "Dim service_date_in As Date" on a single line instead. Use
service_date_in = "#30-May-2009#". Try it out.

It’s been a long time since I use Oracle, so far in my case a single quote
does the job. If not the Oracle function "to_date()" do just fine.

 

[ckpelton via AccessMonster.com]

We discovered, with the help of IT, the year portion of the date was being
truncated to '09' rather than '2009' and I could not for the life of me
figure out how to get around it without changing the stored procedure to do a
useless date check. So I rewrote the SP in Access and it's working fine.
Thanks for all the help and suggesions.

String should be fine, an alternative suggestion would be using Oracle
function like "to_date('SomeDate','DD-MON-YYYY HH24:MI:SS')" or to_char().

1. service_date_in = "to_date('30-May-2009','DD-MON-YYYY HH24:MI:SS')â€

or

2. service_date_in = "to_Char('30-May-2009')"

or

3. objCmd.Parameters("service_date_in") = "to_date('30-May-2009','DD-MON-YYYY
HH24:MI:SS')"

or

4. objCmd.Parameters("service_date_in") = "to_char('30-May-2009')"

or

5 . "Dim service_date_in As Date" on a single line instead. Use
service_date_in = "#30-May-2009#". Try it out.

It’s been a long time since I use Oracle, so far in my case a single quote
does the job. If not the Oracle function "to_date()" do just fine.

Thanks for the quick reply, I tried formatting yesterday evening to no avail,
so I did as you suggested and changed the OS Regional Setting to the proper

[quoted text clipped - 7 lines]

quotes around the date string, but I'm getting an inconsistent data types
error when it tries to execute.