jeremypettit
asked on
Unable to return Output parameters from Stored Proc in Dot Net ---specified cast is not valid ---
I had noticed this topic being posted before but no real solution. What I and others are trying to do is...
from an ASPX page call upon a component's function that hits a database using a stored procedure, and return the output parameter.
My specifics are adding a new user to a DB, then return the @@Identity for the use of the next page after the user is added. The stored proc works, I can run from a VB6, Query Analyzer, or ASP page and get the expected results. So the problem is something I and others are doing in Dot Net. I receive the error "specified cast is not valid " when attempting to do execute the procedure and return the output parameter. I've tried using different data types, but doesn't change the results.
Code for NewUser Function...
Imports System.Data
Imports System.Data.SqlClient
Public Class cLogin
Inherits cConnection 'Connection Object
Public Function NewRefPortalUser(ByVal UserName As String, ByVal Password As String, ByVal FirstName As String, ByVal LastName As String) As VariantType
Dim objCrypto As EncryptIt 'Declare Encryption object
Dim varID As VariantType 'Declare variable for ID of new record
objCrypto = New EncryptIt() 'Instantiate Encryption object
Password = objCrypto.EncryptString(Pa ssword) 'Encrypt the password in DB
objCrypto = Nothing 'Destroy Encryption object
Dim MyCommand As SqlCommand = New SqlCommand("usp_NewRefPort alUser_INS ")
MyCommand.CommandType = CommandType.StoredProcedur e
Dim workParam As SqlParameter = Nothing
MyCommand.Parameters.Add(N ew SqlParameter("@FirstName", SqlDbType.VarChar, 30))
MyCommand.Parameters("@Fir stName").V alue = FirstName
MyCommand.Parameters.Add(N ew SqlParameter("@LastName", SqlDbType.VarChar, 30))
MyCommand.Parameters("@Las tName").Va lue = LastName
MyCommand.Parameters.Add(N ew SqlParameter("@UserName", SqlDbType.VarChar, 50))
MyCommand.Parameters("@Use rName").Va lue = UserName
MyCommand.Parameters.Add(N ew SqlParameter("@Password", SqlDbType.VarChar, 50))
MyCommand.Parameters("@Pas sword").Va lue = Password
workParam = MyCommand.Parameters.Add(N ew SqlParameter("@Identity", SqlDbType.Variant, 4))
workParam.Direction = ParameterDirection.Output
Connect(True) 'Connect to DB
MyCommand.Connection = CNx
MyCommand.ExecuteScalar()
varID = MyCommand.Parameters("@Ide ntity").Va lue
MyCommand.Dispose()
MyCommand = Nothing
Connect(False)
Return varID
End Function
Here is the code from the ASPX.vb page
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objportal As clsRefPortalLogin.cLogin
objportal = New clsRefPortalLogin.cLogin()
Dim varID As VariantType
varID = objportal.NewRefPortalUser ("Jeremy", "powerfull", "Jeremy", "Pettit")
objportal = Nothing
Response.Write(varID)
End Sub
from an ASPX page call upon a component's function that hits a database using a stored procedure, and return the output parameter.
My specifics are adding a new user to a DB, then return the @@Identity for the use of the next page after the user is added. The stored proc works, I can run from a VB6, Query Analyzer, or ASP page and get the expected results. So the problem is something I and others are doing in Dot Net. I receive the error "specified cast is not valid " when attempting to do execute the procedure and return the output parameter. I've tried using different data types, but doesn't change the results.
Code for NewUser Function...
Imports System.Data
Imports System.Data.SqlClient
Public Class cLogin
Inherits cConnection 'Connection Object
Public Function NewRefPortalUser(ByVal UserName As String, ByVal Password As String, ByVal FirstName As String, ByVal LastName As String) As VariantType
Dim objCrypto As EncryptIt 'Declare Encryption object
Dim varID As VariantType 'Declare variable for ID of new record
objCrypto = New EncryptIt() 'Instantiate Encryption object
Password = objCrypto.EncryptString(Pa
objCrypto = Nothing 'Destroy Encryption object
Dim MyCommand As SqlCommand = New SqlCommand("usp_NewRefPort
MyCommand.CommandType = CommandType.StoredProcedur
Dim workParam As SqlParameter = Nothing
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Fir
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Las
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Use
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Pas
workParam = MyCommand.Parameters.Add(N
workParam.Direction = ParameterDirection.Output
Connect(True) 'Connect to DB
MyCommand.Connection = CNx
MyCommand.ExecuteScalar()
varID = MyCommand.Parameters("@Ide
MyCommand.Dispose()
MyCommand = Nothing
Connect(False)
Return varID
End Function
Here is the code from the ASPX.vb page
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objportal As clsRefPortalLogin.cLogin
objportal = New clsRefPortalLogin.cLogin()
Dim varID As VariantType
varID = objportal.NewRefPortalUser
objportal = Nothing
Response.Write(varID)
End Sub
can you post the stored procedure?
seems that the examples I have show using the .ExecuteNonQuery() and not using the .ExecuteScalar() ...
Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database.
-------------------------- ---------- ---------- ----------
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.
--------------------------
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.
ASKER
Stored Proc here...
CREATE PROCEDURE usp_NewRefPortalUser_INS
(
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(30),
@LastName varchar(30),
@Identity int out
)
AS
INSERT INTO
tblRefPortalUsers
(
Email,
PortalPassword,
FirstName,
LastName
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName
)
SELECT @Identity = @@Identity
RETURN
CREATE PROCEDURE usp_NewRefPortalUser_INS
(
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(30),
@LastName varchar(30),
@Identity int out
)
AS
INSERT INTO
tblRefPortalUsers
(
Email,
PortalPassword,
FirstName,
LastName
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName
)
SELECT @Identity = @@Identity
RETURN
ASKER
ExecuteNonQuery Produces same results
Here's a detailed error...
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastExceptio n: Specified cast is not valid.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Specified cast is not valid.]
clsRefPortalLogin.cLogin.N ewRefPorta lUser(Stri ng UserName, String Password, String FirstName, String LastName) +584
PortalComponentTest.WebFor m1.Button1 _Click(Obj ect sender, EventArgs e) +58
System.Web.UI.WebControls. Button.OnC lick(Event Args e) +108
System.Web.UI.WebControls. Button.Sys tem.Web.UI .IPostBack EventHandl er.RaisePo stBackEven t(String eventArgument) +57
System.Web.UI.Page.RaisePo stBackEven t(IPostBac kEventHand ler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePo stBackEven t(NameValu eCollectio n postData) +33
System.Web.UI.Page.Process RequestMai n() +1263
Here's a detailed error...
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastExceptio
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Specified cast is not valid.]
clsRefPortalLogin.cLogin.N
PortalComponentTest.WebFor
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.Page.RaisePo
System.Web.UI.Page.RaisePo
System.Web.UI.Page.Process
workParam = MyCommand.Parameters.Add(N ew SqlParameter("@Identity", SqlDbType.Variant, 4))
workParam.Direction = ParameterDirection.Output
and
@Identity int out
don't match!! thus: Specified cast is not valid.
workParam.Direction = ParameterDirection.Output
and
@Identity int out
don't match!! thus: Specified cast is not valid.
also keep the .ExecuteNonQuery method of your command object
ASKER
ExecuteNonQuery Produces same results
Here's a detailed error...
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastExceptio n: Specified cast is not valid.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Specified cast is not valid.]
clsRefPortalLogin.cLogin.N ewRefPorta lUser(Stri ng UserName, String Password, String FirstName, String LastName) +584
PortalComponentTest.WebFor m1.Button1 _Click(Obj ect sender, EventArgs e) +58
System.Web.UI.WebControls. Button.OnC lick(Event Args e) +108
System.Web.UI.WebControls. Button.Sys tem.Web.UI .IPostBack EventHandl er.RaisePo stBackEven t(String eventArgument) +57
System.Web.UI.Page.RaisePo stBackEven t(IPostBac kEventHand ler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePo stBackEven t(NameValu eCollectio n postData) +33
System.Web.UI.Page.Process RequestMai n() +1263
Here's a detailed error...
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastExceptio
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Specified cast is not valid.]
clsRefPortalLogin.cLogin.N
PortalComponentTest.WebFor
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.Page.RaisePo
System.Web.UI.Page.RaisePo
System.Web.UI.Page.Process
ASKER
workParam = MyCommand.Parameters.Add(N ew SqlParameter("@Identity", SqlDbType.Variant, 4))
Was changed after while trying different variable types, same error with the type of int
Was changed after while trying different variable types, same error with the type of int
then use a datatype that you know is compatible from ADO.NET and SQL Server ... I know that Numeric (in Oracle) must be Decimal in ADO.NET ... but if you don't know those types of conversions then pass it out as a string and receive it as a string then convert is to the numeric or what ever other datatype you need to work with it ... just a suggestion, as I don't have a working .NET project with SQL Server on the go right now ...
ASKER
workParam = MyCommand.Parameters.Add(N ew SqlParameter("@Identity", SqlDbType.Variant, 4))
Was changed after while trying different variable types, same error with the type of int
Was changed after while trying different variable types, same error with the type of int
ASKER
I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOu tput
and sent it a value of 0
then to get the return value...
varID = MyCommand.Parameters("@Ide ntity").Va lue.ToStri ng
to
workParam.Direction = ParameterDirection.InputOu
and sent it a value of 0
then to get the return value...
varID = MyCommand.Parameters("@Ide
good fix ...
I see you've got "a" fix, but I've already written this up, so here it is. By the way, in sample 2 I've successfully tested:
SqlDbType.Int, Integer, and int ... in place of ...
SqlDbType.Variant, VariantType, and sql_variant.
I have no problems with ParameterDirection.Output. I think your fix was just using ToString.
(Guess: Sounds like you're a VBScript // ASP programmer extending your skills. In regard to type-conversion issues, I highly recommend using "Option Strict On" at the top of ALL your VB.NET code. It requires just a tiny bit more work up-front, but will save you lots of headaches later.)
----- ----- -----
[Using Win2000 SP3, SQL Server 2000, Northwind database sample]
I'm not sure why you're using variant so early. I think the only place you need to use VariantType is the return value of NewRefPortalUser()
Here's a couple of examples that work. Of course, if your stored procedure uses some other data type, you'll need to use it's data type for the parameter and then use CType() to convert it to almost whatever you want.
SAMPLE 1: Just uses the return value. No parameter needed.
VB.NET:
Dim cmd1 As New SqlCommand("Sample1_INS")
cmd1.CommandType = CommandType.StoredProcedur e
cmd1.Connection = conn
Dim varID1 As VariantType
varID1 = CType(cmd1.ExecuteScalar() , VariantType)
cmd1.Dispose()
cmd1 = Nothing
T-SQL:
CREATE PROCEDURE Sample1_INS
AS
INSERT INTO [Northwind].[dbo].[Employe es]([LastN ame], [FirstName])
VALUES('Smith', 'Bill' )
SELECT @@IDENTITY
----- ----- -----
SAMPLE 2: Uses an output parameter of type SqlDbType.Variant
VB.NET:
Dim cmd2 As New SqlCommand("Sample2_INS")
cmd2.CommandType = CommandType.StoredProcedur e
cmd2.Connection = conn
Dim workParam2 As SqlParameter = Nothing
workParam2 = cmd2.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant))
workParam2.Direction = ParameterDirection.Output
cmd2.ExecuteScalar()
Dim varID2 As VariantType
varID2 = CType(cmd2.Parameters("@Id entity").V alue, VariantType)
cmd2.Dispose()
cmd2 = Nothing
T-SQL:
CREATE PROCEDURE Sample2_INS (@Identity sql_variant output)
AS
INSERT INTO [Northwind].[dbo].[Employe es]([LastN ame], [FirstName])
VALUES('Smith', 'Bill' )
SET @Identity = (SELECT @@IDENTITY AS 'Identity')
SqlDbType.Int, Integer, and int ... in place of ...
SqlDbType.Variant, VariantType, and sql_variant.
I have no problems with ParameterDirection.Output.
(Guess: Sounds like you're a VBScript // ASP programmer extending your skills. In regard to type-conversion issues, I highly recommend using "Option Strict On" at the top of ALL your VB.NET code. It requires just a tiny bit more work up-front, but will save you lots of headaches later.)
----- ----- -----
[Using Win2000 SP3, SQL Server 2000, Northwind database sample]
I'm not sure why you're using variant so early. I think the only place you need to use VariantType is the return value of NewRefPortalUser()
Here's a couple of examples that work. Of course, if your stored procedure uses some other data type, you'll need to use it's data type for the parameter and then use CType() to convert it to almost whatever you want.
SAMPLE 1: Just uses the return value. No parameter needed.
VB.NET:
Dim cmd1 As New SqlCommand("Sample1_INS")
cmd1.CommandType = CommandType.StoredProcedur
cmd1.Connection = conn
Dim varID1 As VariantType
varID1 = CType(cmd1.ExecuteScalar()
cmd1.Dispose()
cmd1 = Nothing
T-SQL:
CREATE PROCEDURE Sample1_INS
AS
INSERT INTO [Northwind].[dbo].[Employe
VALUES('Smith', 'Bill' )
SELECT @@IDENTITY
----- ----- -----
SAMPLE 2: Uses an output parameter of type SqlDbType.Variant
VB.NET:
Dim cmd2 As New SqlCommand("Sample2_INS")
cmd2.CommandType = CommandType.StoredProcedur
cmd2.Connection = conn
Dim workParam2 As SqlParameter = Nothing
workParam2 = cmd2.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant))
workParam2.Direction = ParameterDirection.Output
cmd2.ExecuteScalar()
Dim varID2 As VariantType
varID2 = CType(cmd2.Parameters("@Id
cmd2.Dispose()
cmd2 = Nothing
T-SQL:
CREATE PROCEDURE Sample2_INS (@Identity sql_variant output)
AS
INSERT INTO [Northwind].[dbo].[Employe
VALUES('Smith', 'Bill' )
SET @Identity = (SELECT @@IDENTITY AS 'Identity')
ASKER
I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOu tput
and sent it a value of 0
then to get the return value...
varID = MyCommand.Parameters("@Ide ntity").Va lue.ToStri ng
to
workParam.Direction = ParameterDirection.InputOu
and sent it a value of 0
then to get the return value...
varID = MyCommand.Parameters("@Ide
ASKER
I did replace the variants back to integers, was originally using them, but tried variants to get around the type conversion thing. Thanks for your help and feedback
Dear expert(s),
A request has been made to close this Q in CS:
https://www.experts-exchange.com/questions/20601596/Question-about-Points.html
Without a response in 72 hrs, a moderator will finalize this question by:
- Saving this Q as a PAQ and refunding the points to the questionner
When you agree or disagree, please add a comment here.
Thank you.
modulo
Community Support Moderator
Experts Exchange
A request has been made to close this Q in CS:
https://www.experts-exchange.com/questions/20601596/Question-about-Points.html
Without a response in 72 hrs, a moderator will finalize this question by:
- Saving this Q as a PAQ and refunding the points to the questionner
When you agree or disagree, please add a comment here.
Thank you.
modulo
Community Support Moderator
Experts Exchange
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.