Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query run successfully in sql*plus but return 0 records affected in vb6

Re: query run successfully in sql*plus but return 0 records affected in vb6

From: skyloon <skyloon_at_gmail.com>
Date: 29 Nov 2006 17:12:17 -0800
Message-ID: <1164849137.211305.185680@h54g2000cwb.googlegroups.com>


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_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
(FWDINVOICE_HD.INVOICEDATE BETWEEN '30-NOV-2003' AND '30-NOV-2006')) Errors
DB Error Count 0
Connection Mode 0

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

>

> Suggestions:
> After the db.Execute sSQL statement, add:
> DEBUG.PRINT sSQL
> DEBUG.PRINT "Errors "; ERROR
> DEBUG.PRINT "DB Error Count "; db.Errors.Count
> DEBUG.PRINT "Connection Mode "; db.Mode
> STOP
>

> Check the Debug window. You may find that the connection that you
> attempted to open is opened in read-only mode, which will cause the
> INSERT statement to fail.
>

> Also, I see syntax like this in your SQL statement:
> "AND " & oLib.SQLRangeDate("FWDINVOICE_HD.InvoiceDate",
> Format(dtDateFrom, "dd-MMM-yyyy"), Format(dtDateTo, "dd-MMM-yyyy"))
>

> What is oLib.SQLRangeDate? Instead, why not specify
> "AND FWDINVOICE_HD.InvoiceDate BETWEEN " & Format(dtDateFrom,
> "dd-MMM-yyyy") & " AND " & Format(dtDateTo, "dd-MMM-yyyy")
>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Wed Nov 29 2006 - 19:12:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US