Executescalar, retrieving a value

From MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIDbCommandClassExecuteScalarTopic.asp

Use the ExecuteScalar method to retrieve a single value (for example, an
aggregate value) from a database.

Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As
SqlConnection)
Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteScalar()
myConnection.Close()
End Sub 'CreateMySqlCommand

...

Thanks Billy...ExecuteScalar is used to retrieve a single value, and then
your example doesn't even reflect retrieving a value...hello! Anyone home?

Ok, sarcasm aside, could someone expound on this flawed example that shows
how a value returned from a stored procedure could be assigned to a variable
expression?

Thank you! :)

David
Wed Aug 31 10:22:53 CDT 2005


"Steve Schroeder" <sschroeder@somewhere.com> wrote in message
news:e%23z6t0jrFHA.1984@tk2msftngp13.phx.gbl...
> From MSDN:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIDbCommandClassExecuteScalarTopic.asp
>
> Use the ExecuteScalar method to retrieve a single value (for example, an
> aggregate value) from a database.
>
> Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As
> SqlConnection)
> Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
> myCommand.Connection.Open()
> myCommand.ExecuteScalar()
> myConnection.Close()
> End Sub 'CreateMySqlCommand
>
> ...
>
> Thanks Billy...ExecuteScalar is used to retrieve a single value, and then
> your example doesn't even reflect retrieving a value...hello! Anyone home?
>
> Ok, sarcasm aside, could someone expound on this flawed example that shows
> how a value returned from a stored procedure could be assigned to a
> variable
> expression?
>

ExecuteScalar is not for stored procedure. It simply returns the first
column of the first row as a scalar value. I wish it had never been
invented.

To return a value from a stored procedure, bind a SqlParameter to the
command with ParameterDirection.Output.

David

Marina
Wed Aug 31 10:28:02 CDT 2005

May I ask why you wish it had never been invented? I find it pretty handy.

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:emsWc$jrFHA.248@TK2MSFTNGP14.phx.gbl...
>
> "Steve Schroeder" <sschroeder@somewhere.com> wrote in message
> news:e%23z6t0jrFHA.1984@tk2msftngp13.phx.gbl...
>> From MSDN:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIDbCommandClassExecuteScalarTopic.asp
>>
>> Use the ExecuteScalar method to retrieve a single value (for example, an
>> aggregate value) from a database.
>>
>> Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As
>> SqlConnection)
>> Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
>> myCommand.Connection.Open()
>> myCommand.ExecuteScalar()
>> myConnection.Close()
>> End Sub 'CreateMySqlCommand
>>
>> ...
>>
>> Thanks Billy...ExecuteScalar is used to retrieve a single value, and then
>> your example doesn't even reflect retrieving a value...hello! Anyone
>> home?
>>
>> Ok, sarcasm aside, could someone expound on this flawed example that
>> shows
>> how a value returned from a stored procedure could be assigned to a
>> variable
>> expression?
>>
>
> ExecuteScalar is not for stored procedure. It simply returns the first
> column of the first row as a scalar value. I wish it had never been
> invented.
>
> To return a value from a stored procedure, bind a SqlParameter to the
> command with ParameterDirection.Output.
>
> David
>

David
Wed Aug 31 10:43:45 CDT 2005


"Marina" <someone@nospam.com> wrote in message
news:%23LmwTCkrFHA.1984@tk2msftngp13.phx.gbl...
> May I ask why you wish it had never been invented? I find it pretty handy.
>

object o = cmd.ExecuteScalar();

==

object ExecuteScalar(SqlCommand cmd)
{
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
return r[0]
}
return null;
}


It just doesn't save enough code to make up for the confusion and
inefficency it causes.

David

MiloszSkalecki
Wed Aug 31 10:47:20 CDT 2005

Hi,

Yes it can be used with stored procedures because stored procs can return
result set.

private int Test()
{
SqlConnection oConnection = new SqlConnection(cszConnectionString);
SqlCommand oCommand = new SqlCommand("MyStoredProcedure", oConnection);

oCommand.CommandType = CommandType.StoredProcedure;

try
{
oConnection.Open();
return (int) oCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oConnection.State != ConnectionState.Closed)
oConnection.Close();
}




// test stored proc
CREATE PROCEDURE [MyStoredProcedure] AS
SELECT 1
GO

--
Milosz Skalecki
MCP, MCAD


"David Browne" wrote:

>
> "Steve Schroeder" <sschroeder@somewhere.com> wrote in message
> news:e%23z6t0jrFHA.1984@tk2msftngp13.phx.gbl...
> > From MSDN:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIDbCommandClassExecuteScalarTopic.asp
> >
> > Use the ExecuteScalar method to retrieve a single value (for example, an
> > aggregate value) from a database.
> >
> > Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As
> > SqlConnection)
> > Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
> > myCommand.Connection.Open()
> > myCommand.ExecuteScalar()
> > myConnection.Close()
> > End Sub 'CreateMySqlCommand
> >
> > ...
> >
> > Thanks Billy...ExecuteScalar is used to retrieve a single value, and then
> > your example doesn't even reflect retrieving a value...hello! Anyone home?
> >
> > Ok, sarcasm aside, could someone expound on this flawed example that shows
> > how a value returned from a stored procedure could be assigned to a
> > variable
> > expression?
> >
>
> ExecuteScalar is not for stored procedure. It simply returns the first
> column of the first row as a scalar value. I wish it had never been
> invented.
>
> To return a value from a stored procedure, bind a SqlParameter to the
> command with ParameterDirection.Output.
>
> David
>
>
>

Marina
Wed Aug 31 10:49:30 CDT 2005

I think it saves enough code to be worth it.

But just because you don't happen to like it, why do you wish it was never
invented? Just don't use it.

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:e81oGLkrFHA.2768@TK2MSFTNGP12.phx.gbl...
>
> "Marina" <someone@nospam.com> wrote in message
> news:%23LmwTCkrFHA.1984@tk2msftngp13.phx.gbl...
>> May I ask why you wish it had never been invented? I find it pretty
>> handy.
>>
>
> object o = cmd.ExecuteScalar();
>
> ==
>
> object ExecuteScalar(SqlCommand cmd)
> {
> using (SqlDataReader r = cmd.ExecuteReader())
> {
> if (r.Read())
> return r[0]
> }
> return null;
> }
>
>
> It just doesn't save enough code to make up for the confusion and
> inefficency it causes.
>
> David
>

Cor
Wed Aug 31 10:53:43 CDT 2005

David,

As almost forever I agree with Marina,

And it shows so nice that you only use one value and not all.

Cor

Steve
Wed Aug 31 11:14:18 CDT 2005

David, Marina, et. al.

Here is a portion of what I ended up doing:

Dim QueryCollection As NameValueCollection
QueryCollection = Request.QueryString
Dim sID As Integer = QueryCollection(0)
Dim adoComm As New SqlCommand("dbo.MG_GetPhoto " & sID, adoConn)
imgEmployee.ImageUrl = adoComm.ExecuteScalar()
Turned out to be pretty easy actually. And ExecuteScalar worked pretty
slick, no need to use output parameters or anything of the like.

I don't know which way is more technically sound/correct but this worked
well enough for me.

I was more disturbed by the lame documentation provided for ExecuteScalar.
Absolutely useless.

Anyhow...thanks.