Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
 Posted - 2002-06-05 : 12:13:21
|
| Has anybody come acrosss the following problem? I know this is not directly SQL related....but I haven't come across it anywhere on the MSDN sites...and I don't know how to raise it as a bug/feature via the Microsoft websites.I've a VB6.0 (SP5) program (code posted below) which encounters 2 different MDAC error codes for a duplicate key condition. Given that Customer Code 000010 already exists, I want the SQL statement to FAIL. I want to trap the Err.Number that ADO returns.....and then do something with it.my expectation is that it will return error code -2147217873....which it does on my own PC....which has MDAC v2.6 on it.yet on another PC, with MDAC v2.0....the "Duplicate" error code that comes back is -2147217900....which is not what I want.why the difference....is it a bug?....and if the ADO error code can change between MDAC versions.....what others have changed?Any and all solutions/reasons appreciated.Regards Andrew VB program references..... Visual Basic for applications Visual Basic runtime objects and procedures Visual Basic objects and procedures OLE Automation Microsoft ActiveX Data Object 2.0 libraryPublic strQFlowADOConnect As StringPublic strSQL As StringPublic AccessADO As ADODB.ConnectionPublic lngRecordsAffected as LongPrivate Sub Command1_Click()On Error GoTo x strQFlowADOConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abcdusercode;Initial Catalog=abcddatabasename;Data Source=abcdservername" Set AccessADO = New ADODB.Connection AccessADO.ConnectionString = strQFlowADOConnect AccessADO.ConnectionTimeout = 60 AccessADO.CursorLocation = adUseServer AccessADO.Open strSQL = "INSERT INTO CUSTOMER SELECT * FROM CUSTOMER WHERE CODE = 000010" AccessADO.Execute strSQL, lngRecordsAffected, adExecuteNoRecordsx: MsgBox "error code = " & Err.Number End Sub |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-05 : 12:20:06
|
quote:
yet on another PC, with MDAC v2.0
Andrew,Seriously, you NEED NEED NEED to update the machines to at least MDAC 2.5. 2.5 was a major rewrite and there's no way you'll be able to practically support older and newer versions of MDAC. If you can't update them then you're SOL quite frankly, you'll drive yourself nuts trying to accommodate it.I'm not surprised MSDN doesn't have much on it, they pretty much don't bother with MDAC before 2.5. Jesus, MDAC 2.0 is ANCIENT, I think it came out BEFORE ASP did!  I certainly haven't written anything earlier than 2.1. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-05 : 12:53:50
|
| Rob...thanks for the update....Our mode of operation here....i.e. we use the stuff we're told to use...MDAC, ADO, etc....we don't worry about what it does or what it's for....If the installation instructions say we're to use it, we use it....our application is a small but reasonably busy productivity enhancer...performance not a great issue....we're more concerned with keeping existing stuff working than anything else....and adding new functionality when required....to date not a problem....but then this cat jumped into the engine!While I don't have a problem with upgrading to 2.5...or 2.6 for that matter...I'm more concerned that problems like this can occur...i.e. error number abcd is used for duplicate key in 2.0 and code wxyz is used in 2.5....what's to say M$ won't change the damn thing again? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-05 : 13:02:34
|
| Nothing. And you should expect them to change if you use ADO.Net (in fact I'm positive they're different because ADO.Net is a whole other animal) Error handling by the numbers is always gonna be a running-in-front-of-the-train kinda thing. I think you'll be OK for a while as long as you can get this particular issue solved, get the updated package out there. This one is really a issue with MDAC 2.0, not your code or technique. There were a lot of things that were smoothed out in 2.5; if you make that your minimum standard then you'll be fine for a few years yet. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-06 : 08:44:15
|
| Thanks Rob.....2.5....2.5.....2.5.....sounds like i'll be repeating it for a while.....it'll eventually sink in..... |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-06 : 10:59:20
|
| If you can not readily upgrade your MDAC, but are prepared to tweak your code, you could trap the Native Error (conn.Errors(i).nativeerror) instead of ADO error (err.number)... This way you are guaranteed to receive the same number regardless of your MDAC version. |
|
|
|
|
|
|
|
|
ADO Error Number changes for different MDACversion