Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query run successfully in sql*plus but return 0 records affected in vb6
i've tried using both different SQL statement that you suggested, it
gives the same statement in immediate window, when i run this query in
sql*plus, it returns result.
here is the result from immediate window:
INSERT INTO FWDINVOICE_DET_at_ORACLEAR
(COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)
(SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE,
FWDINVOICE_DET.INVOICENO,FWDINVOICE_DET.JOBNO, FWDINVOICE_DET.CHARGECODE FROM FWDINVOICE_DET_at_ORACLEDB,FWDINVOICE_HD_at_ORACLEDBWHERE FWDINVOICE_HD.COMPANYCODE = FWDINVOICE_DET.COMPANYCODE AND
FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y') AND(FWDINVOICE_HD.INVOICEDATE BETWEEN '30-NOV-2003' AND '30-NOV-2006')) Errors
Charles Hooper wrote:
> skyloon wrote:
> > I've did a program using vb6 to connect to oracle9i, i can establish
> > the connection, the problem is when i execute the query in oracle
> > sql*plus, it can execute successfully, but when run in vb application,
> > the records affected return 0, it do nothing for this query, any
> > setting need to be done?
> > because this query insert and select to/from different database, i've
> > created a database link for these 2 database, everything work find in
> > oracle sql*plus.
> >
> > thanks for help..
> >
> >
> > db.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;USER
> > ID=max;PASSWORD=max;Data Source=oracledb"
> >
> > sSQL = "INSERT INTO FWDINVOICE_DET_at_ORACLEAR
> > (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)" & _
> > " (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE," & _
> > "
> > FWDINVOICE_DET.InvoiceNo,FWDINVOICE_DET.JobNo,FWDINVOICE_DET.ChargeCode"
> > & _
> > " FROM FWDINVOICE_DET_at_ORACLEDB,FWDINVOICE_HD_at_ORACLEDB" & _
> > " Where FWDINVOICE_HD.CompanyCode = FWDINVOICE_DET.CompanyCode" & _
> > " AND FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE" & _
> > " AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO" & _
> > " AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y') AND "
> > sSQL = sSQL & oLib.SQLRangeDate("FWDINVOICE_HD.InvoiceDate",
> > Format(dtDateFrom, "dd-MMM-yyyy"), Format(dtDateTo, "dd-MMM-yyyy"))
> > sSQL = sSQL & ")"
> >
> > sSQL = UCase(sSQL)
> > db.Execute sSQL
>
>
>
>
>