Hi,

Using adapter, I'm trying to figure out how to retrun all rows (from SQLServer 2000) where someField=null in a joined table. For example:

select table1., table2. from table1 inner join table 1.id on table2.id where table2.field1 is null

Here's what I'm trying:

EntityCollection proposalTypes = new EntityCollection(new ProposalTypeEntityFactory());

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProposalTypeEntity);
            
IPrefetchPathElement2 node1 = prefetchPath.Add(
    ProposalTypeEntity.PrefetchPathProposalTypeFormMap).SubPath.Add(
        ProposalTypeFormMapEntity.PrefetchPathSurveyForm);

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(
    PredicateFactory.CompareValue(ProposalTypeFieldIndex.IsActive, ComparisonOperator.Equal, 1));
filter.PredicateExpression.Add(
     new FieldCompareNullPredicate( EntityFieldFactory.Create(ProposalTypeFormMapFieldIndex.RetireDate), null) );

but I get this SQL error when I do adapter.FetchEntityCollection:

An exception was caught during the execution of a retrieval query: The column prefix 'MyDB.dbo.ProposalTypeFormMap' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Any help? It seems that the last line of code is what causes the error.

Thx, Jack

Hi,

Hmm, no replies yet.. I'm still confused by this issue.... and a related one... seems like it should be very simple, am just looking for the right syntax....

I have a SQL table with a field that's an int type. It's an FK to a table that will not have any entries with a value of 0. IE, I can't do this without an FK error due to MyIntField=0:

INSERT INTO MyTable (FieldA, FieldB, MyIntField) VALUES (1,2,0)

With LLBLGen, I am doing something like:

MyEntity myEnt = new MyEntity(); myEnt.blah = "something"; etc... setting property values, but NOT setting the MyIntField property.

When I use adapter.SaveEntity(myEnt, true);

it throws an FK error, because the generated SQL is trying to insert 0 in MyIntField. I'm guessing that's because MyIntField property defaults to 0, since it's a .net int datatype.

So, is there a way to specify that I want MyIntField = null? IE, so the SQL is:

INSERT INTO MyTable (FieldA, FieldB, MyIntField) VALUES (1,2,null)

or so that the field is skipped in the insert:

INSERT INTO MyTable (FieldA, FieldB) VALUES (1,2)

Thx. Jack

Sorry for the late reply, I missed it in the list.

Jackk100 wrote:

Hi, Using adapter, I'm trying to figure out how to retrun all rows (from SQLServer 2000) where someField=null in a joined table. For example:

select table1., table2. from table1 inner join table 1.id on table2.id where table2.field1 is null

Here's what I'm trying:

EntityCollection proposalTypes = new EntityCollection(new ProposalTypeEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProposalTypeEntity);
IPrefetchPathElement2 node1 = prefetchPath.Add(
    ProposalTypeEntity.PrefetchPathProposalTypeFormMap).SubPath.Add(
        ProposalTypeFormMapEntity.PrefetchPathSurveyForm);
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(
    PredicateFactory.CompareValue(ProposalTypeFieldIndex.IsActive, ComparisonOperator.Equal, 1));
filter.PredicateExpression.Add(
     new FieldCompareNullPredicate( EntityFieldFactory.Create(ProposalTypeFormMapFieldIndex.RetireDate), null) );

The query in SQL you're suggesting is not a prefetch path query. You want the data in a single table or in a hierarchy? This is important, as a prefetch path doesn't use a join but a subquery. It executes 1 query per graph node, so in your case, 3 queries: one for proposaltype, one for proposaltypeformmap and one for surveyform.

Per node you can setup filters for that particular query. So if you want to filter the objects of a particular node if a related entity is nonexistend, you have to formulate a RelationCollection and a PredicateExpression for that node and pass it to the Add() overload of the PrefetchPath2 object.

The filters for the ROOT node (in your case ProposalType) you have to specify when you call FetchEntityCollection. As I described, the prefetchpaths don't use a join, but subqueries. This has the effect that when the root entities are fetched (these are fetched first, and the query used to filter them is used as a filter in the subquery for fetching the child entities etc.) you have to supply related entities if you want to filter on them. So you should add the relation ProposalTypeEntity.Relations.ProposalTypeFormMapUsingProposalTypeID (or equivalent, I don't know the PK field name) to filter.Relations

You can also form the null predicate with the predicatefactory, saves you some code

Jackk100 wrote:

Hmm, no replies yet.. I'm still confused by this issue.... and a related one... seems like it should be very simple, am just looking for the right syntax....

I have a SQL table with a field that's an int type. It's an FK to a table that will not have any entries with a value of 0. IE, I can't do this without an FK error due to MyIntField=0:

INSERT INTO MyTable (FieldA, FieldB, MyIntField) VALUES (1,2,0)

With LLBLGen, I am doing something like:

MyEntity myEnt = new MyEntity(); myEnt.blah = "something"; etc... setting property values, but NOT setting the MyIntField property.

When I use adapter.SaveEntity(myEnt, true);

it throws an FK error, because the generated SQL is trying to insert 0 in MyIntField. I'm guessing that's because MyIntField property defaults to 0, since it's a .net int datatype.

No, it will never insert a value in a field if that field is not changed (i.e.: if its IsChanged flag is set to false). As it will not insert a value, the insert will be: INSERT INTO MyTable (FIeldA, FieldB) VALUES (@paramA, @paramB) and thus sqlserver will insert NULL for MyIntField. If MyIntField is not nullable, this will thus give an error.

So if you do all this, please paste the EXACT code you execute.

Hi,

RE:

Otis wrote:

So if you do all this, please paste the EXACT code you execute.

Here's the scoop....

I have a DropDownList control (called ddlLogo), with data kinda like this:

VALUE | TEXT

-1 | [blank] 1 | LogoName1 2 | LogoName2 etc... one option for each LogoName

The user can pick one of the LogoName's or they can pick the [blank]. If they pick the blank, it means (to me) that I want to store NULL in my db field.

In my code I'm trying to do something like this (note: LogoImageID is an int datatype):

mailbox = new MailboxEntity();

int logoImageID = int.Parse(ddlLogo.SelectedValue);
if (logoImageID > -1)
    mailbox.LogoImageID = logoImageID;
else
    mailbox.LogoImageID = null;

... set some other mailbox properties....

then do the save:

DataAccessAdapter adapter = new DataAccessAdapter();
adapter.SaveEntity(mailbox,true);
adapter.CloseConnection();

that of course, won't compile, because I can't set an int to null, but I tried DBNull.Value and that of course won't compile, either.

As you can see, I'm using SaveEntity to save the entity (and all it's properties, which include some collections ... and which is a very sweet way to do saves, it works fine other than this property). I'm thinking there must be some easy way to have SaveEntity store a null in the db for the mailbox.LogoImageID by setting the property in a certain way (or maybe flagging it as changed, but giving it no value or something).

TIA, Jack

Jackk100 wrote:

I have a DropDownList control (called ddlLogo), with data kinda like this:

VALUE | TEXT

-1 | [blank] 1 | LogoName1 2 | LogoName2 etc... one option for each LogoName

The user can pick one of the LogoName's or they can pick the [blank]. If they pick the blank, it means (to me) that I want to store NULL in my db field.

In my code I'm trying to do something like this (note: LogoImageID is an int datatype):

mailbox = new MailboxEntity();

int logoImageID = int.Parse(ddlLogo.SelectedValue);
if (logoImageID > -1)
    mailbox.LogoImageID = logoImageID;
else
    mailbox.LogoImageID = null;

... set some other mailbox properties....

then do the save:

DataAccessAdapter adapter = new DataAccessAdapter();
adapter.SaveEntity(mailbox,true);
adapter.CloseConnection();

that of course, won't compile, because I can't set an int to null, but I tried DBNull.Value and that of course won't compile, either.

do:

int logoImageID = int.Parse(ddlLogo.SelectedValue);
if (logoImageID > -1)
    mailbox.LogoImageID = logoImageID;
else
    mailbox.SetNewFieldValue((int)MailBoxFieldIndex.LogoImageID, null);

See: using the generated code / selfservicing / using the entity classes -> Entities, NULL values and defaults

Also you don't have to call CloseConnection after SaveEntity if you haven't set KeepConnectionOpen to true, as SaveEntity will open a connection for itself and close it afterwards

Thanks, that resolved the issue for me... and it was helpful for you to point me to that section of the help docs to learn more about the TypeDefaultValue helper class, etc.

BTW, I was using Adapter, but you mentioned Self-Servicing. Found what I needed in the Adapter section.

Re: SaveEntity closing the connection, good to know. Are there any other actions that automatically close the connection? I've been closing them manually out of habit.

Thx, Jack

Jackk100 wrote:

BTW, I was using Adapter, but you mentioned Self-Servicing. Found what I needed in the Adapter section.

Sorry about that, the technique used is the same for both in this case

Re: SaveEntity closing the connection, good to know. Are there any other actions that automatically close the connection? I've been closing them manually out of habit.

You never have to close a connection unless you explicitly set KeepConnectionOpen to true. KeepConnectionOpen is sometimes useful if you need to perform a couple of actions in sequence and you therefore know you need a connection for the whole routine, so you can save yourself some cycles. In that situation, you have to close the connection manually. In all other cases, teh connection is closed once the action is over. Opening a connection per action requests a new connection from the connection pool of teh ADO.NET provider, which on average takes less than 20ms.