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

[skyloon]

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@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@ORACLEDB,FWDINVOICE_HD@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
 

[Killer42]

I don't know the cause of your problem, but if those dates are going into date/time fields in Access, I think you need to put them in m/d/yyyy format rather than dd-mm-yyyy, and put hashes (#) around them.

You might also need a semicolon on the end of the query.

[skyloon]

i've tried using to_char, to_date, trunc, the problem still can't solve..
may i know what is the correct date format?

[Killer42]

i've tried using to_char, to_date, trunc, the problem still can't solve..
may i know what is the correct date format?

Generally, in SQL (for Access, at least) the format for a date is American format with hashes around it. Taking today as an example, that would be #11/30/2006#.

Can you tell us what oLib.SQLRangeDa te() does? Or better still, post a copy of what's in sSQL immediately before issuing the Execute.

[skyloon]

here is the result from immediate window.
run this query in sql*plus, it returns result, but it returns 0 records affected in when i try to debug it..

INSERT INTO FWDINVOICE_DET@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@ORACLEDB,FWDINVOICE_HD@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'))
 

[skyloon]

here is the coding for your reference

coding in VB

dtDateFrom = dtpDate1.Value
dtDateTo = dtpDate2.Value
Set db = New ADODB.Connection
db.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;USER ID=max;PASSWORD=max;Data Source=oracledb"
 
sSQL = "INSERT INTO FWDINVOICE_DET@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@ORACLEDB,FWDINVOICE_HD@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)
Dim i As Integer
db.Execute sSQL, i
db.CommitTrans
 
Debug.Print sSQL
Debug.Print "records return: "; i
Debug.Print "Errors: "; Error
Debug.Print "DB Error Count: "; db.Errors.Count
Debug.Print "Connection Mode: "; db.Mode
Stop
 

Result from immediate window
INSERT INTO FWDINVOICE_DET@ ORACLEAR (COMPANYCODE,BR ANCHCODE,INVOIC ENO,JOBNO,CHARG ECODE) (SELECT FWDINVOICE_DET. COMPANYCODE,FWD INVOICE_DET.BRA NCHCODE, FWDINVOICE_DET. INVOICENO,FWDIN VOICE_DET.JOBNO ,FWDINVOICE_DET .CHARGECODE FROM FWDINVOICE_DET@ ORACLEDB,FWDINV OICE_HD@ORACLED B WHERE FWDINVOICE_HD.C OMPANYCODE = FWDINVOICE_DET. COMPANYCODE AND FWDINVOICE_HD.B RANCHCODE=FWDIN VOICE_DET.BRANC HCODE AND FWDINVOICE_HD.I NVOICENO=FWDINV OICE_DET.INVOIC ENO AND ( FWDINVOICE_HD.C ANCEL= 'Y' OR FWDINVOICE_HD.A PPROVED='Y') AND (FWDINVOICE_HD. INVOICEDATE BETWEEN '30-NOV-2003' AND '30-NOV-2006'))
records return: 0
Errors:
DB Error Count: 0
Connection Mode: 0

[Killer42]

Ok, for starters I think you might get a better response to this question in the Access forum.

Now, given the SQL string you posted, I can see a couple of things which look a bit odd. Whether they will cause problems, I don't know. You need to keep in mind that I'm a VB6 developer and not an Access expert. With luck, someone more knowledgeable in this area might pick up the thread.

Comments:

• Perhaps Access doesn't like "@" in a field name?
• Date formats do not look like Access dates, as mentioned previously.

Here's a modified version. Let me know whether it's any help. Sorry about the layout - I just chopped up the lines so I could more easily identify the pieces involved.

INSERT INTO
  FWDINVOICE_DET@ORACLEAR
    (COMPANYCODE, BRANCHCODE, INVOICENO, JOBNO, CHARGECODE)
    (SELECT
       FWDINVOICE_DET.COMPANYCODE,
       FWDINVOICE_DET.BRANCHCO DE,
       FWDINVOICE_DET.INVOICENO,
       FWDINVOICE_DET.JOBNO,
       FWDINVOICE_DET.CHARGECODE
     FROM
       FWDINVOICE_DET@ORACLEDB,
       FWDINVOICE_HD@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 #11/30/2003# AND #11/30/2006#)
   )

[Killer42]

here is the coding for your reference ...

 ...
db.Execute sSQL, i
db.CommitTrans
 ...

I'm not saying this is a problem, just curious - did you ever do a BeginTrans?

[skyloon]

this query is not for access, is for oracle..
i've mentioned it in the first thread..

I included the db.CommitTrans as well, just forgot to paste it here..sorry man..

[Killer42]

this query is not for access, is for oracle..
i've mentioned it in the first thread..

Sorry, I'm getting it mixed up with other threads. (By the way, it was the first post, or message. The "thread" refers to the whole conversation.)

However, you'll find much more SQL expertise in the Access thread. If the problem is the database conenction then you might find some help here - for instance, willakawill seems to know this area quite well.

I can't think of any way to help further, sorry. I'm surprised it has been so quiet - normally a couple of people would have jumped into the conversation by now.

I included the db.CommitTrans as well, just forgot to paste it here..sorry man..

You mean the BeginTrans, I hope. :)