SQL & PL/SQL

from VB6 code

is anyway to modify of the IF statement that can check date datatype is/was better. Or how to pass the date datatype?

IF (invoiceDate_in != null) THEN
Above statement can't check the value invoiceDate_in is null or not.


Set cmdObj = New ADODB.Command
With cmdObj
.ActiveConnection = connObj
.CommandType = adCmdStoredProc
.CommandText = "invoicepackage.updateinvoiceproc"
.Parameters.Append .CreateParameter("invoiceId_in", adInteger, adParamInput, , invoice_id)
.Parameters.Append .CreateParameter("comId_in", adInteger, adParamInput, , 0)
.Parameters.Append .CreateParameter("branchId_in", adInteger, adParamInput, , branchId_in)
.Parameters.Append .CreateParameter("invoiceOldNum_in", adVarChar, adParamInput, Len(invoiceOldNum_in), invoiceOldNum_in)
.Parameters.Append .CreateParameter("invoiceDate_in", adDate, adParamInput, , invoiceDate_in)
.Parameters.Append .CreateParameter("invoicePoNum_in", adVarChar, adParamInput, Len(invoicePoNum_in), invoicePoNum_in)

.Parameters.Append .CreateParameter("invoiceTotal_in", adInteger, adParamInput, , invoiceTotal_in)
.Parameters.Append .CreateParameter("transportId_in", adInteger, adParamInput, , transportId_in)
.Parameters.Append .CreateParameter("paid_in", adVarChar, adParamInput, Len("") + 1, "")
.Parameters.Append .CreateParameter("cheque_cash_in", adVarChar, adParamInput, Len("") + 1, "")
.Parameters.Append .CreateParameter("remark", adVarChar, adParamInput, Len("") + 1, "")
.Parameters.Append .CreateParameter("varout", adVarChar, adParamOutput, 255, "")
End With

cmdObj.Execute
varout = cmdObj.Parameters("varout")

From pl/sql
procedure updateinvoiceproc
( invoiceId_in in number
, comId_in in number
, branchId_in in number
, invoiceOldNum_in in varchar2
, invoiceDate_in in date
, invoicePoNum_in in varchar2
, invoiceTotal_in in number
, transportId_in in number
, paid_in in varchar2
, cheque_cash_in in varchar2
, remark_in in varchar2
, varout out varchar2
) as
begin
null;
varout := '';
IF (comId_in != 0) THEN
dbms_output.PUT_LINE('comId_in' || comId_in);
varout := varout || 'comId_in : ' || comId_in || ', ' ;
update invoice set com_id = comId_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;
IF (branchId_in != 0) THEN
dbms_output.PUT_LINE('branchId_in' || branchId_in);
varout := varout || 'branchId_in : ' || branchId_in || ', ' ;
update invoice set branch_id = branchId_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;
IF (length(invoiceOldNum_in) != 0) THEN
dbms_output.PUT_LINE('invoiceOldNum_in' || invoiceOldNum_in);
varout := varout || 'invoiceOldNum_in : ' || invoiceOldNum_in || ', ' ;
update invoice set invoice_old_num = invoiceOldNum_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;

--- ---- NOT WORK CHECK BY invoiceDate_in != null
-- IF (invoiceDate_in != null) THEN
-- dbms_output.PUT_LINE('invoiceDate_in' || invoiceDate_in);
-- varout := varout || 'invoiceDate_in : ' || invoiceDate_in || ', ' ;
update invoice set invoice_date = invoiceDate_in where tid=invoiceId_in;
-- varout := varout+1;
-- END IF;
IF (length(invoicePoNum_in) != 0) THEN
dbms_output.PUT_LINE('invoicePoNum_in' || invoicePoNum_in);
varout := varout || 'invoicePoNum_in : ' || invoicePoNum_in || ', ' ;
update invoice set invoice_po_num = invoicePoNum_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;
IF (invoiceTotal_in != 0) THEN
dbms_output.PUT_LINE('invoiceTotal_in' || invoiceTotal_in);
varout := varout || 'invoiceTotal_in : ' || invoiceTotal_in || ', ' ;
update invoice set invoice_total = invoiceTotal_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;
IF (transportId_in != 0) THEN
dbms_output.PUT_LINE('transportId_in' || transportId_in);
varout := varout || 'transportId_in : ' || transportId_in || ', ' ;
update invoice set transport_id = transportId_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;

IF (paid_in = '0' or paid_in = '1') THEN
-- dbms_output.PUT_LINE('paid_in' || paid_in);
-- varout := varout || 'paid_in : ' || paid_in || ', ' ;
update invoice set paid = paid_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;

IF (cheque_cash_in = 'CHEQUE' or cheque_cash_in = 'CASH') THEN
dbms_output.PUT_LINE('cheque_cash' || cheque_cash_in);
varout := varout || 'cheque_cash : ' || cheque_cash_in || ', ' ;
update invoice set cheque_cash = cheque_cash_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;

IF (remark_in = '1' or remark_in = '0') THEN
dbms_output.PUT_LINE('remark_in' || remark_in);
varout := varout || 'remark_in : ' || remark_in || ', ' ;
update invoice set remark = remark_in where tid=invoiceId_in;
-- varout := varout+1;
END IF;

commit;
end updateinvoiceproc;

Thanks a lot