Link to home
Start Free TrialLog in
Avatar of jeremypettit
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(Password)    'Encrypt the password in DB

 

        objCrypto = Nothing             'Destroy Encryption object

 

        Dim MyCommand As SqlCommand = New SqlCommand("usp_NewRefPortalUser_INS")

 

        MyCommand.CommandType = CommandType.StoredProcedure

 

        Dim workParam As SqlParameter = Nothing

 

        MyCommand.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.VarChar, 30))

        MyCommand.Parameters("@FirstName").Value = FirstName

 

        MyCommand.Parameters.Add(New SqlParameter("@LastName", SqlDbType.VarChar, 30))

        MyCommand.Parameters("@LastName").Value = LastName

 

        MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 50))

        MyCommand.Parameters("@UserName").Value = UserName

 

        MyCommand.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 50))

        MyCommand.Parameters("@Password").Value = Password


        workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))

        workParam.Direction = ParameterDirection.Output


        Connect(True)                   'Connect to DB

        MyCommand.Connection = CNx

        MyCommand.ExecuteScalar()

        varID = MyCommand.Parameters("@Identity").Value

        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

Avatar of CarlosMu
CarlosMu

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.

Avatar of jeremypettit

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
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.InvalidCastException: 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.NewRefPortalUser(String UserName, String Password, String FirstName, String LastName) +584
   PortalComponentTest.WebForm1.Button1_Click(Object sender, EventArgs e) +58
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1263
 

 
      workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))

       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
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.InvalidCastException: 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.NewRefPortalUser(String UserName, String Password, String FirstName, String LastName) +584
   PortalComponentTest.WebForm1.Button1_Click(Object sender, EventArgs e) +58
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1263
 

 
workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))
 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 ...
workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))
 Was changed after while trying different variable types, same error with the type of int
I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOutput

and sent it a value of 0

then to get the return value...
varID = MyCommand.Parameters("@Identity").Value.ToString
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.StoredProcedure
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].[Employees]([LastName], [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.StoredProcedure
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("@Identity").Value, VariantType)
cmd2.Dispose()
cmd2 = Nothing

T-SQL:

CREATE PROCEDURE Sample2_INS (@Identity sql_variant output)
AS
INSERT INTO [Northwind].[dbo].[Employees]([LastName], [FirstName])
VALUES('Smith', 'Bill' )
SET @Identity = (SELECT @@IDENTITY AS 'Identity')

I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOutput

and sent it a value of 0

then to get the return value...
varID = MyCommand.Parameters("@Identity").Value.ToString
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
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial