ExecuteScalar and SqlDataAdapter.Fill Behave different for same query

I have a big stored procedure that I run. At the end its supposed to select a singe value to say if it succeeded or not, so I run the query as

Dim Command As New SqlCommand(SqlString, Conn)
Return Command.ExecuteScalar()

Which works. However there is an error in my stored procedure. I know its causing an error because the logic in the stored procedure rolls back the transactions, and after the Execute Scalar call, my transaction count is down to 0 and the my data hasn't changed. However no SQL exception was generated.

The strange part is, I changed the code to grab all the result sets from the SP to see if I could get more information. So I called the same SP like this,

    Dim DAObj As SqlDataAdapter = New SqlDataAdapter
    Dim CommandObj As SqlCommand = New SqlCommand(SQLString, Conn)
    DAObj.SelectCommand = CommandObj
    Dim DS As New DataSet()
    DAObj.Fill(DS)

When I run this, with the exact same sql as before, executing the exact same stored procedure, this time I get an SQL exception because one of my nested SP c开发者_开发百科alls was missing required parameters.

So what could cause this? Why would running it one way produce an error and the other way have the error but not report it? Is the difference on purpose, or some kind of obscure bug in ADO.Net?

---

A DataAdaptor consumes all datasets that come back from the client. An exception is a dataset too.

ExecuteScalar consumes the 1st row, 1st column, 1st dataset only.

• Don't use ExecuteScalar
• Keep consuming DataReaders until you get no more back
• Use DataAdaptor.Fill (DataSet). It will skip exceptions for filling DataTables too

---

.ExecuteScalar() return the first value returned, be it a value from your stored procedure, or the error code generated by your stored procedure. The fill command is expecting data to be returned and an error in sql throws an exception.