Main Page | Report Page

 

  .NET DotNet Forum Index » Visual C# Forum » SQL Server functions and TableAdapters...

Author Message
John3...
Posted: Thu Oct 29, 2009 4:08 pm
 
Hi there,

I have an SQL function (not stored procedure) that returns a single
variable. The function works and I can preview its results in VS. However,
it always returns null when I invoke it in code through my TableAdapter. I'm
reasonably sure it's caused by the underlying call to
"SqlCommand.ExecuteScalar()" which returns "the first column of the first
row in the result set, or a null reference ... if the result set is empty".
Since I'm returning a single variable only, does anyone know how to get it
working so that the latter call to "SqlCommand.ExecuteScalar()" will return
it. Thanks very much.
 
Erland Sommarskog...
Posted: Thu Oct 29, 2009 4:08 pm
 
John3 (no_spa at (no spam) ___spam.com) writes:
Quote:
I have an SQL function (not stored procedure) that returns a single
variable. The function works and I can preview its results in VS.
However, it always returns null when I invoke it in code through my
TableAdapter. I'm reasonably sure it's caused by the underlying call to
"SqlCommand.ExecuteScalar()" which returns "the first column of the
first row in the result set, or a null reference ... if the result set
is empty". Since I'm returning a single variable only, does anyone know
how to get it working so that the latter call to
"SqlCommand.ExecuteScalar()" will return it. Thanks very much.

So what it is your CommandText? It's difficult to tell what's going
when we don't see the code.




--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
John3...
Posted: Thu Oct 29, 2009 5:44 pm
 
Quote:
So what it is your CommandText? It's difficult to tell what's going
when we don't see the code.

Thanks for the feedback. The code is auto-generated by the Visual Studio
designer (using their wizard) . You can see this below. I simply call it but
I but don't think that's the problem. Note that the "command" variable is
simply the name of my SQL function. "command.Parameters[0].ParameterName" is
set to " at (no spam) RETURN_VALUE" which may be useful to know. The "returnValue" comes
back null which is the problem. I'm reasonably sure it's because my function
returns a simple variable, given what I mentioned in my first post (I tried
returning it using a SELECT statement but the problem persists). Maybe this
would work using a stored procedure instead of a function since I do that
all the time without issue (though I'm not returning a simple variable in
those cases). Note that my function works since I've tested it and I can
also call it in Visual Studio when I wire this all up in the first place
(using a "preview" button in the wizard). Any ideas would be helpful since
I've searched for hours now with no luck. Thanks again.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public virtual object CalcLegDistance(int legId, bool miles)
{
global::System.Data.SqlClient.SqlCommand command =
this.CommandCollection[1];
command.Parameters[1].Value = ((int)(legId));
command.Parameters[2].Value = ((bool)(miles));
global::System.Data.ConnectionState previousConnectionState =
command.Connection.State;
if (((command.Connection.State &
global::System.Data.ConnectionState.Open)
!= global::System.Data.ConnectionState.Open))
{
command.Connection.Open();
}
object returnValue;
try
{
returnValue = command.ExecuteScalar();
}
finally
{
if ((previousConnectionState ==
global::System.Data.ConnectionState.Closed))
{
command.Connection.Close();
}
}
if (((returnValue == null)
|| (returnValue.GetType() ==
typeof(global::System.DBNull))))
{
return null;
}
else
{
return ((object)(returnValue));
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
John3...
Posted: Thu Oct 29, 2009 5:53 pm
 
I tried this in a stored procedure instead of a function and it works fine
(by issuing "SELECT at (no spam) MyVariable" as the final line). I'd like to keep it in
a function if possible however so clearly there's some issue with the way
the data is being returned. Any insight you can provide would be welcome.
Thanks again.
 
Michael Coles...
Posted: Thu Oct 29, 2009 9:09 pm
 
What are the contents of this.CommandCollection?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"John3" <no_spa at (no spam) ___spam.com> wrote in message
news:uZh2FHPWKHA.1792 at (no spam) TK2MSFTNGP04.phx.gbl...
Quote:
So what it is your CommandText? It's difficult to tell what's going
when we don't see the code.

Thanks for the feedback. The code is auto-generated by the Visual Studio
designer (using their wizard) . You can see this below. I simply call it
but I but don't think that's the problem. Note that the "command" variable
is simply the name of my SQL function.
"command.Parameters[0].ParameterName" is set to " at (no spam) RETURN_VALUE" which may
be useful to know. The "returnValue" comes back null which is the problem.
I'm reasonably sure it's because my function returns a simple variable,
given what I mentioned in my first post (I tried returning it using a
SELECT statement but the problem persists). Maybe this would work using a
stored procedure instead of a function since I do that all the time
without issue (though I'm not returning a simple variable in those cases).
Note that my function works since I've tested it and I can also call it in
Visual Studio when I wire this all up in the first place (using a
"preview" button in the wizard). Any ideas would be helpful since I've
searched for hours now with no luck. Thanks again.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public virtual object CalcLegDistance(int legId, bool miles)
{
global::System.Data.SqlClient.SqlCommand command =
this.CommandCollection[1];
command.Parameters[1].Value = ((int)(legId));
command.Parameters[2].Value = ((bool)(miles));
global::System.Data.ConnectionState previousConnectionState =
command.Connection.State;
if (((command.Connection.State &
global::System.Data.ConnectionState.Open)
!= global::System.Data.ConnectionState.Open))
{
command.Connection.Open();
}
object returnValue;
try
{
returnValue = command.ExecuteScalar();
}
finally
{
if ((previousConnectionState ==
global::System.Data.ConnectionState.Closed))
{
command.Connection.Close();
}
}
if (((returnValue == null)
|| (returnValue.GetType() ==
typeof(global::System.DBNull))))
{
return null;
}
else
{
return ((object)(returnValue));
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 
Erland Sommarskog...
Posted: Fri Oct 30, 2009 12:36 pm
 
John3 (no_spa at (no spam) ___spam.com) writes:
Quote:
Yes, you're right. That works. The correct value comes back in
Parameters[0]. Ok, I'm seeing how this works now. The question is, do I
need to code this myself. I normally wire everything up on the "DataSet"
design surface in Visual Studio (using the TableAdapter wizard). I don't
see how to automate the call however after doing some experiments. Do
you know if it's possible or am I stuck rolling this myself. Thanks very
much.

Now you are asking me about something I know very little about, that is
Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.


--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
John3...
Posted: Fri Oct 30, 2009 5:41 pm
 
Quote:
Yes, you're right. That works. The correct value comes back in
Parameters[0]. Ok, I'm seeing how this works now. The question is, do I
need to code this myself. I normally wire everything up on the "DataSet"
design surface in Visual Studio (using the TableAdapter wizard). I don't
see how to automate the call however after doing some experiments. Do
you know if it's possible or am I stuck rolling this myself. Thanks very
much.

Now you are asking me about something I know very little about, that is
Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.

Ok, thanks for the feedback. I realize it's a SQL Server NG (also
cross-posted to C#) but most SQL Server developers are probably working with
Visual Studio anyway. For those that are, I think it's much cleaner and
safer to use the "TableAdapter" wizard that's already built in
(notwithstanding this particular issue). It's reliable, cleaner, easier, and
much more maintainable than do-it-yourself code (also sets a common MSFT
standard that everyone can follow and generally saves a lot of time rolling
it yourself). Given this issue particular however, it's clearly not an
exhaustive system yet (by MSFT) but still highly configurable just the same
(you can integrate your code into their existing system which I've now
done). Thanks for everyone's help (appreciated).
 
John3...
Posted: Sat Oct 31, 2009 7:59 am
 
Quote:
Now you are asking me about something I know very little about, that is
Visual Studio. My speciality is in SQL Server and I've learnt the call
API in ADO .Net to get an understanding of it. I only program in C#
occasionally, and when I do I write all code on my own (or peek at
what I did last time). Generally, I dislike having something to generate
my database access code, because I feel that I lose control over it.
But at least VS has the good taste to generate code that you can change
on your own.

But if you want to know if VS can do this better for you, you need to
ask in a Visual Studio forum. Or maybe someone from the C# newsgroup
can fill in.

(Sorry, this was already posted to microsoft.public.dotnet.languages.csharp)

Ok, thanks for the feedback. I realize it's a SQL Server NG (also
cross-posted to C#) but most SQL Server developers are probably working with
Visual Studio anyway. For those that are, I think it's much cleaner and
safer to use the "TableAdapter" wizard that's already built in
(notwithstanding this particular issue). It's reliable, cleaner, easier, and
much more maintainable than do-it-yourself code (also sets a common MSFT
standard that everyone can follow and generally saves a lot of time rolling
it yourself). Given this particular issue however, it's clearly not a
complete system yet (by MSFT) but still highly configurable just the same
(you can integrate your code into their existing system which I've now
done). Thanks for everyone's help (appreciated).
 
 
Page 1 of 1    
All times are GMT - 5 Hours
The time now is Sun May 29, 2016 2:20 pm