Adapter, VB.NET, 1.0.2005.1 version.

I'm trying to figure out how to test for an entity existence prior to doing an insert. I want to test for existence of a record that meets certain criteria and not insert if it exists.

SQL would look like this

IF NOT EXISTS
(
SELECT * FROM ORDERS WHERE...
)
INSERT ORDERS ...

My first attempt was testing for a primary key value.

rowID = Convert.ToInt32( _
                da.GetScalar(PartyMemberFields.PartyMemberId _
                , (PartyMemberFields.PartyMemberId * 1), AggregateFunction.Min _
                , filter))

This works correctly when the record(s) exist but throws a NULL exception when there are no records. I could catch the exception but it sure seems like I should be able to get this without that.

Otis, the GetScalar overloads annoy me because I really just wanted a AggregateFunction.CountRows but the overload requires an aggregate expression. Why isn't there a GetScalar(field, AggregateFunction, Filter) overload? It seems to me that this would be a fairly common task.

Any suggestions on how to do this better would be appreciated.

Joel Reinford Data Management Solutions LLC

It depends on what you are trying to do.

If you are just checking to see if an item exists then I would just try fetching an entity. If adapter.FetchEntity returns false then the record does not exist, if it's true then it does exist.

The following thread was discussing the same issue: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5606