| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
skyloon wrote:
> 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.
> 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:
>
Do you have two database links set up in the database, or is the "@ORACLEDB" referring to the same database name as the "Data Source=oracledb" in the connection string? Is the "@ORACLEAR" a database SID name or a database link name? If ORACLEAR is a database link name, is the database link set up with read only permissions, is there a hard coded user ID specified for the database link, or does it depend on if the user "max" exists in the database pointed to by the ORACLEAR database link?
How many rows are returned if you create an ADO recordset, and specify just the SELECT portion of your SQL statement? 0 or more than 0? Do you issue a db.BeginTrans before attempting to execute the above statement?
If you absolutely cannot resolve the issue, it is possible to use VB code to open a connection to each database, select the data from the first database, and insert the data row by row into the second database. This could potentially be very slow if there are a large number of rows.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Nov 30 2006 - 06:49:46 CST
 |
 |