Trigger Error: List has no rows for assignment to SObject

Trying to solve one last problem on a trigger I am working on. Currently, the trigger works fine on 'insert' and 'delete', but generating the following error message when 'updating':

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger rollupCaseHoursTo360 caused an unexpected exception, contact your administrator: rollupCaseHoursTo360: execution of AfterUpdate caused by: System.QueryException: List has no rows for assignment to SObject: Trigger.rollupCaseHoursTo360: line 91, column 44

The problem occurs when one of our particular fields is NULL; which is the case most of the time. So the update trigger needs to test to make sure the value is not NULL before executing the rest of the code. Below is a copy of the update section of trigger and comments of how I tried to test for the NULL value and section generating error:

.
.
.
else if(Trigger.isUpdate)
{
	//Tried adding test to verify value was not null before executing rest of code
	if (Case.X360_Contract_Cycle__c != NULL) {
	
	//sum total both old and new
	Case [] oldTime = Trigger.old;
	Case [] newTime = Trigger.new;
	Double newSum = 0.0;
	Double oldSum = 0.0;

	for(Case newTe: newTime)
		{
			for(Case oldTe : oldTime)
				{

// SECTION GENERATING ERROR IF 'X360_Contract_Cycle__c' IS NULL
X360_Contract_Cycle__c oldTimesheet = [Select Id, Name, Delivered_Hours__c from X360_Contract_Cycle__c where Id = :oldTe.X360_Contract_Cycle__c];

X360_Contract_Cycle__c newTimesheet = [Select Id, Name, Delivered_Hours__c from X360_Contract_Cycle__c where Id = :newTe.X360_Contract_Cycle__c];
					
					
					Case [] newSumHours = [Select Id, Case_Hours__c  from Case where X360_Contract_Cycle__c = :newTimesheet.Id];
					Case [] oldSumHours = [Select Id, Case_Hours__c from Case where X360_Contract_Cycle__c = :oldTimesheet.Id];

							//sum premiums from child objects
							for(Case oldSumHour : oldSumHours)
							{
								//converting 'Case_Hours__c' from string data type to number (double)
    							double oldCaseHours = double.valueOf(oldSumHour.Case_Hours__c);
								oldSum += oldCaseHours;
							}

							for(Case newSumHour : newSumHours)
							{
								//converting 'Case_Hours__c' from string data type to number (double)
    							double newCaseHours = double.valueOf(newSumHour.Case_Hours__c);
								newSum += newCaseHours;
							}

					newTimesheet.Delivered_Hours__c = newSum;
					oldTimesheet.Delivered_Hours__c = oldSum;

					//sheetsToUpdate.add(newTimesheet);
					//sheetsToUpdate.add(oldTimesheet);

					sheetsToUpdate.add(newTimesheet);
					if(newTimesheet.Id != oldTimesheet.Id){
						sheetsToUpdate.add(oldTimesheet);
					}

				}
			}


update sheetsToUpdate;

}

}

I am far from a full fledged Developer so trying to hack my way through this a bit. Logically I thought the IF test would work, but the rest of the code appears to execute regardless if the value is NULL or not. If the value is not NULL the update works fine. Any suggestions? Thanks!!

July 1, 2010
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

frederic baudax
Hi,

Sometime the NULL value doesn't get caught for some odd reason, i've run into a similar issue last week and by-passed it the following way:

TestObject__c TestObject;
Try {
TestObject = [SELECT Description__c FROM TestObject__c WHERE field__c = :to.field__c];
}
catch(System.QueryException e){

trigger.new[0].addError('Null value!');

Return;
}

Not the best way to do it, but it does the trick.

Hope this helps,

Kr,

Fred

July 2, 2010
·
Like
1
·
Dislike
0

This was selected as the best answer

SeanCo
The Try-Catch did the job...thanks so much! Although I find it a little alarming that such a test would not work *on occasion* the Try-Catch (while not the most graceful method) will do in this case.

Thanks again,

~ Sean

July 3, 2010
·
Like
0
·
Dislike
0

frederic baudax
Hi,

You're welcome, happy i could help.

Tu be noted that i only had this case where i didn't query for the field myself, thought i could reproduce it afterwards with other fields so only explanation i can think of is that since the field value is NULL it isn't taken into account/isn't present and therefor can not be tested for it's value, but that's just a wild guess.

Kr,

Fred.

July 5, 2010
·
Like
0
·
Dislike
0

DDS
I'm getting the same error message for a different scenario.

I have a Status field that holds different stages of a deal. When the status changes to 'Closed' it triggers a series of events. This is a "before insert, before update" trigger

for (Integer i = 0; i < Trigger.new.size(); i++) { if ((Trigger.new[i].Status__c == 'Closed')&&(Trigger.old[i].Status__c <> 'Closed')) {

.....

}

This works fine if the Status I select when I first create the record is different from Closed, otherwise it will return an error basically saying there is no "Trigger.old"?

How do I avoid this? Is there a way of creating an "if" statement that flags whether I'm doing an insert or an update? Something like:

if ( before insert) {

....

}

I'm a beginner APEX coder so the solution might be really easy.

Thanks in advance for your help.

September 1, 2010
·
Like
0
·
Dislike
0

frederic baudax
Hi,

List has no rows for assignment to SObject means you have queried the DB and no records (rows) have returned, this will never happen in a before insert when working on the concerned record. Can you post the complete trigger ? Will make it possible to help you find the issue.

As for your question,

You can indeed use this:

for (Integer i = 0; i < Trigger.new.size(); i++) {
if (trigger.Isinsert ||(trigger.Isupdate && Trigger.new[i].Status__c == 'Closed' && Trigger.old[i].Status__c <> 'Closed')) {

.....

}

Or if you want to split your code:

if ( trigger.Isinsert && Object.Status__c == 'Closed') {

//add logic and/or actions here

}

Don't forget to exclude it in the other part of your code

for (Integer i = 0; i < Trigger.new.size(); i++) {
if (Trigger.new[i].Status__c == 'Closed' && Trigger.old[i].Status__c <> 'Closed' && trigger.Isupdate) {

.....

}

Cheers,

Fred

September 2, 2010
·
Like
0
·
Dislike
0

DDS

trigger CreateClosing on Project__c (before insert, before update) {
    Closing__c[] c = new Closing__c[0];
    string rtLease = '012A0000000SrOn';
    string rtSale = '012A0000000SrOi';

    for (Integer i = 0; i < Trigger.new.size(); i++) {
if ((Trigger.new[i].Status__c == 'Closed')&&(Trigger.old[i].Status__c <> 'Closed')) {
                System.debug('triggerfire');
                if (trigger.new[i].Listing_Type__c <> 'Sale'){
                c.add(new Closing__c(
                    Name = Trigger.new[i].Name,
                    Project__c = Trigger.new[i].Id,
                    Deal_Type__c = Trigger.new[i].Listing_Type__c,
                    Transaction_RSF__c = Trigger.new[i].Square_Footage__c,
                    Property_Type__c = Trigger.new[i].rethink2__Property_Type__c,
                    Class_Of_Property__c = Trigger.new[i].Class_of_Property__c,
                    Lease_Commencement__c = Trigger.new[i].Projected_Lease_Commencement_Date__c,
                    Lease_Expiration__c = Trigger.new[i].Deal_LED__c,
                    Term_Months__c = Trigger.new[i].Deal_Lease_Term_Months__c,
                    Commission__c = Trigger.new[i].Commission_Rate__c,
                    Effective_Rent__c = Trigger.new[i].Effective_Rent_per_Sq_Ft_Annual__c,
                    Total_Commission__c = Trigger.new[i].Est_Commision_Value__c,
                    rethink2__Account_Household__c = Trigger.new[i].rethink2__Company__c,
                    Seller_Landlord__c = Trigger.new[i].Landlord_Sub_Landlord__c,
                    City__c = Trigger.new[i].City__c,
                    State__c = Trigger.new[i].State__c,
                    Zip_Code__c = Trigger.new[i].Zip_Code__c,
                    Date_Signed__c = Trigger.new[i].rethink2__Deal_Close_Date__c,
                    Street_Address__c = Trigger.new[i].Deal_Address__c,
                    Property__c = Trigger.new[i].Property__c,
                    Space__c = Trigger.new[i].Space__c,
                    RecordTypeID = rtlease
                    ));
             }


    insert c;

}

September 2, 2010
·
Like
0
·
Dislike
0

DDS
Thanks for your help Frederic!

That's the code. It basically creates a record on a related object and maps some of the fields. I do get this error on line 13 ( the one I pasted before:

Apex trigger CreateClosing caused an unexpected exception, contact your administrator: CreateClosing: execution of BeforeInsert caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.CreateClosing: line 13, column 71

Again, this only happens when I set the status to Closed when I first create the record.

September 2, 2010
·
Like
0
·
Dislike
0

frederic baudax
You can use the following to solve the NullPointerException.

trigger CreateClosing on Project__c (before insert, before update) {
Closing__c[] c = new Closing__c[0];
string rtLease = '012A0000000SrOn';
string rtSale = '012A0000000SrOi';

for (Integer i = 0; i < Trigger.new.size(); i++) {
if (trigger.Isinsert ||(trigger.Isupdate && Trigger.new[i].Status__c == 'Closed' && Trigger.old[i].Status__c <> 'Closed')) {
System.debug('triggerfire');
if (trigger.new[i].Listing_Type__c <> 'Sale'){
c.add(new Closing__c(
Name = Trigger.new[i].Name,
Project__c = Trigger.new[i].Id,
Deal_Type__c = Trigger.new[i].Listing_Type__c,
Transaction_RSF__c = Trigger.new[i].Square_Footage__c,
Property_Type__c = Trigger.new[i].rethink2__Property_Type__c,
Class_Of_Property__c = Trigger.new[i].Class_of_Property__c,
Lease_Commencement__c = Trigger.new[i].Projected_Lease_Commencement_Date__c,
Lease_Expiration__c = Trigger.new[i].Deal_LED__c,
Term_Months__c = Trigger.new[i].Deal_Lease_Term_Months__c,
Commission__c = Trigger.new[i].Commission_Rate__c,
Effective_Rent__c = Trigger.new[i].Effective_Rent_per_Sq_Ft_Annual__c,
Total_Commission__c = Trigger.new[i].Est_Commision_Value__c,
rethink2__Account_Household__c = Trigger.new[i].rethink2__Company__c,
Seller_Landlord__c = Trigger.new[i].Landlord_Sub_Landlord__c,
City__c = Trigger.new[i].City__c,
State__c = Trigger.new[i].State__c,
Zip_Code__c = Trigger.new[i].Zip_Code__c,
Date_Signed__c = Trigger.new[i].rethink2__Deal_Close_Date__c,
Street_Address__c = Trigger.new[i].Deal_Address__c,
Property__c = Trigger.new[i].Property__c,
Space__c = Trigger.new[i].Space__c,
RecordTypeID = rtlease
));
}

insert c;

}

Cheers,

Fred

September 2, 2010
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Trigger Error: List has no rows for assignment to SObject

All Answers

You need to sign in to do that.