constellation
Programmer
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.
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.