ExecuteScalar method sometimes work, sometimes doesn't

[Randy Smith]

Hi,

I've got some weird behavior happening within one of the datamappers. It
all has to do with inserting a new row, and returning the Id of the row
being entered.



Here is what the code looks like that is getting "Object reference not set
to an instance ..." run-time error:

string ateId = cm.ExecuteScalar().ToString(); ß------------
this line crashes

None of the parameters are null, nor do they appear to have any illegal data
inside of them. The above code inserts a row into the AuditTrailEntry
table.



On the other hand, this code inserts a new jobStep row, and doesn't cause
any errors:

if (storedProcedure == "updateJobStep") //use this if update

cm.ExecuteNonQuery(); // no return value needed

else

{

string jobStepId = cm.ExecuteScalar().ToString(); //insert new
jobStep, get id of new row

js.Id = Convert.ToInt16(jobStepId);

}



Does anyone see a problem with this code, or know why it doesn't work?



TIA, Randy

 

[bruce barker]

ExcuteScalar returns the first column value of the first row of the
first result set. if the first result set has no rows, this routine will
throw an error. would need the sp code to determine why the first result
set has no rows.

-- bruce (sqlwork.com)

 

[Randy Smith]

Hi,
One of my coworker programmers solved the problem. The actual stored
procedure MUST return the value needed with code such as this:
SELECT SCOPE_IDENTITY() AS [AuditTrailEntriesId] //REPLACE
"AuditTrailEntriesId" with the name of the field you need to have returned

HTH, Randy