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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 29 Nov 2006 04:50:42 -0800
Message-ID: <1164804642.802424.44030@l39g2000cwd.googlegroups.com>

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 - 06:50:42 CST