Execute Scalar

[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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Nicholas Paldino [.NET/C# MVP]]

Re: Execute Scalar

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.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"Mike P" <mrp@telcoelect ronics.co.uk> wrote in message
news:eLiEr0ylDH A.1004@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Richard]

Execute Scalar

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
[color=blue]
>-----Original Message-----
>Is it OK to use ExecuteScalar if there is a possibility[/color]
that the SQL it[color=blue]
>uses may not return a value? I'm asking this because I[/color]
have a table[color=blue]
>with a particular field (call it Department) and then[/color]
another field[color=blue]
>(call it Department number). Now I want to find the last[/color]
record with a[color=blue]
>particular Department, so that for the one I am going to[/color]
add, I can add[color=blue]
>1 to the Department number I have found to give the new[/color]
record the next[color=blue]
>ascending Department number.
>
>I am using ExecuteScalar, but in the situation where[/color]
there are not yet[color=blue]
>any members of that Department, I need to check this[/color]
before assigning[color=blue]
>the return value of ExecuteScalar. How do I do this?
>
>
>Thanks,
>
>Mike
>
>
>
>*** Sent via Developersdex http://www.developersdex.com[/color]
***[color=blue]
>Don't just participate in USENET...get rewarded for it!
>.
>[/color]

[I R BABOON]

Re: Execute Scalar

I% SAY :::
[color=blue]
> 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
>[/color]

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

[color=blue][color=green]
>>-----Original Message-----
>>Is it OK to use ExecuteScalar if there is a possibility[/color]
> that the SQL it[color=green]
>>uses may not return a value? I'm asking this because I[/color]
> have a table[color=green]
>>with a particular field (call it Department) and then[/color]
> another field[color=green]
>>(call it Department number). Now I want to find the last[/color]
> record with a[color=green]
>>particular Department, so that for the one I am going to[/color]
> add, I can add[color=green]
>>1 to the Department number I have found to give the new[/color]
> record the next[color=green]
>>ascending Department number.
>>
>>I am using ExecuteScalar, but in the situation where[/color]
> there are not yet[color=green]
>>any members of that Department, I need to check this[/color]
> before assigning[color=green]
>>the return value of ExecuteScalar. How do I do this?
>>
>>
>>Thanks,
>>
>>Mike
>>
>>
>>
>>*** Sent via Developersdex http://www.developersdex.com[/color]
> ***[color=green]
>>Don't just participate in USENET...get rewarded for it!
>>.
>>[/color][/color]