ADO not reporting SQL execution errors (VB6, MDAC 2.7)

[constellation]

ADO seems to be inconsistent in reporting errors whilst executing commands. Sometimes they are reported but other times they are simply not reported and nothing appears to go wrong, however the command is not executed.

For example I am executing the following 3 sql lines

1. CREATE DATABASE TEST

2.USE TEST CREATE TABLE tblOne(PriKey BIGINT, Details Varchar(10))

3. USE TEST CREATE TABLE tblTwo(PriKey BIGINT, OneKey BIGINT, CONSTRAINT fk_tblTwo_tblOne FOREIGN KEY (OneKey) REFERENCES tblThree (PriKey))

The reference to tblThree in line 3 should in fact be to tblOne, since tblThree does not exist.

The SQL is fired line by line using:

Set connectionObject = New ADODB.Connection

connectionObject.ConnectionString = strConnectionString
connectionObject.Open

connectionObject.Execute strSqlLine 'this done for each line
If connectionObject.Errors.Count > 0 Then
For i = 0 To (connectionObject.Errors.Count - 1)
MsgBox connectionObject.Errors(i).Description
etc.

No error is reported like this, neither is a VB error raised, and any errors that are reported simply say things like "DB context changed to TEXT" and other non-error messages. On inspection of the resulting database the first table is created but the second, quite naturally, is not, due to the error. But how to get the thing to report this rather than have to check everything it is supposed to have done afterwards?

If I try the CREATE DATABASE TEST line again, however, it quite rightly reports as an error that the thing already exists.

 

[NoCoolHandle]

constellation,

first the whole connection errors thing is a big pain that needs to be fixed..

If your connection string uses an ODBC connection (provider = msdasql) then you will return all of the errors a specific command could raise, but if you use sqloledb the best you can expect is one.

Try cutting the following code out and pasting it into notepad. Save the file as testerr.vbs (or something like that).

Then run the script by double clicking it.

This should demonstate some of the problems with ado and sqlserver errors. (I reported this to MS in ado 2.1 and I am running ado 2.7 and it still does the same , obvously if you need to get more than one error back from sql you need ODBC ..

HTH

Rob


on error resume next
dim cn
set cn = createobject("adodb.connection&quot
cn.open "provider=sqloledb;server=localhost;trusted_connection=yes"
'cn.execute "create database northwind"
if cn.errors.count > 0 then
for each er in cn.errors
msgbox er.description
next
end if
' if the northwind database existed, then an error would be reported..
cn.execute "create database junk"
cn.execute "use junk" ' Change database context to your new database
cn.execute "create proc errrrrs as raiserror ('Problem1',1,16) raiserror ('Problem2',1,16) raiserror ('Problem3',1,16)"
cn.execute "errrrrs"
if cn.errors.count > 0 then
for each er in cn.errors
msgbox er.description
next
end if
' Change connection type and try again
cn.close
cn.connectionstring = "provider=msdasql;driver=sql server;server=localhost;database=junk;trusted_connection=yes"
cn.open
cn.execute "errrrrs"
if cn.errors.count > 0 then
for each er in cn.errors
msgbox er.description
next
end if




cn.close
set cn = nothing

 

[NoCoolHandle]

One more thought, if you are executing more than one command, check after each execution to see if there is an error. Depending on your error trapping technique you may not see the error if a successfull execution cleared it.

Rob

 

[cmmrfrds]

Another option is to use ADOX and manipulate the sql server objects through the ADOX library. Although there may be some limitations, I have used ADOX to add and manipulate table objects in sql server.

 

[constellation]

Thank you both. I've used the ODBC connection method and it works fine now, many thanks for your help.