help: getting only first error when executing multiple sql statements

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ronny Zuschke (NoSpamPlease_at_dummy.org)
Date: 07/01/04 

Date: Thu, 01 Jul 2004 16:35:29 +0100

Hello everyone,

I am using ADO with VB.NET to connect to a SQL Server 2000. I use the
Command object to execute multiple SQL statements in one batch. The
problem I have is that as soon as any errors occur in that statement I
can only get the first error out of Errors collection.

For example:
In my DB I have a table called 'myTable' with a column named 'name'
which is the primary key. The 'name' column is filled with dummy values
'test1', 'test2', etc. If I would execute the following SQL statements
separately I would get 4 errors each time. That's clear.

insert into myTable( name ) values ('test1')
insert into myTable( name ) values ('test2')
insert into myTable( name ) values ('test3')
insert into myTable( name ) values ('test4')

But when I put these statments into one batch there's only the first
error in the Errors collection.

Here is some code:

         Dim conn1 As New ADODB.Connection
         conn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial
Catalog=myDatabase;Data Source=myDataSource"
         conn1.Open()

         Dim cmd1 As New ADODB.Command
         Dim sr As New StreamReader("sql_statements.txt")
         Dim sSQL As String = sr.ReadToEnd()

         Try
             With cmd1
                 .ActiveConnection = conn1
                 .CommandText = sSQL
                 .Execute()
             End With
         Catch
             Dim errors As ADODB.Errors = cmd1.ActiveConnection.Errors()
             For i As Integer = 0 To (errors.Count - 1) Step 1
                 MessageBox.Show(errors.Item(i).Description)
             Next
         End Try

I am reading the SQL statements out of the 'sql_statements.txt' file.
The Errors object does actually contain 2 items. The first item is the
error that the key could not be created, the second just telling that
the statement was aborted. The Exception object does also just contain
the first error.

Is this supposed to work like this or am I doing something wrong? Any
help would be appreciated.
Ronny Zuschke

Relevant Pages

  • Re: create multiples Stored Procedures usign ADOB command
    ... Since GO is not a valid Transact-SQL statement, but a batch statement, you ... Only way to execute batch of the SQL statements using OLEDB ... > Into query analyzer, to run a query batch, GO command is used to run all ...
    (microsoft.public.vb.database.ado)
  • Re: how to set sort order for MSDE 2000 A
    ... For instance if i login and transmit a batch of sql statements and log off ...
    (microsoft.public.sqlserver.msde)
  • Query Batch
    ... Jhon you mentioned that one may send a batch of ... queries all at once. ... reason it doesn't allow me to have to sql statements ... Protect your identity with Yahoo! ...
    (php.general)
  • RE: getting value at time of insert?
    ... One way that you can do this is by batching your Sql Statements. ... The use of the semi-colon allows you to "batch" the statements together and ... Now if you execute this with ...
    (microsoft.public.dotnet.framework.adonet)