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.ExecuteScala r().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 (storedProcedur e == "updateJobStep" ) //use this if update

cm.ExecuteNonQu ery(); // no return value needed

else

{

string jobStepId = cm.ExecuteScala r().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]

Re: ExecuteScalar method sometimes work, sometimes doesn't

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 wrote:

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.ExecuteScala r().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 (storedProcedur e == "updateJobStep" ) //use this if update
>
cm.ExecuteNonQu ery(); // no return value needed
>
else
>
{
>
string jobStepId = cm.ExecuteScala r().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
>
>

[Randy Smith]

Re: ExecuteScalar method sometimes work, sometimes doesn't

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 [AuditTrailEntri esId] //REPLACE
"AuditTrailEntr iesId" with the name of the field you need to have returned

HTH, Randy




"bruce barker" <nospam@nospam. comwrote in message
news:%23b4OUeeh HHA.4732@TK2MSF TNGP03.phx.gbl. ..

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 wrote:

>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.ExecuteScala r().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
>AuditTrailEntr y table.
>>
>>
>>
>On the other hand, this code inserts a new jobStep row, and doesn't cause
>any errors:
>>
> if (storedProcedur e == "updateJobStep" ) //use this if update
>>
> cm.ExecuteNonQu ery(); // no return value needed
>>
> else
>>
> {
>>
> string jobStepId = cm.ExecuteScala r().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
>>