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

[sguy]

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 [EMAIL="FWDINVOICE_DET@ORACLEAR"]FWDINVOICE_DET@ORACLEAR[/EMAIL] (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)" & _
" (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE," & _
" FWDINVOICE_DET.InvoiceNo,FWDINVOICE_DET.JobNo,FWDINVOICE_DET.ChargeCode" & _
" FROM [EMAIL="FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB"]FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB[/EMAIL]" & _
" 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

[QVeen72]

Hi,

The user who queries or inserts from linked databases should be a Global User registered with the LDAP directory service.
Check if the User ID : max is a global user or not.

Check if the user has "Insert" Rights in Destination Database and "Select" Rights In Source Database

One more point, Max is the reserved word in oracle SQL, use some other user name like MyMax Or something GMax.


Regards
Veena

[sguy]

I've found the problem, its a date format problem..
may i know what is the correct date format?
i've tries to_char, to_date, trunc, still can't solve the problem

[sguy]

can help me to restructure the code? what is the correct date format?

INSERT INTO [EMAIL="FWDINVOICE_DET@ORACLEAR"]FWDINVOICE_DET@ORACLEAR[/EMAIL] (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE) (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE, FWDINVOICE_DET.INVOICENO,FWDINVOICE_DET.JOBNO,FWDINVOICE_DET.CHARGECODE FROM [EMAIL="FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB"]FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB[/EMAIL] 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 TO_DATE(FWDINVOICE_HD.INVOICEDATE,'DD-MON-YYYY') BETWEEN '29-NOV-2003' AND '29-NOV-2006')

[sguy]

here is the vb code 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 [EMAIL="FWDINVOICE_DET@ORACLEAR"]FWDINVOICE_DET@ORACLEAR[/EMAIL] (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)" & _
 " (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE," & _
  " FWDINVOICE_DET.InvoiceNo,FWDINVOICE_DET.JobNo,FWDINVOICE_DET.ChargeCode" & _
 " FROM [EMAIL="FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB"]FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB[/EMAIL]" & _
 " 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 [EMAIL="FWDINVOICE_DET@ORACLEAR"]FWDINVOICE_DET@ORACLEAR[/EMAIL] (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE) (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE, FWDINVOICE_DET.INVOICENO,FWDINVOICE_DET.JOBNO,FWDINVOICE_DET.CHARGECODE FROM [EMAIL="FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB"]FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB[/EMAIL] 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

[QVeen72]

Hi,

Use this Format

AND FWDINVOICE_HD.INVOICEDATE BETWEEN TO_DATE('" & FORMAT(txtFromDate.Text,"dd-mm-yyyy") & "','dd-mm-yyyy') And TO_DATE('" & Format(txtToDate.Text,"dd-mm-yyyy") & "','dd-mm-yyyy') "


Regards
Veena

[sguy]

it executes with no problem..
issue solved...

thanks for help...