469,928 Members | 1,862 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,928 developers. It's quick & easy.

ExecuteScalar method sometimes work, sometimes doesn't

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
Apr 23 '07 #1
2 4965
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.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

Apr 23 '07 #2
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


"bruce barker" <no****@nospam.comwrote in message
news:%2****************@TK2MSFTNGP03.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.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
Apr 24 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Shane | last post: by
24 posts views Thread by Marcin Vorbrodt | last post: by
2 posts views Thread by buran | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.