Execute Scalar

  • Thread starter Thread starter Mike P
  • Start date Start date
M

Mike P

Is it OK to use ExecuteScalar if there is a possibility that the SQL it
uses may not return a value? I'm asking this because I have a table
with a particular field (call it Department) and then another field
(call it Department number). Now I want to find the last record with a
particular Department, so that for the one I am going to add, I can add
1 to the Department number I have found to give the new record the next
ascending Department number.

I am using ExecuteScalar, but in the situation where there are not yet
any members of that Department, I need to check this before assigning
the return value of ExecuteScalar. How do I do this?


Thanks,

Mike
 
Mike,

In this case, the call to ExecuteScalar should return null, or DbNull,
as that is what the underlying DB is going to return to you in this
situation. Or, at least, this is what you should craft your selects to
return to you.

Also, as a side note, this seems to be a very non-scalable approach. If
you are looking to generate IDs, then you might want to keep them in a
separate table, especially if you are performing transactions. The locks
required for the transaction (especially if it becomes long running) can
have a very detrimental effect on this kind of design. If you keep the ids
in another table, then you can increment the value in that table. You can
also create a component that creates a new transaction which is separate
from any other. That way, the operation is very quick (and you don't have
to worry if the calling transaction fails, you just have to accept that your
ids might not be non-sequential).

Hope this helps.
 
Yes you can. You get a null object reference back if
there are no rows in the result set - or maybe DBNull I
forget which - but it's ok to do what you want to do...

--Richard
 
I% SAY :::
Yes you can. You get a null object reference back if
there are no rows in the result set - or maybe DBNull I
forget which - but it's ok to do what you want to do...

--Richard
Click to expand...

what i do is step my sproc to return -1 if there
are no matches.

 
Back
Top