Stored Procedure Call

[rushtome]

Hi,

I am executing a stored procedure written in ORACLE from VB. My problem is when I am executing this procedure from back end (i.e. ORACLE), I am getting the desired output/results. But if the same procedure is executed from VB, then the output is not what is desired.

I am unable to understand where the code is going wrong.

The procedure declaration in ORACLE is as follows,

procedure process_itax_calculation
(
p_run_id in number,
p_company in char,
p_emp_no in varchar2,
p_fin_year in char,
p_it_salary_head_cd in char,
p_it_it_type_cd in char,
p_future_months in number,
p_for_year in number,
p_for_month in number,
p_adhoc_it in char,
p_it_form in char
)
is
---some code here

begin

---some code here

end;


Following is the VB call to the above procedure....

Dim cmd_it_cal as New ADODB.Command

With cmd_it_cal
.ActiveConnection = CommonModule.cnn
.CommandType = adCmdStoredProc
.CommandText = "process_itax_calculation"
.Parameters.Append .CreateParameter("RunNo", adInteger, adParamInput, 6, li_run_no)
.Parameters.Append .CreateParameter("CoCd", adChar, adParamInput, 2, GS_COMP_CD)
.Parameters.Append .CreateParameter("EmpNo", adVarChar, adParamInput, 12, rs_tt_process_emp("emp_no&quot.Value)
.Parameters.Append .CreateParameter("FinYr", adChar, adParamInput, 16, ls_fin_year)
.Parameters.Append .CreateParameter("TDSHead", adChar, adParamInput, 4, ls_tds_head_cd) '2006
.Parameters.Append .CreateParameter("Param1", adChar, adParamInput, 4, "9000&quot
.Parameters.Append .CreateParameter("FutureMonths", adInteger, adParamInput, 6, ln_future_months)
.Parameters.Append .CreateParameter("ForYear", adInteger, adParamInput, 6, li_for_yr)
.Parameters.Append .CreateParameter("ForMonth", adInteger, adParamInput, 6, li_for_month)
If ComboAdHocIT.Text = "No" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 1, "N&quot
ElseIf ComboAdHocIT.Text = "Yes" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 3, "Yes&quot
End If
.Parameters.Append .CreateParameter("Param3", adChar, adParamInput, 1, "N&quot
.Execute

End With

could anybody tell me where I am going wrong?

Thanks and Regards
rushtome

 

[CCLINT]

Have a look at this link for starters:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/NamedParameters.asp

 

[Razvi]

Hi rushtome
There is one abnormality I noticed in your code.
'Param2' has variable lengths (1 and 3) when ComboAdHocIT.Text = "No" and ComboAdHocIT.Text = "Yes"
I guess the correct code should read like,

If ComboAdHocIT.Text = "No" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 1, "N&quot

ElseIf ComboAdHocIT.Text = "Yes" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 1, "Y&quot

End If

I hope this helps. If not, please elaborate a little more on the problem you are facing. Pointers to any errors you are getting while trying to execute the stored procedure might help me to throw more light on the problem.

Cheers
Syed.

 

[rushtome]

hi, Razvi

If ComboAdHocIT.Text = "No" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 1, "N&quot
ElseIf ComboAdHocIT.Text = "Yes" Then
.Parameters.Append .CreateParameter("Param2", adChar, adParamInput, 3, "Yes&quot
End If


The above code that I have written, is correct. I want to pass "N" if the combovalue is "No" and "Yes" if it is "Yes". I am handling these values like this only in the procedure.

I am not quite sure whether the above written code is making some difference.

Thanks