I just installed the VisualWebDeveloper 2005 Express Beta and am writing a test application. One problem I ran into is with a library that I wrote in the previous ASP.Net version and works well. However, I am getting an error when I run it under ASP.Net 2.0.
Here's the code of the function:
public int ExecuteScalar(String sConn, String sQuery, Hashtable oParams, int nStoredProc) {
SqlConnection oConn;
SqlCommand oCmd;
int nResult;
try {
oConn = new SqlConnection(sConn);
oCmd = new SqlCommand(sQuery, oConn);
if (nStoredProc == 1) {
oCmd.CommandType = CommandType.StoredProcedure;
}
if (oParams != null && oParams.Count > 0) {
foreach (DictionaryEntry oItem in oParams) {
oCmd.Parameters.Add(new SqlParameter(oItem.Key.ToString(), oItem.Value.ToString()));
}
}
oConn.Open();
nResult = (int)oCmd.ExecuteScalar();
oConn.Close();
return nResult;
} catch (SqlException e) {
return -1;
}
}
And here's the error that I get when this function is called: Exception Details: System.InvalidCastException: Specified cast is not valid. Source Error: Line 129: } Line 130: oConn.Open();
Line 131: nResult = (int)oCmd.ExecuteScalar(); Line 132: oConn.Close(); Line 133: return nResult; When I check my database, the stored procedure or query that was executed has run successfully and the changes show up in the tables. This worked without
problem in pervious ASP.Net versions, but for some reason it's a problem in 2.0. If anyone has any ideas on what's wrong, I'd appreciate your assistance. Thank you.
I am not sure why you would get this in v2.0 but not in previous versions. ExecuteScalar() is returning something that cannot be cast to an int, and thus you are seeing this error. So the first thing you can check is what ExecuteScalar is really returning.
You can use the debugger, or output "ExecuteScalar().GetType().ToString()", and that will give you the type of what is being returned.
Thanks,
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
And that worked for me. Shouldn't I be able to automatically convert to integer using the (int) cast without having to go through
a Decimal type? Or maybe it depends on the data type being returned from the query?
That is interesting. After doing some reading, it looks like this is by design. It can not automatically convert to integer from a Object that contains a boxed Decimal. The reason for that is because of how Unboxing Conversions work. Here is a section of C#
reference dealing with that topic:
4.3.2 Unboxing conversions Note these 2 lines: "An unboxing operation consists of first checking that the object instance is a boxed value of the given value-type, and then copying the value out of the instance." "If the source operand is a reference to
an incompatible object, a System.InvalidCastException is thrown." This makes sense, because when you cast a reference type (Object) to a value type (int) what it really tries to do is cast internally the value type that is stored in the object, and if it's
not what it expects (it is Decimal but it expects an integer) it throws an exception. When you do this: Decimal result = (Decimal) ((int) oCmd.ExecuteScalar(); Then first it unboxes the object into an integer, then converts it to decimal using a user-defined
conversion. This shouldn't have anything to do with your upgrade to v2.0 - this is the behavior starting with v1.0. But if you can prove me wrong and show me some code that you know works in v1.1 and doesn't work in v2.0, I'd be very intereseted to hear that!
If you still have the previous version of ASP.Net on your machine, try to switch your application to that version, and probably you will still get that problem. If not, see if it still returns Decimal as the type.
Thanks,
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
Interesting explanation. I did the test that you mention: I ran the same code in 1.0 and it worked without modification. The data type returned was Int32. Looking deeper, in the stored procedure I was executing, I did something like this:
select 1 as returnvalue
When I changed it to this:
declare @retVal int
select @retVal = 1
select @retVal as returnvalue
I was able to
get it to work without modification in both 2.0 and 1.0.
That sounds like a bug that "select 1 as returnvalue" returns an Int32 on v1.0 and a Decimal on v2.0. However, I was not able to reproduce it by creating and executing this stored procedure: CREATE PROCEDURE test AS select 1 as returnValue It's possible this
was fixed in the later builds that I am using. If you can post the complete text of your stored procedure, I can try running that as well to see if I can reproduce the problem.
Thanks,
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
Here's a test stored procedure (unmodified, uses select -1):
CREATE proc usp_Test
@category varchar(50) = NULL
as
declare @resval int, @restext varchar(255)
if (@category is NULL)
begin
raiserror('Required parameter @category is missing.', 16, 1)
select -1
end
else if exists (select * from web_category where category = @category)
begin
raiserror('The specified category ''%s'' already exists.', 16, 1, @category)
select -1
end
else
begin
insert into web_category values (@category)
select @errVal = @@ERROR
if (@errVal <> 0)
begin
raiserror('Unable to add new category ''%s''. (%d)', 16, 1, @category, @errVal)
select -1
end
else
begin
select @@IDENTITY
end
end
And here is the same stored procedure except modified to use select @errVal = -1:
CREATE proc usp_Test
@category varchar(50) = NULL
as
declare @resval int, @restext varchar(255), @errVal int
if (@category is NULL)
begin
raiserror('Required parameter @category is missing.', 16, 1)
select @errVal = -1
end
else if exists (select * from web_category where category = @category)
begin
raiserror('The specified category ''%s'' already exists.', 16, 1, @category)
select @errVal = -1
end
else
begin
insert into web_category values (@category)
select @errVal = @@ERROR
if (@errVal <> 0)
begin
raiserror('Unable to add new category ''%s''. (%d)', 16, 1, @category, @errVal)
select @errVal = -1
end
else
begin
select @errVal = @@IDENTITY
end
end
select @errVal
Here is a test web page that calls the stored procedure:
With the first version of the stored procedure I get the error, and with the second, I get no error.
Also, just for completeness, the table web_category is defined as follows:
create table web_category (
cid int not null,
category varchar(50) not null
)
geekmeister
Member
87 Points
21 Posts
ExecuteScalar gets Specified cast is not valid error
Sep 16, 2004 10:32 PM|LINK
public int ExecuteScalar(String sConn, String sQuery, Hashtable oParams, int nStoredProc) { SqlConnection oConn; SqlCommand oCmd; int nResult; try { oConn = new SqlConnection(sConn); oCmd = new SqlCommand(sQuery, oConn); if (nStoredProc == 1) { oCmd.CommandType = CommandType.StoredProcedure; } if (oParams != null && oParams.Count > 0) { foreach (DictionaryEntry oItem in oParams) { oCmd.Parameters.Add(new SqlParameter(oItem.Key.ToString(), oItem.Value.ToString())); } } oConn.Open(); nResult = (int)oCmd.ExecuteScalar(); oConn.Close(); return nResult; } catch (SqlException e) { return -1; } }And here's the error that I get when this function is called: Exception Details: System.InvalidCastException: Specified cast is not valid. Source Error: Line 129: } Line 130: oConn.Open(); Line 131: nResult = (int)oCmd.ExecuteScalar(); Line 132: oConn.Close(); Line 133: return nResult; When I check my database, the stored procedure or query that was executed has run successfully and the changes show up in the tables. This worked without problem in pervious ASP.Net versions, but for some reason it's a problem in 2.0. If anyone has any ideas on what's wrong, I'd appreciate your assistance. Thank you.Leonid Tsybe...
Participant
1145 Points
226 Posts
Microsoft
Re: ExecuteScalar gets Specified cast is not valid error
Sep 16, 2004 11:32 PM|LINK
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
geekmeister
Member
87 Points
21 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Sep 17, 2004 06:30 PM|LINK
Leonid Tsybe...
Participant
1145 Points
226 Posts
Microsoft
Re: ExecuteScalar gets Specified cast is not valid error
Sep 18, 2004 01:31 AM|LINK
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
geekmeister
Member
87 Points
21 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Sep 20, 2004 12:51 AM|LINK
Leonid Tsybe...
Participant
1145 Points
226 Posts
Microsoft
Re: ExecuteScalar gets Specified cast is not valid error
Sep 20, 2004 07:10 PM|LINK
Leonid
This posting is provided "AS IS" with no warranties, and confers no rights.
geekmeister
Member
87 Points
21 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Sep 22, 2004 09:50 PM|LINK
CREATE proc usp_Test @category varchar(50) = NULL as declare @resval int, @restext varchar(255) if (@category is NULL) begin raiserror('Required parameter @category is missing.', 16, 1) select -1 end else if exists (select * from web_category where category = @category) begin raiserror('The specified category ''%s'' already exists.', 16, 1, @category) select -1 end else begin insert into web_category values (@category) select @errVal = @@ERROR if (@errVal <> 0) begin raiserror('Unable to add new category ''%s''. (%d)', 16, 1, @category, @errVal) select -1 end else begin select @@IDENTITY end endAnd here is the same stored procedure except modified to use select @errVal = -1:CREATE proc usp_Test @category varchar(50) = NULL as declare @resval int, @restext varchar(255), @errVal int if (@category is NULL) begin raiserror('Required parameter @category is missing.', 16, 1) select @errVal = -1 end else if exists (select * from web_category where category = @category) begin raiserror('The specified category ''%s'' already exists.', 16, 1, @category) select @errVal = -1 end else begin insert into web_category values (@category) select @errVal = @@ERROR if (@errVal <> 0) begin raiserror('Unable to add new category ''%s''. (%d)', 16, 1, @category, @errVal) select @errVal = -1 end else begin select @errVal = @@IDENTITY end end select @errValHere is a test web page that calls the stored procedure:<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> void cmdAddCategory_Click(object sender, EventArgs e) { SqlConnection oConn; SqlCommand oCmd; String strCategory; int nResult; strCategory = txtCategory.Text; oConn = new SqlConnection(Application["DBC"].ToString()); oCmd = new SqlCommand("usp_Test", oConn); oCmd.CommandType = CommandType.StoredProcedure; oCmd.Parameters.Add(new SqlParameter("@category", strCategory)); oConn.Open(); nResult = (int)oCmd.ExecuteScalar(); oConn.Close(); lblTest.Text = nResult.ToString(); } </script>Untitled Page
<form id="form1" runat="server">
With the first version of the stored procedure I get the error, and with the second, I get no error. Also, just for completeness, the table web_category is defined as follows:create table web_category ( cid int not null, category varchar(50) not null )ajmasters
Member
15 Points
2 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Oct 26, 2008 07:46 PM|LINK
I was getting an error also on the cast to an integer when using ExecuteScalar.
I needed to return the count from a table using something like this to make sure a record was not already in before INSERTING.
SELECT Count(*) FROM `mySQLtable` WHERE ...
Here is what I assembled from various posts on the net (including this one) for C#.
What I like is that it deals with nulls if they should ever be produced.
//intCount = (int)aCommand.ExecuteScalar(); // does not work for me in .NET 2.0
object anObj = aCommand.ExecuteScalar();
intCount = (anObj == null ? -1 : Convert.ToInt32(anObj.ToString()));
return intCount;
Good luck to all.
Morgstar
Member
16 Points
5 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Apr 12, 2010 11:10 AM|LINK
Nice Post ajmasters - thanks for the help - works well.
Omkar Lale
Participant
1288 Points
223 Posts
Re: ExecuteScalar gets Specified cast is not valid error
Apr 12, 2010 11:31 AM|LINK
Hi,
try following
nResult = int.Parse(oCmd.ExecuteScalar().ToString());
Omkar A. Lale
Keep your words soft and arguments hard.
~Do Mark as Answer if it solves your query~