•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Visual Basic 4 / 5 / 6 section within the Software Development category of DaniWeb, a massive community of 401,946 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,332 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Views: 1979 | Replies: 6 | Solved
 |
•
•
Join Date: Nov 2006
Posts: 7
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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..
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
•
•
Join Date: Nov 2006
Posts: 711
Reputation:
Rep Power: 3
Solved Threads: 97
Master Poster
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
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
•
•
Join Date: Nov 2006
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
may i know what is the correct date format?
i've tries to_char, to_date, trunc, still can't solve the problem
•
•
Join Date: Nov 2006
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
can help me to restructure the code? what is the correct date format?
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 TO_DATE(FWDINVOICE_HD.INVOICEDATE,'DD-MON-YYYY') BETWEEN '29-NOV-2003' AND '29-NOV-2006')
•
•
Join Date: Nov 2006
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
here is the vb code for your reference.
coding in VB
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
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
StopINSERT 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
•
•
Join Date: Nov 2006
Posts: 711
Reputation:
Rep Power: 3
Solved Threads: 97
Master Poster
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
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
|
•
•
•
•
•
•
•
•
DaniWeb Visual Basic 4 / 5 / 6 Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Query conversion from Sybase to MS SQL Server 2000 (MS SQL)
- force Search Page to return no records on open (ASP)
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: Please answer
- Next Thread: let me know d steps to connect to oracle(sql) from vb6
