By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,594 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,594 IT Pros & Developers. It's quick & easy.

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

P: 5
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..

Expand|Select|Wrap|Line Numbers
  1. db.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;USER ID=max;PASSWORD=max;Data Source=oracledb"
  2.  
  3. sSQL = "INSERT INTO FWDINVOICE_DET@ORACLEAR (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)" & _
  4. " (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE," & _
  5. " FWDINVOICE_DET.InvoiceNo,FWDINVOICE_DET.JobNo,FWDINVOICE_DET.ChargeCode" & _
  6. " FROM FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB" & _
  7. " Where FWDINVOICE_HD.CompanyCode = FWDINVOICE_DET.CompanyCode" & _
  8. " AND FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE" & _
  9. " AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO" & _
  10. " AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y') AND "
  11. sSQL = sSQL & oLib.SQLRangeDate("FWDINVOICE_HD.InvoiceDate", Format(dtDateFrom, "dd-MMM-yyyy"), Format(dtDateTo, "dd-MMM-yyyy"))
  12. sSQL = sSQL & ")"
  13.  
  14. sSQL = UCase(sSQL)
  15. db.Execute sSQL
  16.  
Nov 29 '06 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,434
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.
Nov 29 '06 #2

P: 5
i've tried using to_char, to_date, trunc, the problem still can't solve..
may i know what is the correct date format?
Nov 29 '06 #3

Expert 5K+
P: 8,434
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.SQLRangeDate() does? Or better still, post a copy of what's in sSQL immediately before issuing the Execute.
Nov 29 '06 #4

P: 5
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..

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO FWDINVOICE_DET@ORACLEAR (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE) 
  2. (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE, FWDINVOICE_DET.INVOICENO,
  3.     FWDINVOICE_DET.JOBNO,FWDINVOICE_DET.CHARGECODE 
  4. FROM FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB 
  5. WHERE FWDINVOICE_HD.COMPANYCODE = FWDINVOICE_DET.COMPANYCODE 
  6.     AND FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE 
  7.     AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO 
  8.     AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y') 
  9.     AND (FWDINVOICE_HD.INVOICEDATE BETWEEN '30-NOV-2003' AND '30-NOV-2006'))
  10.  
Nov 30 '06 #5

P: 5
here is the coding for your reference

coding in VB
Expand|Select|Wrap|Line Numbers
  1. dtDateFrom = dtpDate1.Value
  2. dtDateTo = dtpDate2.Value
  3. Set db = New ADODB.Connection
  4. db.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;USER ID=max;PASSWORD=max;Data Source=oracledb"
  5.  
  6. sSQL = "INSERT INTO FWDINVOICE_DET@ORACLEAR (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE)" & _
  7.     " (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE," & _
  8.         " FWDINVOICE_DET.InvoiceNo,FWDINVOICE_DET.JobNo,FWDINVOICE_DET.ChargeCode" & _
  9.     " FROM FWDINVOICE_DET@ORACLEDB,FWDINVOICE_HD@ORACLEDB" & _
  10.     " Where FWDINVOICE_HD.CompanyCode = FWDINVOICE_DET.CompanyCode" & _
  11.         " AND FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE" & _
  12.         " AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO" & _
  13.         " AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y') AND "
  14. sSQL = sSQL & oLib.SQLRangeDate("FWDINVOICE_HD.InvoiceDate", Format(dtDateFrom, "dd-mmm-yyyy"), Format(dtDateTo, "dd-Mmm-yyyy"))
  15. sSQL = sSQL & ")"
  16.  
  17. sSQL = UCase(sSQL)
  18. Dim i As Integer
  19. db.Execute sSQL, i
  20. db.CommitTrans
  21.  
  22. Debug.Print sSQL
  23. Debug.Print "records return: "; i
  24. Debug.Print "Errors: "; Error
  25. Debug.Print "DB Error Count: "; db.Errors.Count
  26. Debug.Print "Connection Mode: "; db.Mode
  27. Stop
  28.  
Result from immediate window
INSERT INTO FWDINVOICE_DET@ORACLEAR (COMPANYCODE,BRANCHCODE,INVOICENO,JOBNO,CHARGECODE ) (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCO DE, FWDINVOICE_DET.INVOICENO,FWDINVOICE_DET.JOBNO,FWDI NVOICE_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
Nov 30 '06 #6

Expert 5K+
P: 8,434
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.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO
  2.   FWDINVOICE_DET@ORACLEAR
  3.     (COMPANYCODE, BRANCHCODE, INVOICENO, JOBNO, CHARGECODE)
  4.     (SELECT
  5.        FWDINVOICE_DET.COMPANYCODE,
  6.        FWDINVOICE_DET.BRANCHCO DE,
  7.        FWDINVOICE_DET.INVOICENO,
  8.        FWDINVOICE_DET.JOBNO,
  9.        FWDINVOICE_DET.CHARGECODE
  10.      FROM
  11.        FWDINVOICE_DET@ORACLEDB,
  12.        FWDINVOICE_HD@ORACLEDB
  13.      WHERE
  14.        FWDINVOICE_HD.COMPANYCODE  = FWDINVOICE_DET.COMPANYCODE
  15.      AND FWDINVOICE_HD.BRANCHCODE = FWDINVOICE_DET.BRANCHCODE
  16.      AND FWDINVOICE_HD.INVOICENO  = FWDINVOICE_DET.INVOICENO
  17.      AND (FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED = 'Y')
  18.      AND (FWDINVOICE_HD.INVOICEDATE BETWEEN #11/30/2003# AND #11/30/2006#)
  19.    )
Nov 30 '06 #7

Expert 5K+
P: 8,434
here is the coding for your reference ...
Expand|Select|Wrap|Line Numbers
  1.  ...
  2. db.Execute sSQL, i
  3. db.CommitTrans
  4.  ...
I'm not saying this is a problem, just curious - did you ever do a BeginTrans?
Nov 30 '06 #8

P: 5
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..
Nov 30 '06 #9

Expert 5K+
P: 8,434
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. :)
Nov 30 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.