query run successfully in sql*plus but return 0 records affe

Moderator: dafunkymunky

Page 1 of 1

[ 2 posts ]

Print view

Previous topic | Next topic

Author

Message

skyloon

Post subject: query run successfully in sql*plus but return 0 records affe

Posted: Wed Nov 29, 2006 8:43 am

Joined: Wed Nov 29, 2006 8:41 am
Posts: 2

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..

Code:

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

Top

skyloon

Post subject:

Posted: Thu Nov 30, 2006 2:43 am

Joined: Wed Nov 29, 2006 8:41 am
Posts: 2

here is the coding for your reference
coding in VB

Code:

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,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'))
records return: 0
Errors:
DB Error Count: 0
Connection Mode: 0

Top

Page 1 of 1

[ 2 posts ]

Board index » Programming » Visual Basic Forum

All times are UTC + 1 hour

Who is online

Users browsing this forum: No registered users and 0 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to: